因数据库存储数据要持之以恒，数据库中的表需要一些方法验证各种数据类型。不仅仅局限于数据类型，还有唯一值，值的范围，或者某列的值和另外一个表中的列匹配。
当你在定义表的时候其用这些数据验证方法。这叫做声明数据完整性。也就是我们说的表约束。
USE tempdb
GO
CREATE TABLE s
(
sid VARCHAR(20) ,
sname VARCHAR(20) ,
ssex VARCHAR(2) CHECK ( ssex = '男'
OR ssex = '女' )
DEFAULT '男' ,
sage INT CHECK ( sage BETWEEN 0 AND 100 ) ,
sclass VARCHAR(20) UNIQUE ,
CONSTRAINT PK_s PRIMARY KEY ( sid, sclass )
)
CREATE TABLE t
(
teacher VARCHAR(20) PRIMARY KEY ,
sid VARCHAR(20) NOT NULL ,
sclass VARCHAR(20) NOT NULL ,
num INT ,
FOREIGN KEY ( sid, sclass ) REFERENCES s ( sid, sclass )
)
主键约束 Primary Key Constraints
primary key = unique constraint + not null constraint
创建主键约束时，数据库自动创建唯一索引，默认为聚集索引
创建方法一
-- Primary Key Constraints
CREATE TABLE Production.Categories
(
categoryid INT NOT NULL IDENTITY,
categoryname NVARCHAR(15) NOT NULL,
description NVARCHAR(200) NOT NULL,
CONSTRAINT PK_Categories PRIMARY KEY(categoryid)
);
创建方法二
USE TSQL2012;
ALTER TABLE Production.Categories
ADD CONSTRAINT PK_Categories PRIMARY KEY(categoryid);
GO
列出数据库中的主键约束
-- To list the primary key constraints in a database, you can query the sys.key_constraints table filtering on a type of PK:
SELECT *
FROM sys.key_constraints
WHERE type = 'PK';
唯一性约束 Unique Constraints
仅可以有一行为NULL，ORACLE中可以有多行列值为NULL。
创建唯一键约束时，数据库自动创建唯一索引，默认为非聚集索引
在保证数据唯一性上，唯一索引、唯一约束并没有区别，那么应该使用约束还是索引？
约束定义通常出现在数据库逻辑结构设计阶段，即定义表结构时，索引定义通常出现在数据库物理结构设计/查询优化阶段。
从功能上来说唯一约束和唯一索引没有区别，但在数据库维护上则不太一样，对于唯一约束可以用唯一索引代替，以方便维护，但是主键约束则没法代替。
ALTER TABLE Production.Categories
ADD CONSTRAINT UC_Categories UNIQUE (categoryname);
GO
列出数据库中的唯一约束
SELECT *
FROM sys.key_constraints
WHERE type = 'UQ';
外键 Foreign Key Constraints
创建
ALTER TABLE Production.[Products] WITH CHECK
ADD CONSTRAINT [FK_Products_Categories] FOREIGN KEY(categoryid)
REFERENCES Production.Categories (categoryid)
查找外键
SELECT *
FROM sys.foreign_keys
WHERE name = 'FK_Products_Categories';
删除外键
ALTER TABLE Production.Products DROP CONSTRAINT FK_Products_Categories;
检查约束 Check Constraints
创建
ALTER TABLE Production.Products WITH CHECK
ADD CONSTRAINT CHK_Products_unitprice
CHECK (unitprice>=0);
GO
ALTER TABLE dbo.NewTable
ADD ZipCode INT NULL
CONSTRAINT CHK_ZipCode
CHECK (ZipCode LIKE '[0-9][0-9][0-9][0-9][0-9]');
查找检查约束
SELECT *
FROM sys.check_constraints
WHERE parent_object_id = OBJECT_ID(N'Production.Products', N'U');
默认约束 Default Constraints
创建
CREATE TABLE Production.Products
(
productid INT NOT NULL IDENTITY,
productname NVARCHAR(40) NOT NULL,
supplierid INT NOT NULL,
categoryid INT NOT NULL,
unitprice MONEY NOT NULL
CONSTRAINT DFT_Products_unitprice DEFAULT(0),
discontinued BIT NOT NULL
CONSTRAINT DFT_Products_discontinued DEFAULT(0),
);
查找
SELECT *
FROM sys.default_constraints
WHERE parent_object_id = OBJECT_ID(N'Production.Products', 'U');
启用和禁用约束检查
ALTER TABLE Products NOCHECK CONSTRAINT CHK_Price;
ALTER TABLE Products CHECK CONSTRAINT CHK_Price;
检查在SQL Server中的约束
--Easiest way to check for the existence of a constraint (and then do something such as drop it if it exists) is to use the OBJECT_ID() function...
IF OBJECT_ID('CK_ConstraintName', 'C') IS NOT NULL
ALTER TABLE dbo.[tablename] DROP CONSTRAINT CK_ConstraintName
--OBJECT_ID can be used without the second parameter ('C' for check constraints only) and that may also work, but if your constraint name matches the name of other objects in the database you may get unexpected results.
IF OBJECT_ID('CK_ConstraintName') IS NOT NULL
ALTER TABLE dbo.[tablename] DROP CONSTRAINT CK_ConstraintName
--OBJECT_ID can also be used with other "constraints" such as Foreign Key constraints or Primary Key constraints, etc. For best results, always include the appropriate object type as the second parameter for the OBJECT_ID function:
--Constraint Object Types:
--•C = CHECK constraint
--•D = DEFAULT (constraint or stand-alone)
--•F = FOREIGN KEY constraint
--•PK = PRIMARY KEY constraint
--•R = Rule (old-style, stand-alone)
--•UQ = UNIQUE constraint
参考文章