By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Test1
(
ThingA INT NOT NULL,
ThingB INT NOT NULL,
PRIMARY KEY (ThingA, ThingB)
)
CREATE TABLE Test2
(
ThingAVal INT NOT NULL,
ThingBVal INT NOT NULL DEFAULT(3) CHECK(ThingBVal = 3),
Val INT NOT NULL,
FOREIGN KEY (ThingAVal, ThingBVal) REFERENCES Test1 (ThingA, ThingB)
)
INSERT INTO Test1 VALUES (1, 3), (1, 4), (2, 4)
INSERT INTO Test2 (ThingAVal, Val) VALUES (1, 999)
4 rows affected
INSERT INTO Test2 (ThingAVal, Val) VALUES (2, 998)
INSERT INTO Test2 (ThingAVal, ThingBVal, Val) VALUES (2, 3, 998)
Msg 547 Level 16 State 0 Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Test2__1367E606". The conflict occurred in database "fiddle_ed24026294614e3eb6bd6bfd4a9dadff", table "dbo.Test1".
Msg 547 Level 16 State 0 Line 2
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Test2__1367E606". The conflict occurred in database "fiddle_ed24026294614e3eb6bd6bfd4a9dadff", table "dbo.Test1".
Msg 3621 Level 0 State 0 Line 1
The statement has been terminated.
Msg 3621 Level 0 State 0 Line 2
The statement has been terminated.
SELECT * FROM Test1
SELECT * FROM Test2
| ThingA | ThingB |
|---|---|
| 1 | 3 |
| 1 | 4 |
| 2 | 4 |
| ThingAVal | ThingBVal | Val |
|---|---|---|
| 1 | 3 | 999 |
DROP TABLE Test2
DROP TABLE Test1