SQL Server 2014

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