附数据完整性

1、约束,约束定义了必须遵循的用于维护数据一致性和正确性的规则，是强制实现数据完整性的主要途径。约束有5种类型，包括：主键约束、唯一性约束、检查约束、默认约束、外键约束（参照约束）。 约束可以在两个层次上实施： 列级：用户定义的约束只对表中的一列起作用； 表级：用户定义的约束对表中的多列起作用。,强制数据完整性的约束机制,一、主键约束（PRIMARY KEY）(实体) 二、唯一性约束（UNIQUE）(实体) 三、检查约束（CHECK）(域) 四、默认约束（DEFAULT）(域) 五、外键约束（FOREIGN KEY）(参照) 六、不为空约束（NOT NULL）(域),1约束的创建、修改 （1）使用

2、CREATE TABLE语句创建约束 使用CREATE TABLE语句创建约束是在创建表时定义约束，约束是表格定义的一部分。其语法形式为： CREATE TABLE table_name ( column_name data_type CONSTRAINT constraint_name constraint_type ,n ) constraint_name：要创建的约束的名字。若缺省约束名，则SQL Server会自动为约束提供一个名字。 constraint_type：要创建的约束类型。,（2）使用ALTER TABLE语句创建约束 在已有的表上创建、修改约束可以使用ALTER TABL

3、E命令。其语法形式为： ALTER TABLE table_name WITH CHECK | WITH NOCHECK ADD CONSTRAINT constraint_name constraint_type WITH CHECK | WITH NOCHECK：新加入的约束对表中现有的数据是否进行检查。 CONSTRAINT constraint_name：强制起索引文件名 2约束的删除 语法形式为：ALTER TABLE table_name DROP CONSTRAINT constraint_name,一、主键约束（PRIMARY KEY） 主键用于唯一地标识表中每一条记录。我们可

4、以定义表中的一列或多列为主键，则主键列上没有两行具有相同值，也不能为空值。 1使用T-SQL语句创建主键约束 创建主键约束的语法形式： CONSTRAINT constraint_name PRIMARY KEY CLUSTERED | NONCLUSTERED ( col_name ,n),例1 创建Orders表，包括OrderID、CustomerID、SaleID和OrderDate四个字段，其中OrderID字段设为主键。 CREATE TABLE Orders ( OrderID int PRIMARY KEY, CustomerID char(3), SaleID char(3)

5、, OrderDate datetime) 或 CREATE TABLE Orders (OrderID int CONSTRAINT pk_orderid PRIMARY KEY, CustomerID char(3), SaleID char(3), OrderDate datetime),例2 已有Salers表，包含SaleID、SaleName、Sex等字段，将该表中SaleID字段设为主键。,注意：即使在创建主键约束时带有WITH NOCHECK选项，系统总要对现存数据进行检查，若现有数据在该列上出现重复或空值，SQL Server会提示错误信息，并拒绝执行创建主键约束操作。,AL

6、TER TABLE Salers ADD CONSTRAINT pk_saleid PRIMARY KEY(Saleid),例3 OrderDetails表包含OrderID、ProductID和Quantity三个字段，在OrderID和ProductID上创建主键。,ALTER TABLE OrderDetails ADD CONSTRAINT pk_order_product PRIMARY KEY(orderid,productid),主键约束定义在不止一列上时，一列中的值可以重复，但主键约束定义中的所有列的组合值必须唯一。,例4 删除例3-21中创建的主键约束。 ALTER TABL

7、E Salers DROP CONSTRAINT pk_saleid 2使用企业管理器创建主键约束 在企业管理器下也可创建、修改、删除主键约束。只需进入企业管理器，选中需要添加主键约束的表，右键单击，在系统弹出的快捷菜单中选择“设计表”命令，在弹出的窗口中设置、取消主键即可。,二、唯一性约束（UNIQUE） 唯一性（UNIQUE）约束用来限制不受主键约束的列上的数据的唯一性，即表中任意两行在指定列上都不允许有相同的值。一个表上可以放置多个UNIQUE约束。 唯一性约束和主键约束的区别： 唯一性约束允许在该列上存在NULL值，而主键约束限制更严格，不但不允许有重复，而且也不允许有空值。 在创建唯

8、一性约束和主键约束时可以创建簇索引和非簇索引，但在缺省情况下主键约束产生簇索引，而唯一性约束产生非簇索引。,1使用T-SQL语句创建唯一约束 创建唯一性约束的语法形式为： CONSTRAINT constraint_name UNIQUE CLUSTERED | NONCLUSTERED ( col_name ,n) 例5 创建表department，包含dep_id、dep_name以及dep_head三个字段，并在dep_id字段上创建主键约束，在dep_name字段上创建唯一性约束。 CREATE TABLE department (dep_id int PRIMARY KEY, dep

9、_name char(20) CONSTRAINT unq_depname UNIQUE, dep_head char(5) ),例6 在salers表的Telephone字段建立唯一性约束。 ALTER TABLE Salers ADD CONSTRAINT unq_telephone UNIQUE(Telephone) 2使用企业管理器创建唯一性约束 在企业管理器下也可创建、修改、删除唯一约束。只需进入企业管理器，选中需要添加唯一约束的表，右键单击，在系统弹出的快捷菜单中选择“所有任务管理索引”命令，在弹出的窗口中设置、修改、取消唯一约束即可。,三、检查约束（CHECK） CHECK约束用

10、来指定某列的可取值的范围。它通过限制输入到列中的值来强制域的完整性。我们可以在单列上定义多个CHECK约束，以它们定义的顺序来求值。 1使用T-SQL语句创建检查约束 语法形式为： CONSTRAINT constraint_name CHECK (expression),例7 创建学生表s，包含sid（学号）、sname（姓名）、sage（年龄）以及scity（城市）四个字段，并在sage字段创建一个CHECK约束，使得sage的值在1830岁之间。,CREATE TABLE s (sid int PRIMARY KEY, sname char(20), sage int CONSTRAIN

11、T check_age CHECK (sage=18 AND sage=30), scity char(10) ),上面语句还可写成如下形式： CREATE TABLE s (sid int PRIMARY KEY, sname char(20), sage int CONSTRAINT check_age CHECK (sage BETWEEN 18 AND 30), scity char(10) ),例8 修改学生表s，在scity字段创建一个CHECK约束，以限制只能输入有效的城市。,ALTER TABLE s WITH NOCHECK ADD CONSTRAINT check_city

12、 CHECK (scity IN(北京,上海,天津,重庆),例9 修改Salers表，在Telephone字段创建一个CHECK约束，使得该字段的值的格式为（0-90-90-9）0-90-90-90-90-90-90-90-9。,ALTER TABLE Salers ADD CONSTRAINT check_telephone CHECK (Telephone LIKE (0-90-90-9)0-90-90-90-90-90-90-90-9),注意：不能在具有IDENTITY属性的列上设置CHECK约束,2使用企业管理器创建检查约束 在企业管理器下也可创建、修改、删除检查约束。只需进入企业管理

13、器，选中需要添加检查约束的表，右键单击，在系统弹出的快捷菜单中选择“设计表”命令，在弹出的窗口中选中对应的字段，右键单击，在系统弹出的快捷菜单中选择“CHECK约束”命令，在弹出的窗口中选中设置、修改、取消检查即可。,四、默认约束（DEFAULT） DEFAULT约束用于给表中指定列赋予一个常量值（默认值），当向该表插入数据时，如果用户没有明确给出该列的值，SQL Server自动为该列输入默认值。每列只能有一个DEFAULT约束。 1使用T-SQL语句创建默认约束 创建默认约束的语法形式为： CONSTRAINT constraint_name DEFAULT (expression | N

14、ULL) FOR column_name,例10 修改Orders表，在OrderDate字段创建DEFAULT约束，将当前日期设为默认值，当未给Orders表的订货日期提供值时，取当前日期插入。 ALTER TABLE Orders ADD CONSTRAINT default_date DEFAULT getdate() FOR OrderDate 注意： 不能在具有IDENTITY属性的列上设置DEFAULT约束 DEFAULT约束只能用于INSERT语句。 如果对一个已经有数据的表添加DEFAULT约束，原来的数据不会得到默认值。,2使用企业管理器创建默认约束 在企业管理器下也可创建、

15、修改、删除默认约束。只需进入企业管理器，选中需要添加检查约束的表，右键单击，在系统弹出的快捷菜单中选择“设计表”命令，在弹出的窗口中选中对应的字段，在下方“列”中“默认” 中设置、修改、取消默认即可。,五、外键约束（FOREIGN KEY） 外键约束用于与其他表（称为参照表）中的列（称为参照列）建立连接。外键的建立是通过将参照表中的主键所在列或具有唯一性约束的列包含在另一个表中，这些列就是另一个表的外键。当参照表中的参照列更新后，外键列也会自动更新，从而保证两个表之间的一致性关系 。,1使用T-SQL语句创建外键约束 创建外键约束的语法形式为： CONSTRAINT constraint_na

16、me FOREIGN KEY (col_name1,n) REFERENCES table_name(column_name1,n) 其中： col_name1,n：是要实施外键约束的列。 table_name：是参照表表名。 column_name1,n：是参照表中的参照列,例11 若sales数据库中包含salers表和customers表。其中salers表包含SaleID、SaleName等字段，SaleID为主键；customers表包含CustomerID、Company等字段，CustomerID为主键。现创建Orders表，包含OrderID、CustomerID、SaleID

17、和OrderDate四个字段，CustomerID、SaleID为外键。 CREATE TABLE Orders (Orderid int PRIMARY KEY, CustomerID char(3) REFERENCES Customers(CustomerID), SaleID char(3) CONSTRAINT fk_saleid REFERENCES Salers(SaleID), OrderDate datetime DEFAULT getdate() ),例12 修改OrderDetails表，在OrderID字段上创建外键约束。 ALTER TABLE OrderDetail

18、s ADD CONSTRAINT fk_orderid FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),ALTER TABLE OrderDetails WITH NOCHECK ADD CONSTRAINT fk_orderid FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),注意：当将外键约束添加到一个已有数据的列上时，默认情况下，SQL Server将会自动检查表中已有数据，以确保所有的数据和主键保持一致，或者为NULL。但我们也可以根据实际情况的需要，设置SQL Server不对现有数据

19、进行外键约束的检查，如下列所示：,2使用企业管理器创建外键约束 在企业管理器下也可创建、修改、删除外键约束。只需进入企业管理器，选中需要添加外键约束的表，右键单击，在系统弹出的快捷菜单中选择“设计表”命令，在弹出的窗口中选中对应的字段，右键单击，在快捷菜单中选择“关系”，在弹出的窗口中设置、修改、取消外键即可。 见下图：,使用企业管理器创建外键约束图：如cjgl库中xs和cj两表外键约束。,六、不为空约束（NOT NULL）,NOT NULL 限制字段的值不能为空。 1、语法形式为： sp_addtype type_name, system_type ,NULL | NOT NULL 例13 使用T-SQL语句创建自定义数据类型zip，定长字符型，长度为6，不允许为空。,USE cjgl GO EXEC xs zip,char(6),NOT NULL,六、不为空约束（NOT N