3

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?

GMB
  • 216,147
  • 25
  • 84
  • 135
Vy Do
  • 46,709
  • 59
  • 215
  • 313

1 Answers1

6

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.

GMB
  • 216,147
  • 25
  • 84
  • 135
  • Can you show few lines of SQL for easy-to-understand? An example for valid data, An another example for invalid data. I still don't understand. – Vy Do Oct 24 '20 at 00:43
  • 2
    @DoNhuVy: for your example, you can't have two rows with the same `title` and `copies`. Rows with the same `title` and different `copies` are allowed, as well as rows with the same `copies` and different `title`. – GMB Oct 24 '20 at 00:48
  • 1
    For the example, couldn't you implement it with a composite unique constraint? – Ben Thul Oct 24 '20 at 19:37