SQL Server - Multiple foreign key constraint in one go

Question

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:

Employee (Id, Name, DeptId, AreaId, ManagerId)
Area (Id, Name)
Department (Id, Name)

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.

The spec shows that you need to add a comma after each ADD CONSTRAINT line — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Mar 20, 2014 at 9:29
Yes.it is like that. I am not aware about syntax very much. Is it possible? — Dipal Mehta
– Dipal Mehta, Commented Mar 20, 2014 at 9:31

Tanner · Accepted Answer · 2014-03-20 09:34:18Z

2

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)

edited Mar 20, 2014 at 9:34

answered Mar 20, 2014 at 9:28

Tanner

22.8k9 gold badges70 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Dipal Mehta Over a year ago

Yes, That i know but, Is it possible in one ALTER TABLE statement?

Panagiotis Kanavos Over a year ago

The question is how NOT to do that. Besides, GO is not a valid T-SQL command

Tanner Over a year ago

@DipalMehta try the above

Panagiotis Kanavos · Accepted Answer · 2014-03-20 09:35:53Z

1

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

answered Mar 20, 2014 at 9:35

Panagiotis Kanavos

134k16 gold badges211 silver badges271 bronze badges

Comments

Pravellika · Accepted Answer · 2014-03-20 09:41:52Z

0

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)

answered Mar 20, 2014 at 9:41

Pravellika

18214 bronze badges

Collectives™ on Stack Overflow

SQL Server - Multiple foreign key constraint in one go

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related