CODEWITHSUNDEEP
sqlsql-serversql-server-2008
CodeMinion
CodeMinion

Reputation: 653

Specify a Composite Foreign Key

I'm trying to create a Foreign Key out of two values in two different tables, some pointers would be appreciated!

Below is the code I am using for this :

CREATE TABLE [dbo].[EmployeeUnsetZone](
    [EmployeeID] [char](2) NULL,
    [ZoneOfficeID] [int] NOT NULL,
    [ZoneID] [char](4) NULL
    CONSTRAINT EmployeeUnsetZone_EmployeeFK FOREIGN KEY([EmployeeID],[ZoneID])
    REFERENCES [Employee]([ID]), [ZoneByOffice]([ID])
)

Upvotes: 1

Views: 108

Answers (1)

OMG Ponies
OMG Ponies

Reputation: 332571

I'd use:

CREATE TABLE [dbo].[EmployeeUnsetZone](
  [EmployeeID] [char](2) NULL,
  [ZoneOfficeID] [int] NOT NULL,
  [ZoneID] [char](4) NULL,
  PRIMARY KEY ([EmployeeID], [ZoneID]),
  CONSTRAINT fk_employeeid FOREIGN KEY([EmployeeID]) REFERENCES [Employee]([ID]),
  CONSTRAINT fk_zoneid FOREIGN KEY([ZoneID]) REFERENCES [ZoneByOffice]([ID])
)

The primary key will stop duplicates, and also setup the clustered key for the two columns to make searching against better.

Upvotes: 1

Related Questions