TSQL -> Create primary and foreign keys

We have department number column in Employees table. This example shows the way to create foreign key constraint on this column.

EmployeesDepartments
EmpNoDeptNoFirstNameLastName
11johnsilver
21gretgarbo
32lechen
43danieldefo
DeptNoDeptNameBuildingNum
1management1
2logistic2
3advertizing3
.........


-- add PK on table Departments

ALTER TABLE dbo.Departments ALTER COLUMN DeptNo INT NOT NULL
GO
ALTER TABLE dbo.Departments
ADD CONSTRAINT PK_Departments PRIMARY KEY CLUSTERED
(DeptNo ASC) ON [PRIMARY]
GO

/* add FK on column DeptNo in Employees table referencing DeptNo in Departments */

ALTER TABLE dbo.Employees ALTER COLUMN DeptNo INT NOT NULL
GO

ALTER TABLE dbo.Employees
ADD CONSTRAINT FK_Employees_DeptNo FOREIGN KEY (DeptNo)
REFERENCES Departments (DeptNo);

/*
newlly created FOREIGN KEY constraint does not create index on DeptNo column. If we think it is necessary, we have to define it in additional command
*/

CREATE NONCLUSTERED INDEX idx_Employees_DeptNo
ON dbo.Employees (DeptNo ASC) ON [PRIMARY]
GO




sqlexamples.info