You can create a unique constraint across company_id
and id
of the User
table, then create two foreign keys that reference them.
Syntax for MySQL
CREATE TABLE `User` (
id int PRIMARY KEY,
company_id int,
UNIQUE KEY (company_id, id)
);
CREATE TABLE UserPair (
company_id int,
user1_id int,
user2_id int,
PRIMARY KEY (company_id, user1_id, user2_id),
FOREIGN KEY (company_id, user1_id) REFERENCES `User` (company_id, id),
FOREIGN KEY (company_id, user2_id) REFERENCES `User` (company_id, id)
);
INSERT `User` (id, company_id) VALUES
(1,1),
(2,1),
(3,2);
INSERT UserPair (company_id, user1_id, user2_id) VALUES(1,1,2);
INSERT UserPair (company_id, user1_id, user2_id) VALUES(1,1,3);
-- Cannot add or update a child row: a foreign key constraint fails
db<>fiddle (for SQL Server, which is slightly different syntax)
The constraint fails because there is only a single company_id
which each foreign key uses, but one of the corresponding parent rows has a different company_id
.