2

Suppose I have a table such as Users that has a id and a company_id I want to make a 2nd table that has pairs of user id's ex: (primary_key_id, user1_id, user2_id).

The user id's must be in the same company for these pairs of users.

How can I add a constraint to my database to require this?

1 Answer 1

6

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.

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.