SQL Sever数据库完整性控制

Summ
900 阅读6分钟

数据完整性概念：
数据完整性就是用于保证数据库中的数据在逻辑上的一致性、正确性和可靠性。强制数据完整性可确保数据库中的数据质量。
数据完整性分类：一般包括3种类型
域完整性、实体完整性、参照完整性

1.域完整性

域完整性又称为列完整性，指给定列输入的有效性，即保证指定列的数据具有正确的数据类型、格式和有效的数据范围。
实现域完整性可通过定义相应的CHECK约束，此外，通过为表的列定义数据类型和NOT NULL也可以实现域完整性。

CHECK约束

CHECK约束实际上是字段输入内容的验证规则，表示一个字段的输入内容必须满足CHECK约束的条件，若不满足，则数据无法正常输入。
CHECK约束可以作为表定义的一部分在创建表时创建，也可以添加到现有表中。
表和列可以包含多个CHECK约束。允许修改或删除现有的CHECK约束。

创建表时定义CHECK约束语法格式：

CREATE TABLE table_name (column) [CONSTRAINT check_name] CHECK (表达式)
如：

CREATE TABLE  SC
        (Sno  CHAR(9)， 
         Cno  CHAR(4)，  
         Grade SMALLINT CHECK (Grade>=0  AND  Grade<=100) ,/*为Grade增加约束，取值范围为0~100*/
         PRIMARY  KEY (Sno，Cno)，  
	 FOREIGN  KEY (Sno) REFERENCES  Student(Sno)，
	 FOREIGN  KEY (Cno)  REFERENCES  Course(Cno)
  );

修改表时定义CHECK约束语法格式：

ALTER TABLE table_name ADD [CONSTRAINT check_name] CHECK (表达式)
如：

ALTER  TABLE  t1  
   ADD   CONSTRAINT   CHK_Sage   
                 CHECK(Sage>16  and Ssex in ('男','女'))

修改表时定义CHECK约束语法格式：

ALTER TABLE table_name [WITH CHECK | WITH NOCHECK] ADD [CONSTRAINT check_name] CHECK (表达式)

说明：
1) WITH CHECK选项表示CHECK约束同时作用于已有数据和新数据；当省略该选项，取默认设置时，也表示CHECK约束同时作用于已有数据和新数据；
2) WITH NOCHECK选项表示CHECK约束仅作用于新数据，对已有数据不强制约束检查。

使用T-SQL语句删除CHECK约束语法格式：

ALTER TABLE table_name DROP [CONSTRAINT] check_name
如：

ALTER  TABLE t1 DROP CONSTRAINT CHK_Sage

注：
有ADD CONSTRAINT必须命名！
无ADD CONSTRAINT不能主动命名！
同一个数据库中的约束名字不可以相同，即使不在同一个表。

默认值约束

创建表时定义DEFAULT约束语法格式：

CREATE TABLE table_name <column> [CONSTRAINT default_name] default (表达式)
如：

CREATE  TABLE  CLASS                       
 (CLSNO  CHAR(8),
  NUM   INT  CONSTRAINT  DEF_NUM   DEFAULT   30)

修改表时定义DEFAULT约束语法格式：

ALTER TABLE table_name ADD [CONSTRAINT default_name] default <表达式> for <column>
如：

ALTERTABLE STU
  ADD CONSTRAINT DEF_SAGE DEFAULT 18 FOR SAGE

删除DEFAULT约束，同上

ALTER TABLE table_name DROP [CONSTRAINT] constraint_name

2．实体完整性

实体完整性又称为行完整性，是用于保证数据表中每一个特定实体的记录都是唯一的。
通过PRIMARY KEY约束、UNIQUE约束、IDENTITY属性或索引可以实现数据的实体完整性。

PRIMARY KEY约束

PRIMARY KEY约束可以在表中定义一个主键，来唯一地标识表中的行。
主键可以是一列或列组合。
一个表只能有一个PRIMARY KEY约束，而且每个表都应有一个主键。
如果已有PRIMARY KEY约束，则可对其进行修改或删除。
要修改PRIMARY KEY约束，必须先删除现有的，然后再用新定义重新创建。
注：
当向表中的现有列添加PRIMARY KEY约束时，SQL SERVER 2008检查列中现有的数据以确保现有数据遵从主键的规则（无空值和重复值）。如果PRIMARY KEY约束添加到具有空值或重复值的列上，SQL SERVER将不执行该操作并返回错误信息。
当PRIMARY KEY约束由另一表的FOREIGN KEY约束引用时，不能删除被引用的PRIMARY KEY约束，要删除它，必须先删除引用的FOREIGN KEY约束。或设置级联删除。

创建表时定义PRIMARY KEY约束

CREATE TABLE table_name ...... PRIMARY KEY...
PRIMARY KEY子句的位置/顺序

CREATE TABLE sc2
(sno CHAR(8),
 cno char (10),
 g INT,
 primary key (sno , cno)
 )    /*使用较多*/

CREATE TABLE sc3
(sno CHAR(8),
 cno char (10),
 primary key (sno , cno),
 g INT
)

CREATE TABLE sc4
(primary key (sno , cno),
 sno CHAR(8),
 cno char (10),
 g INT 
 )

CREATE TABLE sc5
(sno CHAR(8) primary key (sno , cno),
 cno char (10),
 g INT 
 )

修改表时增加PRIMARY KEY约束

ALTER TABLE table_name ADD [CONSTRAINT constraint_name] PRIMARY KEY(...)
如：

alter table 教材 add primary key(书名)

 alter  table  q  add  primary key(q1 desc,q2 )

修改表时删除PRIMARY KEY约束，同上

ALTER TABLE table_name DROP [CONSTRAINT] constraint_name

UNIQUE约束

如果要确保一个表中的非主键列不输入重复值，应在该列上定义UNIQUE约束（唯一约束）。
在允许空值的列上保证唯一性时，应使用UNIQUE约束而不是PRIMARY KEY约束，不过在该列中只允许有一个NULL值。
注：
PRIMARY KEY约束与UNIQUE约束的主要区别如下：
（1）一个数据表只能定义一个PRIMARY KEY约束，但一个表中可根据需要对不同的列定义若干个UNIQUE约束。
（2）PRIMARY KEY字段的值不允许为NULL，而UNIQUE字段的值可取NULL。
（3）一般创建PRIMARY KEY约束时，系统会自动产生索引，索引的默认类型为簇索引。 创建UNIQUE约束时，系统会自动产生一个UNIQUE索引，索引的默认类型为非簇索引。
PRIMARY KEY约束与UNIQUE约束的相同点在于：二者均不允许表中对应字段存在重复值。

创建表时定义UNIQUE约束

CREATE TABLE table_name <column> [CONSTRAINT constraint_name] UNIQUE
如：

CREATE TABLE Student          
	  ( PRIMARY KEY (Sno) ， /* 表级完整性约束*/   
            Sno   CHAR(9)，
            Sname  CHAR(20) UNIQUE，
            Ssex    CHAR(2)，
            Sage   SMALLINT，
            Sdept  CHAR(20)   
           )；

修改表时增加UNIQUE约束

ALTER TABLE table_name ADD [CONSTRAINT constraint_name] UNIQUE (column)
如：

alter  table  Student20  add  unique(sname)

删除UNIQUE约束，同上

ALTER TABLE table_name DROP [CONSTRAINT] constraint_name

3．参照完整性

当增加、修改或删除数据表中的记录时，可以借助参照完整性来保证相关联表之间数据的一致性。
参照完整性可以保证主表中的数据与从表中数据的一致性。
在SQL SERVER 2008中，参照完整性是通过定义外键与主键之间或外键与唯一键之间的对应关系来实现的。

FOREIGN KEY外键

对两个相关联的表（主表与从表）进行数据插入和删除时，通过参照完整性保证它们之间数据的一致性。
利用FOREIGN KEY定义从表的外键，利用PRIMARY KEY或UNIQUE约束定义主表的主键或唯一键（不允许为空），可实现主表与从表之间的参照完整性。
定义表间参照关系：先定义主表主键约束（或唯一键约束），再对从表定义外键约束。

创建表时定义外键约束

CREATE TABLE table_name ( <column> [CONSTRAINT constraint_name] [FOREIGN KEY] REFERENCES ref_table (ref_column) ...... )
如：

CREATE TABLE  Course
     ( Cno  CHAR(4) PRIMARY KEY，
       Cname  CHAR(40)，            
       Cpno CHAR(4) FOREIGN KEY REFERENCES Course(Cno)，/*Cpno是外码，被参照表是Course，被参照列是Cno*/
       Ccredit  SMALLINT
      );

修改表时增加外键约束：一次只能增加一个外键约束！

ALTER TABLE table_name ADD [CONSTRAINT constraint_name] FOREIGN KEY (column) REFERENCES ref_table (ref_column)
如果需要为一个表(如xscj)增加两个外键，只能一个一个地
增加，即写两个alter table xscj ...add constraint  语句。

alter  table  XSCJ   add  constraint  FK1 
            foreign  key(学号)  references  XSDA(学号)

alter  table  XSCJ   add   constraint   FK2
            foreign key(课程编号) references  KCXX(课程编号)

可以用一个alter table xscj drop constraint...  语句删除多个外键：

alter  table  xscj  drop  [constraint]  fk1, fk2

设置级联修改

先删除原来的外键

alter table xscj 
drop [constraint]  FK_kcxx_课程编号

再增加新的带CASCADE的外键

alter table xscj 
add constraint FK_kcxx_课程编号
foreign key(课程编号) references 
            kcxx(课程编号) on update cascade

删除表间的参照关系

删除表间的参照关系，实际上删除表的外键约束即可。语法格式与前面其他约束删除的格式相同。
ALTER TABLE table_name DROP [CONSTRAINT] constraint_name 如：

alter  table  q  DROP constraint FK__q__q1__61F08603

注： 主键、外键、唯一值、默认值、check约束都是按约束constraint删除

avatar
文章
阅读
粉丝