SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE dbo.a (
 id int NOT NULL PRIMARY KEY,
 date datetime NOT NULL,
 client_id int NOT NULL ,
  UNIQUE(id, client_id)
);
CREATE TABLE dbo.b (
 id int NOT NULL PRIMARY KEY FOREIGN KEY REFERENCES dbo.a (id)
);
CREATE TABLE dbo.c (
 id int NOT NULL PRIMARY KEY FOREIGN KEY REFERENCES dbo.a (id)
);
CREATE TABLE e (
 id int NOT NULL PRIMARY KEY,
 client_id int NOT NULL,
  UNIQUE(id, client_id)
);
CREATE TABLE d (
 id int NOT NULL PRIMARY KEY FOREIGN KEY REFERENCES dbo.a (id),
 e_id int NOT NULL FOREIGN KEY REFERENCES dbo.e (id),
 client_id INT NOT NULL,
  FOREIGN KEY (id,client_id) REFERENCES dbo.a (id, client_id),
  FOREIGN KEY (e_id,client_id) REFERENCES dbo.e (id, client_id)
);
INSERT INTO a (id, date, client_id) VALUES (1, '2050-01-01', 1);
INSERT INTO a (id, date, client_id) VALUES (2, '2050-01-02', 1);
INSERT INTO a (id, date, client_id) VALUES (3, '2050-01-01', 1);
INSERT INTO a (id, date, client_id) VALUES (4, '2050-01-02', 1);
INSERT INTO b (id) VALUES (1);
INSERT INTO b (id) VALUES (2); -- should work
INSERT INTO c (id) VALUES (3);
INSERT INTO c (id) VALUES (4); -- should work
8 rows affected

INSERT INTO e (id, client_id) VALUES (1, 1), (2,2);
2 rows affected

INSERT INTO d (id, e_id, client_id) VALUES (1, 1, 2);
Msg 547 Level 16 State 0 Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__d__44FF419A". The conflict occurred in database "fiddle_34951bde3c294464b6d71a1713fc59a4", table "dbo.a".
Msg 3621 Level 0 State 0 Line 1
The statement has been terminated.

INSERT INTO d (id, e_id, client_id) VALUES (1, 2, 1);
Msg 547 Level 16 State 0 Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__d__45F365D3". The conflict occurred in database "fiddle_34951bde3c294464b6d71a1713fc59a4", table "dbo.e".
Msg 3621 Level 0 State 0 Line 1
The statement has been terminated.

INSERT INTO d (id, e_id, client_id) VALUES (1, 1, 1);
1 rows affected