SQL Server 约束

约束是一种限制。通过列级或表级设置约束，确保数据符合某种数据完整性规则

下面将介绍一下几种约束：

• 主键约束
• 外键约束
• 唯一约束
• CHECK约束
• DEFAULT约束

1. 主键约束

       主键是数据表中每行的唯一标识符，一个表最多可以有一个主键约束。主键约束确保声明作为

主键的那些列的唯一性。

       创建主键约束有两种方法。一种是在创建表时创建，一种是修改表添加主键约束

1.1 主键约束创建

 1.1.1 在创建表的时候创建主键约束          

CREATE TABLE Users(ID VARCHAR(32) NOT NULL PRIMARY KEY,NAME VARCHAR(50) NOT NULL)

或者

CREATE TABLE Users(ID VARCHAR(32) NOT NULL,NAME VARCHAR(50) NOT NULL,CONSTRAINT PK_USERID PRIMARY KEY (ID))

        当需要指定主键约束的名字 或者 多个列定义主键约束 时，需要使用下面这种

1.1.2 表创建完成之后创建主键约束          

CREATE TABLE Users(ID       VARCHAR(32) NOT NULL,NAME     VARCHAR(50) NOT NULL)GOALTER TABLE UsersADD CONSTRAINT PK_USERID PRIMARY KEY(ID)GO

主键约束创建完成后，可以使用Management Studio查看主键情况，也可以使用系统表进行查询，1.2.3中将进行介绍

1.2 注意事项

1.2.1 可以将主键约束定义的列定义为自增列

CREATE TABLE Users(ID       INT IDENTITY NOT NULL PRIMARY KEY,NAME     VARCHAR(50) NOT NULL)

      这样默认从1开始，每次执行插入语句后增加1，下次就使用增加后的值。也可以设置初始值和增长的幅度，

 IDENTITY(10000, 3) 表示从10000开始，每次增加3

      注意：IDENTITY 作用的列，数据类型必须是 int、bigint、smallint、tinyint 或 decimal，或者是小数位数为 0 的 numeric 数据类型，并且约束为不可为 Null

      当主键为使用Identity定义的自增列是，插入数据使用如下方式     

INSERT INTO Users(NAME)VALUES('User1')

      不能给是自增列的ID列赋值，否则会报错。如果非要自己给ID列赋值的话，得使用  SET IDENTITY_INSERT TableName ON 设置之后，再进行插入

1.2.2 sqlserver中可以使用 NEWID()获取UUID，用于主键约束的列

1.2.3 使用sql查询表的主键约束

SELECT  tab.name AS tabName,  idx.name AS primaryKeyName,  col.name AS colNameFROM  sys.indexes idx    JOIN sys.index_columns idxCol       ON (idx.object_id = idxCol.object_id           AND idx.index_id = idxCol.index_id           AND idx.is_primary_key = 1)    JOIN sys.tables tab      ON (idx.object_id = tab.object_id)    JOIN sys.columns col      ON (idx.object_id = col.object_id          AND idxCol.column_id = col.column_id);

2. 外键约束

        外键 (FK) 是用于建立和加强两个表数据之间的链接的一列或多列。当创建或修改表时可通过定义 FOREIGN KEY 约束来创建外键。

        在外键引用中，当一个表的列被引用作为另一个表的主键值的列时，就在两表之间创建了链接。这个列就成为第二个表的外键。

        和主键约束一样，外键约束的创建方法也有两种。一种是在创建表时创建，一种是修改表添加主键约束

2.1 外键约束创建

2.1.1 在创建表的时候创建外键约束 

CREATE TABLE Orders(Id_O int NOT NULL PRIMARY KEY,OrderNo int NOT NULL,Id_P int FOREIGN KEY REFERENCES Persons(Id_P))

2.1.2 表创建完成之后创建外键约束

CREATE TABLE Orders(Id_O int NOT NULL PRIMARY KEY,OrderNo int NOT NULL,Id_P int )GOALTER TABLE Orders ADD CONSTRAINT FK_Order_Person FOREIGN KEY (Id_P) REFERENCES Persons(Id_P);

2.2 注意事项

2.2.1 使用sql查询外键约束

select   oSub.name  AS  tabName,  fk.name AS foreignKeyName,  SubCol.name AS colName,  oMain.name  AS  referenceTabName,  MainCol.name AS referenceColNamefrom   sys.foreign_keys fk      JOIN sys.all_objects oSub          ON (fk.parent_object_id = oSub.object_id)    JOIN sys.all_objects oMain         ON (fk.referenced_object_id = oMain.object_id)    JOIN sys.foreign_key_columns fkCols         ON (fk.object_id = fkCols.constraint_object_id)    JOIN sys.columns SubCol         ON (oSub.object_id = SubCol.object_id              AND fkCols.parent_column_id = SubCol.column_id)    JOIN sys.columns MainCol         ON (oMain.object_id = MainCol.object_id              AND fkCols.referenced_column_id = MainCol.column_id)；

3. 唯一约束

        可以使用 UNIQUE 约束确保在非主键列中不输入重复的值。尽管 UNIQUE 约束和 PRIMARY KEY 约束都强制

唯一性，但想要强制一列或多列组合（不是主键）的唯一性时应使用 UNIQUE 约束而不是 PRIMARY KEY 约束。

可以对一个表定义多个UNIQUE 约束，但只能定义一个 PRIMARY KEY 约束。而且，UNIQUE 约束允许 

NULL 值，这一点与 PRIMARY KEY 约束不同。不过，当与参与 UNIQUE 约束的任何值一起使用时，

每列只允许一个空值。

        FOREIGN KEY 约束可以引用 PRIMARY约束或UNIQUE 约束。

        唯一约束的创建方法也有两种。

3.1 唯一约束创建

3.1.1 在创建表的时候创建唯一约束 

CREATE TABLE Users(ID int NOT NULL PRIMARY KEY,PhoneNo varchar(11) NOT NULL UNIQUE )

3.1.2 表创建完成之后创建唯一约束

CREATE TABLE Users(ID int NOT NULL PRIMARY KEY,PhoneNo varchar(11) NOT NULL)GOALTER TABLE UsersADD CONSTRAINT U_Users UNIQUE (PhoneNo);

3.2 注意事项

3.2.1 使用sql查询唯一约束

SELECT  tab.name AS tabName,  idx.name AS uniqueKeyName,  col.name AS colNameFROM  sys.indexes idx    JOIN sys.index_columns idxCol       ON (idx.object_id = idxCol.object_id           AND idx.index_id = idxCol.index_id           AND idx.is_unique_constraint = 1)    JOIN sys.tables tab      ON (idx.object_id = tab.object_id)    JOIN sys.columns col      ON (idx.object_id = col.object_id          AND idxCol.column_id = col.column_id);

4. CHECK约束       

         通过限制列可接受的值，CHECK 约束可以强制域的完整性。此类约束类似于 FOREIGN KEY 约束，

因为可以控制放入列中的值。但是，它们在确定有效值的方式上有所不同：FOREIGN KEY 约束从其他表获得有效

值列表，而 CHECK 约束通过不基于其他列中的数据的逻辑表达式确定有效值。例如，可以通过创建 CHECK 约束

将 salary 列中值的范围限制为从 $15,000 到 $100,000 之间的数据这将防止输入的薪金值超出正常的薪金范围。

         可以通过任何基于逻辑运算符返回 TRUE 或 FALSE 的逻辑（布尔）表达式创建 CHECK 约束。

对于上面的示例，逻辑表达式为：salary >= 15000 AND salary <= 100000。

4.1 CHECK约束创建

ALTER TABLE Customers    ADD CONSTRAINT CN_CustomersDateInSystem    CHECK ( DateInSystem <= GETDATE() );

4.2 注意事项

4.2.1 使用sql查询CHECK约束

SELECT  tab.name AS tabName,  chk.name AS checkName,  col.name AS colName,  chk.definition as checkDefinitionFROM  sys.check_constraints chk    JOIN sys.tables tab      ON (chk.parent_object_id = tab.object_id)    JOIN sys.columns col      ON (chk.parent_object_id = col.object_id          AND chk.parent_column_id = col.column_id)

5. DEFAULT约束               

         记录中的每列均必须有值，即使该值是 NULL。可能会有这种情况：必须向表中加载一行数据但不知道某一列

的值，或该值尚不存在。如果列允许空值，就可以为行加载空值。由于可能不希望有可为空的列，因此最好是为

列定义 DEFAULT 定义（如果合适）。例如，通常为数值列指定零作为默认值，为字符串列指定 N/A 作为默认值。

5.1 DEFAULT约束创建

5.1.1 在创建表的时候创建DEFAULT约束 

CREATE TABLE Users(ID int NOT NULL PRIMARY KEY,PhoneNo varchar(11) NOT NULL UNIQUE,EntryDate datetime NOT NULL DEFAULT GETDATE())

5.1.2 表创建完成之后创建DEFAULT约束

CREATE TABLE Users(ID int NOT NULL PRIMARY KEY,PhoneNo varchar(11) NOT NULL UNIQUE,EntryDate datetime NOT NULL)
GOALTER TABLE UsersADD CONSTRAINT D_Users_EntryDate DEFAULT GETDATE() FOR EntryDate;

5.2 注意事项

5.2.1 使用sql查询DEFAULT约束

SELECT  tab.name as tabName,       df.name   AS checkName,       c.name    AS colName,df.definitionFROM   sys.default_constraints df       JOIN sys.[columns]  AS c ON  df.parent_column_id = c.column_id  AND df.parent_object_id = c.[object_id]              JOIN sys.tables as tab ON df.parent_object_id = tab.object_id                     

5.2.2 DEFAULT约束使用注意事项

1） DEFAULT约束默认值只能在INSERT语句中使用

2）如果在INSERT语句中使用了任意值，则不使用默认值

本文参考：

sqlserver数据库约束

强制数据完整性

下篇文章将在本文基础上，完成表结构及约束复制存储过程