CODEWITHSUNDEEP
sql-server
Wyatt Zochert
Wyatt Zochert

Reputation: 31

SQL Server Foreign Keys across multiple table levels

In Microsoft SQL Server, let's say I have the following tables:

CREATE TABLE dbo.Employee
(
  ID int not null,
  Name varchar(50) not null
)
CREATE TABLE dbo.EmployeeAddress
(
  ID int not null,
  EmployeeID int not null,
  Address varchar(50) not null
)
CREATE TABLE dbo.Paycheck
(
  ID int not null,
  EmployeeID int not null,
  AddressID int not null,
  Checkdate datetime not null,
  Amount money not null
)

I know I can create foreign keys to ensure that employeeid in EmployeeAddress exists in the Employee table, and I can create foreign keys to ensure that employeeid and addressid in Paycheck exist in their respective tables. What I want to know is, can I create a constraint that will make sure that the EmployeeID in Paycheck matches the EmployeeID in EmployeeAddress for the AddressID in Paycheck?

Yes, I realize that I could just remove EmployeeID from the Paycheck table and solve the problem, but my real structure (which has nothing to do with employees or their paychecks) is much more complex and doesn't allow that.

Upvotes: 1

Views: 50

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93694

Create a composite primary key in EmployeeAddress table for ID and EmployeeID columns

CREATE TABLE dbo.EmployeeAddress
(
  ID int not null,
  EmployeeID int not null,
  Address varchar(50) not null,
  PRIMARY KEY(ID,EmployeeID)
)

Now you can define foreign key in Paycheck table from Composite primary key of EmployeeAddress table

  ALTER TABLE dbo.Paycheck
  ADD CONSTRAINT FK_Paycheck_EmployeeAddress
  FOREIGN KEY(AddressID, EmployeeID) REFERENCES EmployeeAddress(ID, EmployeeID)

Upvotes: 2

Related Questions