How do I preserve data integrity with a linking table in PostgreSQL?

Question

I have these tables in my PostgreSQL database:

CREATE TABLE companies (
 id INT PRIMARY KEY,
 name VARCHAR(255) NOT NULL UNIQUE
);
-- Employees belong to one company
CREATE TABLE employees (
 id INT PRIMARY KEY,
 companyId INT NOT NULL,
 employeeName VARCHAR(255) NOT NULL,
 FOREIGN KEY (companyId) REFERENCES companies(id)
);
-- Projects Belong to one company
CREATE TABLE projects (
 id INT PRIMARY KEY,
 companyId INT NOT NULL,
 projectName VARCHAR(255) NOT NULL,
 FOREIGN KEY (companyId) REFERENCES companies(id)
);

Now I need to create a table that links an employee to a project:

-- The linking table (with the problem)
CREATE TABLE assignments (
 id INT PRIMARY KEY,
 employeeId INT NOT NULL,
 projectId INT NOT NULL,
 FOREIGN KEY (employeeId) REFERENCES employees(id),
 FOREIGN KEY (projectId) REFERENCES projects(id)
);

The issue is that an employee from one company, can be linked to a project from another company, which I do not want.

How do I enforce the employee and project to be from the same company?

• Adrian Klaver: First thought is a BEFORE INSERT OR UPDATE trigger on assigments that tests that the employee companyId and project companyID match and then per plpgsql trigger: Row-level triggers fired BEFORE can return null to signal the trigger manager to skip the rest of the operation for this row (i.e., subsequent triggers are not fired, and the INSERT/UPDATE/DELETE does not occur for this row. Or you could throw an exception.
• Frank Heikens: How should it work when one employee works for more than one company? Or moves from one company to another?
• philipxy: (Obviously) This is a longtime duplicate question. But if you don't clearly phrase your question you can't search (or reason or communicate). There's no such clear phrasing in this post, just a lot of fragments. PS Add the company as part of a FK, because company+other has to exist elsewhere.
• philipxy: Please before considering posting read the manual/reference & google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. How much research effort is expected of Stack Overflow users? How to Ask Help center Reflect research in posts. Please in code questions give a minimal reproducible example.
• philipxy: Don't report what LLMs or your cat said, it's irrelevant. If it motivates you to research to find something a competent justified person wrote, you could use a quote with credit or words out of your own mind to add something relevant in presenting your 1 question. But don't just dump something related to your question that isn't part of phrasing your question. PS Using LLMs to compose posts is explicitly not allowed. PS There was no question in the post body. Saying you're not sure is not asking a question. PS So far there is no reason to tag this content with database-normalization.

Answer 1

The clean SQL solution is to add redundant unique constraints that include the companyid to projects and employees and reference those with the foreign keys:

ALTER TABLE projects ADD UNIQUE (companyid, id);
ALTER TABLE employees ADD UNIQUE (companyid, id);
CREATE TABLE assignments (
 id INT PRIMARY KEY,
 employeeid INT NOT NULL,
 projectid INT NOT NULL,
 companyid INT NOT NULL,
 FOREIGN KEY (companyid, employeeid) REFERENCES employees(companyid, id),
 FOREIGN KEY (companyid, projectid) REFERENCES projects(companyid, id)
);

The extra unique index is of course painful, but a consolation is that you should have an index on companyid anyway if you ever want to delete a row in companies.

• Adrian Klaver: I am not seeing companyid in the assignments table or am I missing something?
• Laurenz Albe: I didn't see it either, so I added it. Thanks.