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

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