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 
);
CREATE TABLE dbo.b (
 id int NOT NULL PRIMARY KEY FOREIGN KEY REFERENCES a (id)
);
CREATE TABLE dbo.c (
 id int NOT NULL PRIMARY KEY FOREIGN KEY REFERENCES a (id)
);
CREATE VIEW dbo.UniqueB
WITH SCHEMABINDING
AS
SELECT a.client_id, a.date
FROM dbo.a 
INNER JOIN dbo.b ON b.id = a.id;
CREATE VIEW dbo.UniqueC
WITH SCHEMABINDING
AS
SELECT a.client_id, a.date
FROM dbo.a 
INNER JOIN dbo.c ON c.id = a.id;
CREATE UNIQUE CLUSTERED INDEX UQ_UniqueC__clientid_date ON dbo.UniqueC (client_id, date);
CREATE UNIQUE CLUSTERED INDEX UQ_UniqueB__clientid_date ON dbo.UniqueB (client_id, date);
INSERT INTO a (id, date, client_id) VALUES (1, '2050-01-01', 1);
INSERT INTO b (id) VALUES (1);
INSERT INTO a (id, date, client_id) VALUES (2, '2050-01-01', 1);
3 rows affected

INSERT INTO b (id) VALUES (2); -- should raise error
Msg 2601 Level 14 State 1 Line 1
Cannot insert duplicate key row in object 'dbo.UniqueB' with unique index 'UQ_UniqueB__clientid_date'. The duplicate key value is (1, Jan  1 2050 12:00AM).
Msg 3621 Level 0 State 0 Line 1
The statement has been terminated.

INSERT INTO a (id, date, client_id) VALUES (3, '2050-01-01', 2);
INSERT INTO a (id, date, client_id) VALUES (4, '2050-01-02', 2);
INSERT INTO a (id, date, client_id) VALUES (5, '2050-01-01', 2);
INSERT INTO a (id, date, client_id) VALUES (6, '2050-01-02', 2);
INSERT INTO b (id) VALUES (3);
INSERT INTO b (id) VALUES (4); -- should work
INSERT INTO c (id) VALUES (5);
INSERT INTO c (id) VALUES (6); -- should work
8 rows affected