SQL Server(第一章) 建立表 刪除表 建立主鍵約束、唯一約束、外鍵約束、CHECK約束、預設約束

標籤：int   pos   creat   gpo   ble   刪除   time   arc   主鍵約束   

1.Employees員工表

/**建立Employees員工表**/USE TSQL2012IF OBJECT_ID(‘dbo.Employees‘,‘U‘) IS NOT NULLDROP TABLE dbo.EmployeesCREATE 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_EmployeesPRIMARY KEY(empid);/**添加唯一約束**/ALTER TABLE dbo.Employees ADD CONSTRAINT UNQ_Employees_ssnUNIQUE(ssn);/**添加外鍵約束**/ALTER TABLE dbo.Employees ADD CONSTRAINT FK_Employees_EmployeesFOREIGN KEY(mgrid) REFERENCES dbo.Employees(empid);/**添加CHECK約束**/ALTER TABLE dbo.Employees ADD CONSTRAINT CHK_Employees_salaryCHECK(salary>0.00)/**刪除CHECK約束**/ALTER TABLE dbo.Employees DROP CONSTRAINT CHK_Employees_salary

 

2.Orders訂單表

/**    建立Orders訂單表**/USE TSQL2012IF 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_ordertsDEFAULT(SYSDATETIME()) FOR orderts;/**刪除CHECK約束**/ALTER TABLE dbo.Employees DROP CONSTRAINT DFT_Orders_orderts

 

SQL Server(第一章) 建立表 刪除表 建立主鍵約束、唯一約束、外鍵約束、CHECK約束、預設約束