|
-- 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
|