SQL Server 定義資料完整性 6大約束

標籤：var   ref   pre   ima   定義   主鍵約束   server   varchar   span   

1.建立一客戶張表

 1 IF OBJECT_ID(‘dbo.Employees‘, ‘U‘) IS NOT NULL 2   DROP TABLE dbo.Employees; 3  4 CREATE TABLE dbo.Employees 5 ( 6   empid     INT         NOT NULL, 7   firstname VARCHAR(30) NOT NULL, 8   lastname  VARCHAR(30) NOT NULL, 9   hiredate  DATE        NOT NULL,10   mgrid     INT         NULL,11   ssn       VARCHAR(20) NOT NULL,12   salary    MONEY       NOT NULL13 );

2.主鍵約束

-- Primary keyALTER TABLE dbo.Employees  ADD CONSTRAINT PK_Employees  PRIMARY KEY(empid);  

對於主鍵約束後台將建立一個唯一索引，以物理機制強制邏輯的唯一性限制式

3.唯一約束

-- UniqueALTER TABLE dbo.Employees  ADD CONSTRAINT UNQ_Employees_ssn  UNIQUE(ssn);

4.外鍵約束 建立一張訂單表 插入外鍵

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

-- Foreign keysALTER TABLE dbo.Orders  ADD CONSTRAINT FK_Orders_Employees  FOREIGN KEY(empid)  REFERENCES dbo.Employees(empid);

5.CHECK約束

-- CheckALTER TABLE dbo.Employees  ADD CONSTRAINT CHK_Employees_salary  CHECK(salary > 0.00);

6.預設約束

-- DefaultALTER TABLE dbo.Orders  ADD CONSTRAINT DFT_Orders_orderts  DEFAULT(SYSDATETIME()) FOR orderts;-- CleanupDROP TABLE dbo.Orders, dbo.Employees;

 

SQL Server 定義資料完整性 6大約束