CODEWITHSUNDEEP
databasesql-server-2008t-sql
Bob R
Bob R

Reputation: 617

Foreign composite key to different tables

I am trying to make a unique composed of the modelId and the Item serial number in MS SQL or Transact SQL and I am not sure how to do this.

ALTER TABLE [test].[Inventory] ADD CONSTRAINT [CK_UId_Model_SN]
FOREIGN KEY ([UId]) REFERENCES (Models.ModelId, [Inventory].SerialNum)

Upvotes: 0

Views: 33

Answers (1)

StackUser
StackUser

Reputation: 5398

You cannot create foreign key relationship across multiple tables. Refer the Foreignkey SQL Server Documentation. However you can create composite foreign key like the below.

ALTER TABLE [Inventory] ADD CONSTRAINT [CK_UId_Model_SN] FOREIGN KEY (C1,C2) REFERENCES Models (C1,C2)
ALTER TABLE [Inventory] ADD CONSTRAINT [CK_UId_Model_SN] FOREIGN KEY (C1,C2) REFERENCES Inventory (C1,C2)

Upvotes: 1

Related Questions