Create Tables and Constraints

CREATE TABLE Department
(
     ID INT,
     [Name] NVARCHAR(12)

     CONSTRAINT Uq_ID UNIQUE([Name])
)
 
CREATE TABLE Employee
(
     ID INT,
     [Name] NVARCHAR(12) CONSTRAINT DF_Name DEFAULT('test'),
     Age TINYINT,
     DepartmentName NVARCHAR(12)

 

     CONSTRAINT PK_ID PRIMARY KEY CLUSTERED(ID DESC),
     CONSTRAINT CK_Age Check(Age>10 and Age<50),
     CONSTRAINT FK_Department FOREIGN KEY(DepartmentName) REFERENCES Department([Name])
     ON DELETE  CASCADE 
     ON UPDATE CASCADE 
)

 

ALTER TABLE Employee
ADD phone NVARCHAR(12) CONSTRAINT fk_phone FOREIGN KEY REFERENCES Department([Name])
ON DELETE NO ACTION
ON UPDATE NO ACTION

ALTER TABLE Employee
DROP CONSTRAINT fk_phone

ALTER TABLE Employee
DROP COLUMN phone

ALTER TABLE employee
ADD CONSTRAINT age_default DEFAULT 12 FOR age 

CREATE NONCLUSTERED INDEX IX_Name on Employee(Name ASC) INCLUDE(Age)

CREATE DATABASE NorthwindTrade   ON
  ( NAME = NorthwindTrade_dat1,FILENAME = 'c:/Program files/microsoft sql server/MSSQL.1/MSSQL/data/NorthwindTrade.mdf'),                      
  ( NAME = NorthwindTrade_log1,FILENAME = 'c:/Program files/microsoft sqlserver/MSSQL.1/MSSQL/data/NorthwindTrade.ldf')
  FOR ATTACH;
GO