利用SQL语言实现数据库的静态完整性

利用SQL语言实现数据库的静态完整性

SQL语言支持的约束类别

SQL语言支持如下几种约束

• 静态约束

  • 列完整性—域完整性约束

  • 表完整性–关系完整性约束

• 触发器
  

SQL语言实现约束的方法-Create Table

Create Table

• CreateTable有三种功能：定义关系模式、定义完整性约束和定义物理存储特性。

• 定义完整性约束条件

  • 列完整性

  • 表完整性

CREATE TABLE tablename
( ( colname datatype [ DEFAULT { default_constant | NULL} ]
[ col_constr {col_constr. . .} ]
| , table_constr
{, { colname datatype [DEFAULT { default_constant | NULL} ]
[col_constr {col_constr. . .} ]
| , table_constr }
. . . } );

Col_constr列约束

• 一种域约束类型，对单一列的值进行约束。

{ NOT NULL | //列值非空
[ CONSTRAINT constraintname ] //为约束命名，便于以后撤消
{ UNIQUE //列值是唯一
| PRIMARY KEY //列为主键
| CHECK (search_cond) //列值满足条件,条件只能使用列当前值
| REFERENCES tablename [(colname) ]
[ON DELETE { CASCADE | SET NULL } ] } } 
//引用另一表tablename的列colname的值，如有ON DELETE CASCADE 或ON DELETE SET 
NULL语句，则删除被引用表的某列值v 时，要将本表该列值为v 的记录删除或列值更新为
null；缺省为无操作 。

• Col_constr列约束：只能应用在单一列上，其后面的约束如UNIQUE, PRIMARY KEY及search_cond只能是单一列唯一、单一列为主键、和单一列相关。

示例

Create Table Student ( S# char(8) not null unique, Sname char(10), 
Ssex char(2) constraint ctssex check (Ssex=‘男’ or 
Ssex=‘女’), Sage integer check (Sage>=1 and Sage<150), 
D# char(2) references Dept(D#) on delete cascade, 
Sclass char(6) );
//假定Ssex只能取{男，女}, 1=<Sage<=150, D#是外键

示例

Create Table Course ( C# char(3) , Cname char(12), Chours integer, 
Credit float(1) constraint ctcredit check (Credit >=0.0 and 
Credit<=5.0 ), T# char(3) references Teacher(T#) on delete 
cascade );
//假定每门课学分最多5分，最少0分

table_constr表约束

• 一种关系约束类型，对多列或元组的值进行约束。

[ CONSTRAINT constraintname ] //为约束命名，便于以后撤消
{ UNIQUE (colname {, colname. . .}) //几列值组合在一起是唯一
| PRIMARY KEY (colname {, colname. . .}) //几列联合为主键
| CHECK (search_condition) //元组多列值共同满足条件
//条件中只能使用同一元组的不同列当前值
| FOREIGN KEY (colname {, colname. . .})
REFERENCES tablename [(colname {, colname. . .})]
[ON DELETE CASCADE] }
//引用另一表tablename的若干列的值作为外键

• table_constr表约束：是应用在关系上，即对关系的多列或元组进行约束，列约束是其特例。

示例

Create Table Student ( S# char(8) not null unique, Sname char(10), 
Ssex char(2) constraint ctssex check (Ssex=‘男’ or 
Ssex=‘女’), Sage integer check (Sage>1 and Sage<150), 
D# char(2) references Dept(D#) on delete cascade, 
Sclass char(6) , primary key(S#)
 );
Create Table Course ( C# char(3) , Cname char(12), Chours integer, 
Credit float(1) constraint ctcredit check (Credit >=0.0 and 
Credit<=5.0 ), T# char(3) references Teacher(T#) on delete 
cascade, primary key(C#),
constraint ctcc check(Chours/Credit = 20) ); 
//假定严格约束20学时一个学分

Create Table SC ( S# char(8), C# char(3), 
Score float(1) constraint ctscore check (Score>=0.0 and 
Score<=100.0), 
forergn key (S#) references student(S#) on delete cascade,
forergn key (C#) references course(C#) on delete cascade );

• check中的条件可以是Select-From-Where内任何Where后的语句，包含子查询。

示例

Create Table SC ( S# char(8) check( S# in (select S# from student)), 
C# char(3) check( C# in (select C# from course)), 
Score float(1) constraint ctscore check (Score>=0.0 and 
Score<=100.0));

• Create Table中定义的表约束或列约束可以在以后根据需要进行撤消或追加。撤消或追加约束的语句是 Alter Table(不同系统可能有差异)。

ALTER TABLE tblname
[ADD ( { colname datatype [DEFAULT {default_const|NULL} ] 
[col_constr {col_constr...} ] | , table_constr } 
{, colname ...}) ]
[DROP { COLUMN columnname | (columnname {, columnname…})}]
[MODIFY ( columnname data-type
[DEFAULT {default_const | NULL } ] [ [ NOT ] NULL ]
{, columnname . . .})]
[ADD CONSTRAINT constr_name]
[DROP CONSTRAINT constr_name]
[DROP PRIMARY KEY ] ;

示例：撤消SC表的ctscore约束(由此可见，未命名的约束是不能撤消)

Alter Table SC
DROP CONSTRAINT ctscore;

示例：若要再对SC表的score进行约束，比如分数在0~150之间，则可新增加一个约束。在Oracle中增加新约束，需要通过修改列的定义来完成

Alter Table SC
Modify ( Score float(1) constraint nctscore check (Score>=0.0 and 
Score<=150.0));

• 有些DBMS支持独立的追加约束,注意书写格式可能有些差异。

示例

Alter Table SC
Add Constraint nctscore check (Score>=0.0 and Score<=150.0) );

SQL语言实现约束的方法-断言

• 一个断言就是一个谓词表达式，它表达了希望数据库总能满足的条件。

• 表约束和列约束就是一些特殊的断言。

• SQL还提供了复杂条件表达的断言。其语法形式为：

CREATE ASSERTION <assertion-name> CHECK <predicate>

• 当一个断言创建后，系统将检测其有效性，并在每一次更新中测试更新是否违反该断言。

• 断言测试增加了数据库维护的负担，要小心使用复杂的断言。

  • “每位教师不能在同一个学期的同一时间段在两个不同的教室上课”

示例

“每笔贷款,要求至少一位借款者账户中存有最低数目的余额，例如1000元”

create assertion balance_constraint check
(not exists (
select * from loan 
where not exists ( 
select * from borrower, depositor, account
where loan.loan_number = borrower.loan_number
and borrower.customer_name = depositor.customer_name
and depositor.account_number = account.account_number
and account.balance >= 1000)));

borrower(customer_name, loan_number,…) //客户及其贷款(一笔贷款的借款者)
account(account_number,…, balance) //账户及其余额
depositor(account_number, customer_name) //客户及其账户(一个借款者的账户)
loan(loan_number, amount) //每一笔贷款

示例

“每个分行的贷款总量必须小于该分行所有账户的余额总和”

create assertion sum_constraint check
(not exists (select * from branch
where (select sum(amount ) from loan
where loan.branch_name = 
branch.branch_name )
>= (select sum (balance ) from account
where account.branch_name = 
branch.branch_name )));

account(branch_name, account_number,…, balance) //分行，账户及其余额
loan(branch_name , loan_number, amount,) //分行的每一笔贷款
branch(branch_name, … ) //分行