Exclusive Arc Integrity with Check Constraints

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
sql
ALTER TABLE entity_attachments
ADD 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

ADD CONSTRAINT chk_exclusive_owner

Creates a named constraint for enforcing data integrity across multiple columns.

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.

Previous snippet Next snippet

From your library

Exclusive Arc Integrity with Check Constraints

Related