Adding Constraints That Check A Separate (linked) Table For A Value

I have two tables: Book(BookID, Title, Author, Decision) BookShipment(BookID, ShipmentID) CREATE TABLE BookShipment( BookID CHAR(4), ShipmentID(7) CONSTRAINT pk_BookShipment PRIMAR

Solution 1:

If you have only a single thing to check, then astentex's answer will do you nicely. But if you have arbitrary constraints, especially spanning multiple tables, there is a different option which is more flexible.

It is based around a trick involving Indexed Views. I got this from an article by spaghettidba.

An indexed view is a view that is persisted to disk. We create it by creating a clustered index on the view. There are many limitations to it, crucially in our case that we can't use left/right/full join, only inner is allowed. It also must be schema-bound (you can't change the underlying columns), and must reference tables with two-part names.

Let us suppose that the opposite of your constraint is true: there are rows in BookShipment for which the relevant Book is not Approved. How can we see such Books in a view:

CREATE/* OR ALTER */VIEW dbo.vwNonApprovedBooks
WITH SCHEMABINDING
ASSELECT b.BookId
    FROM dbo.BookShipment AS bs
    JOIN dbo.Book AS b ON b.BookID = bs.BookID
    WHERE b.Decision <>'Approved';
GO

We could index this by creating a clustered index, DO NOT do this yet:

CREATEUNIQUE CLUSTERED INDEX CX_vwNonApprovedBooks ON dbo.vwNonApprovedBooks (BookId);

Now we will pull a little trick. If we want to stop any rows existing in this view, we need to force every inserted row to multiply out so that it fails the unique constraint.

Let us create a table for this:

CREATETABLE dbo.DummyTwoRows (x bit NOTNULLPRIMARY KEY);
GO
INSERT dbo.DummyTwoRows VALUES (0),(1);

Now we can redefine the view like this:

CREATE/* OR ALTER */VIEW dbo.vwNonApprovedBooks
WITH SCHEMABINDING
ASSELECT1AS DummyOne
    FROM dbo.BookShipment AS bs
    JOIN dbo.Book AS b ON b.BookID = bs.BookID
    CROSSJOIN dbo.DummyTwoRows
    WHERE b.Decision <>'Approved';
GO
CREATEUNIQUE CLUSTERED INDEX CX_vwNonApprovedBooks ON dbo.vwNonApprovedBooks (DummyOne);

And on any insert into BookShipment with a Book that is not Approved, the unique constraint will fail.

SQL Server will maintain this view on inserts and updates, so that if a Book is changed to not Approved where it has BookShipment, the constraint will fail the update also.

Note that this index takes up no space as there are never any rows in it.

Solution 2:

If you'll always have a single status to check, this can be done with little tricks on FK constraint:

• Create dummy unuque index on Books(BookId, Decision).
• Add calculated column to BookShipment with value Approved.
• Reference the created unique index in FK constraint.

Defining UDF in CHECK constraint should be more flexible way for this.

createtable book (
  BookID intidentity(1,1) primary key,
  Title varchar(100),
  Author varchar(100),
  Decision varchar(100),
  
  --Dummy constraint for FKconstraint u_book unique(bookid, decision)
);

CREATETABLE BookShipment(
  BookID int,
  ShipmentID varchar(7),
  --Dummy column for FK
  approved ascast('Approved'asvarchar(100)) persisted
  
  CONSTRAINT pk_BookShipment PRIMARY KEY (BookID),
  CONSTRAINT fk_BookShipment_Book_Approved
    FOREIGN KEY (BookID, approved)
    REFERENCES Book(BookID, decision)
);

insertinto book (Title, Author, Decision)
select'A', 'B', 'Approved'unionallselect'A', 'B', 'New'
;

--2 rows affectedinsertinto BookShipment values(1, 1);

--1 rows affectedinsertinto BookShipment values(2, 2);

/*

insert into BookShipment values(2, 2);


Msg 547 Level 16 State 0 Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "fk_BookShipment_Book_Approved". The conflict occurred in database "fiddle_ea408f09b06247a78b47ea9c353eda10", table "dbo.book".
Msg 3621 Level 0 State 0 Line 1
The statement has been terminated.
*/

db<>fiddle here