Check constraint based on information in another table

Question

Given two tables:

TableA

(
    id : primary key, 
    type : tinyint, 
    ...
) 

TableB

(
    id : primary key, 
    tableAId : foreign key to TableA.id,
    ...
)

There is a check constraint on TableA.type with permitted values of (0, 1, 2, 3). All other values are forbidden.

Due to the known limitations, records in TableB can exist only when TableB.TableAId references the record in TableA with TableA.type=0, 1 or 2 but not 3. The latter case is forbidden and leads the system into an invalid state.

How can I guarantee that in such case the insert to TableB will fail?

Answer 1

Cross-table constraint using an empty indexed view:

Tables

CREATE TABLE dbo.TableA 
(
    id integer NOT NULL PRIMARY KEY, 
    [type] tinyint NOT NULL 
        CHECK ([type] IN (0, 1, 2, 3))
);

CREATE TABLE dbo.TableB 
(
    id integer NOT NULL PRIMARY KEY, 
    tableAId integer NOT NULL 
        FOREIGN KEY
        REFERENCES dbo.TableA
);

The 'constraint view'

-- This view is always empty (limited to error rows)
CREATE VIEW dbo.TableATableBConstraint
WITH SCHEMABINDING AS
SELECT
    Error = 
        CASE 
            -- Error condition: type = 3 and rows join
            WHEN TA.[type] = 3 AND TB.id = TA.id
            -- For a more informative error
            THEN CONVERT(bit, 'TableB cannot reference type 3 rows in TableA.')
            ELSE NULL
        END
FROM dbo.TableA AS TA
JOIN dbo.TableB AS TB
    ON TB.id = TA.id
WHERE
    TA.[type] = 3;
GO
CREATE UNIQUE CLUSTERED INDEX cuq 
ON dbo.TableATableBConstraint (Error);

Online demo:

-- All succeed
INSERT dbo.TableA (id, [type]) VALUES (1, 1);
INSERT dbo.TableA (id, [type]) VALUES (2, 2);
INSERT dbo.TableA (id, [type]) VALUES (3, 3);

INSERT dbo.TableB 
    (id, tableAId) 
VALUES
    (1, 1),
    (2, 2);

-- Fails
INSERT dbo.TableB (id, tableAId) VALUES (3, 3);

-- Fails
UPDATE dbo.TableA SET [type] = 3 WHERE id = 1;

This is similar in concept to the linked answer to Check constraints that ensures the values in a column of tableA is less the values in a column of tableB, but this solution is self-contained (does not require a separate table with more than one row at all times). It also produces a more informational error message, for example:

Msg 245, Level 16, State 1
Conversion failed when converting the varchar value 'TableB cannot reference type 3 rows in TableA.' to data type bit.

Important notes

The error condition must be completely specified in the CASE expression to ensure correct operation in all cases. Do not be tempted to omit conditions implied by the rest of the statement. In this example, it would be an error to omit TB.id = TA.id (implied by the join).

The SQL Server query optimizer is free to reorder predicates, and makes no general guarantees about the timing or number of evaluations of scalar expressions. In particular, scalar computations can be deferred.

Completely specifying the error condition(s) within a CASE expression ensures the complete set of tests is evaluated together, and no earlier than correctness requires. From an execution plan perspective, this means the Compute Scalar associated with the CASE tests will appear on the indexed view delta maintenance branch:

The light shaded area highlights the indexed view maintenance region; the Compute Scalar containing the CASE expression is dark-shaded.