关 键 词:
DB-完整性控制
chinanetboy在3/29/2008的日记：
设计原则：
原则1：
数据库完整性控制包含下面4级,尽量用前 1-3级，当业务规则太复杂时，才使用第4级的自定义行为
第1级.实体完整性控制
第2级.域完整性
第3级.参照完整性
第4级.用户定义的完整性。
原则2：
尽量用参照完整性的CASCADE(级联策略)代替触发器，因为的触发器会影响数据库的性能
1.前言:
数据库的完整性表明数据库的存在状态是否合理，是通过数据库内容的完整性约束来实现的。
数据库系统检查数据的状态和状态的转换，判定它们是否合理，是否应予接受。
对于每个数据库操作要判定其是否符合完整性约束，全部判定无矛盾时才可以执行.
各种完整性约束作为数据库模式定义的一部分。
既有效防止了对数据库的意外破坏，提高了完整性检测的效率，又可以减轻编程人员的负担。
2.数据完整性类型共4种
实体完整性,域完整性,参照完整性,用户定义的完整性。
2.1.实体完整性：
实体完整性要求表中的每一行必须是符合按一定条件得到的惟一记录
有4种类型：它可以通过主键约束(Primary Key)、惟一约束(Unique key)、标识属性列(IDENTITY),索引约束(index)。
2.1.1主键约束(Primary Key)
原理：
向表中添加主键约束时，database将检查现有记录的列值，以确保现有数据符合主键的规则，
所以在添加主键之前要保证主键列没有空值和重复值,主键可以是单独一列或者多组合而成
代码：
单一主键：create table(column name data type not null Primary Key,...)
组合主键：alter table table_name add constraint 主键名称 PRIMARY KEY (column_name_list)
删除主键：alter table table_name drop Primary Key(column_name)
2.1.2惟一约束(Unique key)
原理：
Unique key主要是为了防止非主键的列名的数据不重复，不是的表的主键，它相当于候选码的功能，
可以用Unique说明该属性的值不能重复出现。 一个表中可以有定义多个column为Unique列
*如果 UNIQUE 约束中没有指定 CLUSTERED 或 NONCLUSTERED，则默认为 NONCLUSTERED。
*每个 UNIQUE 约束都生成一个索引。由 UNIQUE 约束生成的索引不能使表中的非聚集索引超过 249 个，聚集索引超过 1 个。
代码：
定义：
alter table table_name add constraint 唯一约束名 Unique CLUSTERED/NONCLUSTERED (cloumn_name_list)
CONSTRAINT U_store UNIQUE NONCLUSTERED (stor_name, city)//例如：定义唯一约束是同一个城市中没有重复的商店名称
删除：alter table tb drop CONSTRAINT 唯一约束名
2.1.3标识属性列(IDENTITY)
原理：
为表中的记录按照要求自动地生成标识字段的值，通常该字段的值在现实生活中并没有直接的意义可以用表的标识列来实现。
不同的数据库都有自动标识属性专用关键字
代码：
identity(起始值,步长)
CREATE TABLE table_name ( ID int IDENTITY (1, 1) NOT NULL ,,,,)
2.2域完整性
域完整性：就是规定指定column的数据取值范围以及合理性
它可以定义初始化值，取值范围
它主要包括数据类型约束，check约束,default约束
2.2.1数据类型约束
原理：
主要是指定列的数据类型，比如int,long,char,varchar,double,float,real.....
代码：
create table (columnname 数据类型 ,,,,)
create table (id int not null,name char(10) null,chinesename varchar(20))
上面的int,char,varchar就是数据类型约束
2.2.2check约束
原理：
主要是指定例的取值范围,使用CHECK（检查）子句可保证属性值满足某些前提条件
格式：CHECK <条件>,它通常放在create table语句每个列定义的最后
代码：
CHECK (age>=1 AND age<=200) //设定age范围(1-200)
CHECK (sex IN (“男”,”女”)); //设定性别取值为 男,女
CHECK（dno IN(select dno from department)）; //用子查询指定必须是XX表的一个值
create table tablename(sex char(2) not null check(sex IN (“男”,”女”)),,,)//完整代码
.2.3deflaut约束
原理：
主要是设定一个列的初始化值
建议：最好用相应的数据初始化为NULL的列，防止产生NULL，减少客户端的程序检查
字符类型都用default ''初始化为空串,数字则用default 0 初始化为 0，防止表中出现null数据
格式：default <数据内容>
代码：
create table tablename(
rate real not null deflaut 0.08,//初始化它为0.08
remark char(255) null default "" //初始化它为空串
createdate date not null default (getdate())//用当天的日期来初始化createdate
)
2.3参照完整性
参照完整性实现的前提：用foreign key(外键)定义两个表之间的关系
参照完整性定义了一个关系数据库中，不同的表中列之间的关系（父键与外键）。
要求一个表中（子表）的一列或一组列的值必须与另一个表（父表）中的相关一列或一组列的值相匹配。
被引用的列或一组列称为父键，父键必须是主键或惟一键，通常父键为主键，主键表是主表。
引用父键的一列或一组列称为外键，外键表是子表。如果父键和外键属于同一个表，则称为自参照完整性。
子表的外键必须与主表的主键相匹配，只要依赖某一主键的外键存在，主表中包含该主键的行就不能被删除
通用格式:FOREIGN KEY <主表的列名> REFERENCES <参照表>(<参照表的列名>)
定义单列外键:只需要 REFERENCES 子句。
create table name(job_id int NOT NULL DEFAULT 1 REFERENCES jobs(job_id))
定义多列外键:
CONSTRAINT 此表的外键名称 FOREIGN KEY (stor_id, ord_num, title_id) REFERENCES 参考表名称 (stor_id, ord_num, title_id)
通过定义一个表的外键，从而把两表的关系联系起来，下面的三种方法才可以选择
选定义两个名词：父键和子键
父键=定义为一个表的参考表的指定主键，那个参考表通常称为父键
子键=直接引用外键的表的本身，这个表的列，通常称为子键
参照完整性共有3种控制策略：1.RESTRICT(限制策略),2.CASCADE(级联策略),3.SET NULL(置空策略)
2.3.1.RESTRICT(限制策略)
当在子键插入父键不存在的值时，会被数据库拒绝插入
当用户对表进行违反了上述完整性约束、条件的插入、删除或修改操作时，将会被数据库系统拒绝。
2.CASCADE(级联策略)
当对两表之间应用级联策略后，在包含父键的表的进行修改或者删除键值时，对应的参照表的子键会自动被修改或者被删除，以达到两表之间的数据的高度统一性
设置两表之间的级联策略，通常是用数据库的管理器界面进行设置和修改
3.SET NULL(置空策略)
在这种策略下，当删除时，包含父键的表的进行修改或者删除键值时,对应的参照表的子键的值将被置空 ,这是因为找不到参考物，就只能置空值
2.4用户定义的完整性
用户定义的完整性包括：
自定义数据类型 //不同的数据库有不同的数据类型
自定义的规则 //不同的数据库有不同的规则
存储过程 //自定义特制的仹储过程
触发器 //自定义特制的触发器
这个请参考我的BLOG的其它文章
3. 各中约束的管理
约束与数据库中的表和视图一样，可以进行增、删、改的更新操作。
为了改和删约束，需要在定义约束时对其进行命名，在各种约束的说明前加上关键字CONSTRAINT 和该约束的名称即可。
例如：在employee表的create table语句中：
eno char(4) CONSTRAINT PK_employee PRIMARY KEY,
dno char(4)CONSTRAINT FK_employee FOREIGN KEY REFERENCES department(dno);
当对各种约束进行命名后，就可以用ALTER TABLE语句来更新与属性或表有关的各种约束。如：
ALTER TABLE employee DROP CONSTRAINT FK_employee;
ALER TABLE Salary ADD CONSTRAINT RightSalary CHECK(Insure Fund<Rest);
上述的增加约束，实际上也是通过ALTER TABLE语句定义约束的一种形式。
SQL不能直接修改约束，修改某一个约束实际上是用ALTER TABLE 语句先删除该约束，然后再增加一个与该约束同名的新约束。
4.约束定义代码实列
完整的表定义
下例显示 pubs 数据库中所创建的三个表（jobs、employee 和 publishers）的完整表定义，
其中包含所有的约束定义。
/* ************************** jobs table ************************** */
CREATE TABLE jobs
(
job_id smallint IDENTITY(1,1) PRIMARY KEY CLUSTERED,
job_desc varchar(50) NOT NULL DEFAULT ''''New Position - title not formalized yet'''',
min_lvl tinyint NOT NULL CHECK (min_lvl >= 10),
max_lvl tinyint NOT NULL CHECK (max_lvl <= 250)
)
/* ************************* employee table ************************* */
CREATE TABLE employee
(
emp_id empid
CONSTRAINT PK_emp_id PRIMARY KEY NONCLUSTERED
CONSTRAINT CK_emp_id CHECK (emp_id LIKE ''''[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]'''' or emp_id LIKE ''''[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]''''),
fname varchar(20) NOT NULL,
minit char(1) NULL,
lname varchar(30) NOT NULL,
job_id smallint NOT NULL DEFAULT 1 REFERENCES jobs(job_id),
job_lvl tinyint DEFAULT 10,
pub_id char(4) NOT NULL DEFAULT (''''9952'''') REFERENCES publishers(pub_id),
hire_date datetime NOT NULL DEFAULT (getdate())
)
/* ***************** publishers table ******************** */
CREATE TABLE publishers
(
pub_id char(4) NOT NULL CONSTRAINT UPKCL_pubind PRIMARY KEY CLUSTERED
CHECK (pub_id IN (''''1389'''', ''''0736'''', ''''0877'''', ''''1622'''', ''''1756'''') OR pub_id LIKE ''''99[0-9][0-9]''''),
pub_name varchar(40) NULL,
city varchar(20) NULL,
state char(2) NULL,
country varchar(30) NULL DEFAULT(''''USA'''')
)
G. 在列中使用 uniqueidentifier 数据类型
下例创建含有 uniqueidentifier 列的表。该表使用 PRIMARY KEY 约束以确保用户不会在表中插入重复的值，
并在 DEFAULT 约束中使用 NEWID() 函数为新行提供值。
CREATE TABLE Globally_Unique_Data
(
guid uniqueidentifier CONSTRAINT Guid_Default DEFAULT NEWID(),
Employee_Name varchar(60),
CONSTRAINT Guid_PK PRIMARY KEY (Guid)
)
H. 对计算列使用表达式
下例显示如何使用表达式 ((low high)/2) 计算 myavg 计算列。
CREATE TABLE mytable
(
low int,
high int,
myavg AS (low high)/2
)
I. 对计算列使用 USER_NAME 函数
下例在 myuser_name 列中使用 USER_NAME 函数。
CREATE TABLE mylogintable
(
date_in datetime,
user_id int,
myuser_name AS USER_NAME()
)
J. 使用 NOT FOR REPLICATION
下例显示如何在订阅了复制的表中使用 IDENTITY 属性。此表包含 CHECK 约束，以确保此系统生成的 SaleID 值不会增长到为复制发布服务器指派的范围内。
CREATE TABLE Sales
(
SaleID INT IDENTITY(100000,1) NOT FOR REPLICATION,
CHECK NOT FOR REPLICATION (SaleID <= 199999),
SalesRegion CHAR(2),
CONSTRAINT ID_PK PRIMARY KEY (SaleID)
)
If you believe an article violates your rights or the rights of others, please contact us.