CODEWITHSUNDEEP
sqlt-sql
Tong
Tong

Reputation: 755

SQL - How to use a foreign key from parent in child?

Is it possible to copy (automatically) or add a constraint that forces a child record to use the same foreign key that its parent record is using.

Example:

Company Table
-----------------
Id | Name
-------------
1  | Pepsi
2  | Coke


Department Table
------------------------
Id | Name           | CompanyId
----------------------------
1  | Human Resources|  1
2  | Sales          |  2

Employee Table
------------------------
Id | Name           |  DepartmentId |  CompanyId
------------------------------------------------
1  | Bob            |  1            |  X
2  | Jim            |  2            |  X

In the employee table, I want there to be a constraint that X must be the same as the CompanyId in DepartmentId.

Upvotes: 0

Views: 94

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

My first thought is: Don't store the company id in the employee table. Always look it up through the department. That is the "correct" solution.

If you have a reason to duplicate this data, you can manage that with a unique constraint/index and a foreign key to both columns.

alter table department add constraint unq_department_company_department
    unique (companyId, departmentId);

You can then use this for the foreign key:

alter table employee add constraint fk_employee_department
    foreign key (companyId, departmentId) references department(companyId departmentId);

Upvotes: 2

Related Questions