0





52

Advertisement

A similar question is asked here multiple foreign keys referencing single column in other table but the syntax is not shown in the answer. I would like to know how this can be accomplished in SQL server. The following syntax gives error

ALTER TABLE ItemIssue ADD CONSTRAINT FK_ItemIssue_Person 
FOREIGN KEY (PersonID, AdvisorID) REFERENCES Person (PersonID)
;

ERROR: Number of referencing columns in foreign key differs from number of referenced columns, table 'ItemIssue'.

--  Create Tables 
CREATE TABLE ItemIssue ( 
ItemIssueID int identity(1,1)  NOT NULL,
PersonID int,
AdvisorID int,
    )
;
CREATE TABLE Person ( 
PersonID int NOT NULL,
Name nvarchar(500),
)

;

Question author Shomaail | Source

Advertisement


0


You need to define two foreign keys, one for each column:

ALTER TABLE ItemIssue ADD CONSTRAINT FK_ItemIssue_Person 
   FOREIGN KEY (PersonID) REFERENCES Person (PersonID)
;

ALTER TABLE ItemIssue ADD CONSTRAINT FK_ItemAdvisor_Person 
   FOREIGN KEY (AdvisorID) REFERENCES Person (PersonID)
;
Answer author A-horse-with-no-name