数据完整性(Data Integrity)笔记

因数据库存储数据要持之以恒，数据库中的表需要一些方法验证各种数据类型。不仅仅局限于数据类型，还有唯一值，值的范围，或者某列的值和另外一个表中的列匹配。

当你在定义表的时候其用这些数据验证方法。这叫做声明数据完整性。也就是我们说的表约束。

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

参考文章

09. 约束与索引的联系