By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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
);
-- This view is always empty
CREATE VIEW dbo.TableATableBConstraint
WITH SCHEMABINDING AS
SELECT
Error =
CASE
-- Must reference a column from all tables
WHEN TA.[type] = 3 AND TB.id = TB.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;
CREATE UNIQUE CLUSTERED INDEX cuq
ON dbo.TableATableBConstraint (Error);
-- 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);
INSERT dbo.TableB (id, tableAId) VALUES (2, 2);
5 rows affected
-- Fails
INSERT dbo.TableB (id, tableAId) VALUES (3, 3);
Msg 245 Level 16 State 1 Line 2
Conversion failed when converting the varchar value 'TableB cannot reference type 3 rows in TableA.' to data type bit.
-- Fails
UPDATE dbo.TableA SET [type] = 3 WHERE id = 1;
Msg 245 Level 16 State 1 Line 2
Conversion failed when converting the varchar value 'TableB cannot reference type 3 rows in TableA.' to data type bit.