Search code examples
sqlsql-serverpostgresqlexclusion-constraint

What is exclusion constraint in PostgreSQL? What is the according term (and sample SQL script) in Microsoft SQL Server?

I read a book

enter image description here

Exclusion constraint

CREATE TABLE movies
(
    Title TEXT,
    Copies INTEGER
);

ALTER TABLE movies ADD EXCLUDE (title WITH=, copies WITH=);

What is the meaning of Exclusion constraints? What is the according term (and sample SQL script) in Microsoft SQL Server?

Solution

  • What is the meaning of Exclusion constraints?

    That's a special type of constraints, that is defined as a series of comparaison on table columns. It guaranties that there cannot be two different rows for which the comparisons are all true.

    In the (oversimplified) example that is presented in the book, the constraint prevents two different rows to have the same title and the same copies.

    Exclusion constraints are very powerful; they have a flexible syntax, that can accomodate much more complex cases that what is demonstrated in your book. A typical example is to ensure that, given a table with two timestamp columns, there is no overlap in the timestamp ranges across rows.

    What is the according term (and sample SQL script) in Microsoft SQL Server?

    I don't think that such feature exists in SQL Server. It has check constraints, but that does not offer the same range of functionality as Postgres' exclusion constraints.