sql / expert
Snippet
Exclusive Arc Integrity with Check Constraints
An Exclusive Arc (or Polymorphic Association) ensures that a record is associated with exactly one of several possible parents. Using a CHECK constraint at the table level enforces this business rule with mathematical certainty.
snippet.sql
1
2
3
4
5
ALTER TABLE entity_attachmentsADD CONSTRAINT chk_exclusive_owner CHECK ((user_id IS NOT NULL AND organization_id IS NULL) OR(user_id IS NULL AND organization_id IS NOT NULL));
Breakdown
1
ADD CONSTRAINT chk_exclusive_owner
Creates a named constraint for enforcing data integrity across multiple columns.
2
CHECK ((user_id IS NOT NULL AND ...)
Defines the logical XOR (exclusive OR) condition where one column must be set and the other must be null.