CODEWITHSUNDEEP
sqlsql-server
Dipal Mehta
Dipal Mehta

Reputation: 462

SQL Server - Multiple foreign key constraint in one go

I am very much use to work with ORM (e.g. EF, L2S). So, finding difficulty writing queries.

I've created an Employee table also few masters like Department, Area.

Schema is something like:

Now, I want to add constraint in ONE GO.

Something like, 

ALTER TABLE EMPLOYEE 
ADD CONSTRAINT [FK_EMPLOYEE_DEPARTMENT] FOREIGN KEY (DEPTID) REFERENCES DEPARTMENT(ID)
ADD CONSTRAINT [FK_EMPLOYEE_DEPARTMENT] FOREIGN KEY (AREAID) REFERENCES AREA(ID)
ADD CONSTRAINT [FK_EMPLOYEE_EMPLOYEE] FOREIGN KEY (MANAGERID) REFERENCES EMPLOYEE(ID) --Self Reference

but, I know this is syntax error.

So, please correct my script to enable me to do this. I don't want add constraint one by one.

Thanks in advance.

Upvotes: 0

Views: 63

Answers (3)

Pravellika
Pravellika

Reputation: 182

works with comma. Your query was perfect.

ALTER TABLE EMPLOYEE 
ADD CONSTRAINT [FK_EMPLOYEE_DEPARTMENT] FOREIGN KEY (DEPTID) REFERENCES DEPARTMENT(ID),
ADD CONSTRAINT [FK_EMPLOYEE_DEPARTMENT] FOREIGN KEY (AREAID) REFERENCES AREA(ID),
ADD CONSTRAINT [FK_EMPLOYEE_EMPLOYEE] FOREIGN KEY (MANAGERID) REFERENCES EMPLOYEE(ID)

Upvotes: 0

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131601

You just need to separate each constraint with a comma, eg:

ALTER TABLE EMPLOYEE 
  ADD CONSTRAINT [FK_EMPLOYEE_DEPARTMENT] FOREIGN KEY (DEPTID) REFERENCES DEPARTMENT(ID),
      CONSTRAINT [FK_EMPLOYEE_DEPARTMENT] FOREIGN KEY (AREAID) REFERENCES AREA(ID),
      CONSTRAINT [FK_EMPLOYEE_EMPLOYEE] FOREIGN KEY (MANAGERID) REFERENCES EMPLOYEE(ID)

You can find the syntax of the ALTER TABLE statement in the MSDN documentation and in Books Online

Upvotes: 1

Tanner
Tanner

Reputation: 22743

Try this:

ALTER TABLE EMPLOYEE ADD 
    CONSTRAINT [FK_EMPLOYEE_DEPARTMENT] 
               FOREIGN KEY (DEPTID) REFERENCES DEPARTMENT(ID),
    CONSTRAINT [FK_EMPLOYEE_DEPARTMENT] 
               FOREIGN KEY (AREAID) REFERENCES AREA(ID),
    CONSTRAINT [FK_EMPLOYEE_EMPLOYEE] 
               FOREIGN KEY (MANAGERID) REFERENCES EMPLOYEE(ID)

Upvotes: 2

Related Questions