Add Foreign Key in SQL Server 2005

In this section we will learn on defining a Foreign Key Constraint. In simple words lets try to define a relationship between two tables.

Lets create a Employee table with below structure.

CREATE TABLE Employee(
EMP_ID int IDENTITY(1,1) NOT NULL,
ENAME varchar(50) NOT NULL,
DOB datetime NOT NULL,
SALARY decimal(18, 0) NOT NULL,
DEPTNO int NOT NULL
)

Now lets define Primary Key on EMP_ID column, though this is not mandatory but it is a good practise. Check out the link on Adding a Primary Key on the table.

Lets look at the syntax:

ALTER TABLE EMPLOYEE ADD CONSTRAINT FK_EMPLOYEE_01 FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO)

It is very simple isn't it. Similar to Primary Key syntax with a little change.