2.Orders訂單表 ...
1.Employees員工表
/** 創建Employees員工表 **/ USE TSQL2012 IF OBJECT_ID('dbo.Employees','U') IS NOT NULL DROP TABLE dbo.Employees CREATE TABLE dbo.Employees ( empid INT NOT NULL, firstname VARCHAR(30) NOT NULL, lastname VARCHAR(30) NOT NULL, hiredate DATE NOT NULL, mgrid INT NULL, ssn VARCHAR(20) NOT NULL, salary MONEY NOT NULL ) /** 添加主鍵約束 **/ ALTER TABLE dbo.Employees ADD CONSTRAINT PK_Employees PRIMARY KEY(empid); /** 添加唯一約束 **/ ALTER TABLE dbo.Employees ADD CONSTRAINT UNQ_Employees_ssn UNIQUE(ssn); /** 添加外鍵約束 **/ ALTER TABLE dbo.Employees ADD CONSTRAINT FK_Employees_Employees FOREIGN KEY(mgrid) REFERENCES dbo.Employees(empid); /** 添加CHECK約束 **/ ALTER TABLE dbo.Employees ADD CONSTRAINT CHK_Employees_salary CHECK(salary>0.00) /** 刪除CHECK約束 **/ ALTER TABLE dbo.Employees DROP CONSTRAINT CHK_Employees_salary
2.Orders訂單表
/** 創建Orders訂單表 **/ USE TSQL2012 IF OBJECT_ID('dbo.Orders','U') IS NOT NULL DROP TABLE dbo.Orders; CREATE TABLE dbo.Orders ( orderid INT NOT NULL, empid INT NOT NULL, custid VARCHAR(10) NOT NULL, orderts DATETIME2 NOT NULL, qty INT NOT NULL, CONSTRAINT PK_Orders PRIMARY KEY(orderid) ); /** 添加主鍵約束 **/ ALTER TABLE dbo.Orders ADD CONSTRAINT FK_Orders_Employees FOREIGN KEY(empid) REFERENCES dbo.Employees(empid); /** 添加預設約束 **/ ALTER TABLE dbo.Orders ADD CONSTRAINT DFT_Orders_orderts DEFAULT(SYSDATETIME()) FOR orderts; /** 刪除CHECK約束 **/ ALTER TABLE dbo.Employees DROP CONSTRAINT DFT_Orders_orderts