数据库完整性
大连海事大学
数据库原理课程实验大纲
实验名称：实验七完整性实验学时： 2 适用专业：
实验环境：
执笔者：编写日期：
1实验目的
（1）掌握实体完整性、参照完整性和用户自定义完整性约束的创建方法。
（2）掌握完整性约束的运行检查机制。
（3）掌握参照完整性的级联删除和修改方法。
（4）掌握正确设计关系模式完整性约束的方法。
2实验内容
2.1掌握实体完整性约束的创建和使用方法
（1）创建表时定义由一个属性组成的主键（给约束命名）。
（2）创建表时定义由两个或两个以上属性组成的主键（给约束命名）。
（3）删除以上两个主键约束。
（4）利用ALTER TABLE语句定义上述两个主键。
2.2掌握参照完整性约束的创建和使用方法
（5）创建表时定义一个列级参照完整性约束（给约束命名）。
（6）创建表时定义一个表级的由两个属性组成的参照完整性约束（给约束命名）。（7）设计数据更新语句检查参照完整性约束是否起作用。
（8）删除上述完整性约束。
（9）利用ALTER TABLE 建立上述参照完整性约束，并且规定UPDATE/DELETE时的动作。
（10）设计数据更新语句检查参照完整性约束及其相应的动作是否起作用。
2.3掌握用户自定完整性约束的创建和使用方法
（11）定义一个检查约束，检查其值在某个取值范围内，并设计相应的更新语句检查该约束是否起作用
（12）定义一个检查其值符合某个匹配模式的检查约束（使用LIKE），并设计相应的更新语句检查该约束是否起作用
（13）定义一个检查其值符合某个正则表达式的检查约束（使用SIMILAR TO），并设计相应的更新语句检查该约束是否起作用
（14）定义一个UNIQUE约束，并设计相应的更新语句检查该约束是否起作用
（15）定义一个DEFAULT约束，设计一个INSERT语句检查该约束是否起作用。
3实验要求
（1）深入复习教材第五章数据库完整性约束内容。
（2）根据书上的例子，针对TPCH数据库模式设计各种完整性约束，每种类型完整性约束至少要设计一个，描述清楚完整性约束要求，设计和运行触发完整性约束检查的数据更新语句，并截图相应的实验结果，每幅截图并要有较为详细的描述。也可以按照附1所列示例做实验。（3）实验步骤和实验总结中要详细描述实验过程中出现的问题、原因和解决方法。
（4）思考题：完整性约束的违约处理有哪几种方式
4实验步骤
4.1掌握实体完整性约束的创建和使用方法
（1）创建表时定义由一个属性组成的主键（给约束命名）。
创建零件表part时定义主键。/
CREATE TABLE PART
(PARTKEY INTEGER,
MFGR CHAR(100),
TYPE CHAR(25),
SIZE INTEGER,
NAME CHAR(55),
CONTAINER CHAR(10),
BRAND CHAR(10),
RETAILPRICE REAL,
COMMENT VARCHAR(23),
CONSTRAINT PARTY PRIMARY KEY(PARTKEY))
如图：partkey为主键
（2）创建表时定义由两个或两个以上属性组成的主键（给约束命名）。
创建零件供应关系表partsupp时定义主键。
CREATE TABLE PARTSUPP
(PARTKEY INTEGER,
AVAILQTY INTEGER,
SUPPKEY INTEGER,
COMMENT VARCHAR(199),
SUPPLYCOST REAL,
CONSTRAINT PARTSUPPY PRIMARY KEY(PARTKEY,SUPPKEY)
);
如图给点约束名,和SUPPKEY为主键
（3）删除以上两个主键约束。
删除零件表和零件供应关系表的主键。ALTER TABLE PARTSUPP
DROP CONSTRAINT PARTSUPPY;
利用约束名SUPPKEY删除约束
ALTER TABLE PART
DROP CONSTRAINT PARTY
利用约束名PARTY删除约束
（4）利用ALTER TABLE语句定义上述两个主键。
利用ALTER TABLE增加part和partsupp表的主键。ALTER TABLE PART
ADD CONSTRAINT PARTY PRIMARY KEY (PARTKEY);
利用ALTER给表PART增加约束PARTY
ALTER TABLE PARTSUPP
ADD CONSTRAINT PARTSUPPY PRIMARY KEY(PARTKEY,SUPPKEY)
利用ALTER给表增加约束PARTSUPPY主键PARTKEY,SUPPKEY 4.2掌握参照完整性约束的创建和使用方法
（5）创建表时定义一个列级参照完整性约束（给约束命名）。
定义partsupp表的列级参照完整性约束。
CREATE TABLE PARTSUPP
(PARTKEY INTEGER,
AVAILQTY INTEGER,
SUPPKEY INTEGER,
COMMENT VARCHAR(199),
SUPPLYCOST REAL NOT NULL,
CONSTRAINT PARTSUPPY PRIMARY KEY(PARTKEY,SUPPKEY)
);
（6）创建表时定义一个表级的由两个属性组成的参照完整性约束（给约束命名）。
定义lineitem的参照完整性约束。
CREATE TABLE lineitem(
orderkey INTEGER,
partkey INTEGER,
suppkey INTEGER,
linenumber INTEGER,
quantity REAL,
extendedprice REAL,
discount REAL,
tax REAL,
returnflag CHAR(1),
linestatus CHAR(1),
shipDATE DATE,
commitDATE DATE,
receiptDATE DATE,
shipinstruct CHAR(25),
shipmode CHAR(10),
comment VARCHAR(44)
CONSTRAINT LINETY PRIMARY KEY(ORDERKEY,PARTKEY,SUPPKEY),
CONSTRAINT FPART FOREIGN KEY(PARTKEY)REFERENCES PART,
CONSTRAINT FPARTSUPP FOREIGN KEY(SUPPKEY,PARTKEY)REFERENCES PARTSUPP);
创建表时创建外码FPART和FPARTSUPP分别为PART和PARTSUPP表的主码
（7）设计数据更新语句检查参照完整性约束是否起作用。
设计INSERT、DELETE和UPDATE语句检查上述完整性约束是否有效。INSERT INTO lineitem(orderkey,partkey,suppkey)
VALUES('4','2','4')
INSERT INTO PART(PARTKEY,SIZE,RETAILPRICE)
VALUES('1','34','2')
（8）删除上述完整性约束。
删除partsupp和lineitem的参照完整性约束。
ALTER TABLE LINEITEM
DROP CONSTRAINT FPART
ALTER TABLE LINEITEM
DROP CONSTRAINT FPARTSUPP
ALTER TABLE PARTSUPP
DROP CONSTRAINT FPART
（9）利用ALTER TABLE 建立上述参照完整性约束，并且规定UPDATE/DELETE时的动作。
利用ALTER TABLE 建立上述两个完整性约束，并规定UPDATE和DELETE级联修改和删除。ALTER TABLE PARTSUPP
ADD CONSTRAINT FPART FOREIGN KEY(PARTKEY)REFERENCES PART ON DELETE CASCADE ON UPDATE NO ACTION
ALTER TABLE LINEITEM
ADD CONSTRAINT FPS1FOREIGN KEY(PARTKEY)REFERENCES PART ON DELETE CASCADE ON UPDATE NO ACTION
ALTER TABLE LINEITEM ON
ADD CONSTRAINT FPS2FOREIGN KEY(SUPPKEY,PARTKEY)REFERENCES PARTSUPP ON DELETE NO ACTION ON UPDATE NO ACTION
（10）设计数据更新语句检查参照完整性约束及其相应的动作是否起作用。
删除零件供应记录partsupp 或者是 lineitem表中的记录，检查约束是否起作用
删除主键时因为主码约束无法删除
4.3掌握用户自定完整性约束的创建和使用方法
（11）定义一个检查约束，检查其值在某个取值范围内，并设计相应的更新语句检查该约束是否起作用
定义一个检查Lineitem中的折扣只能在0和1之间的CHECK约束，并设计INSERT语句检查约束是否起作用。
ALTER TABLE lineitem ADD CONSTRAINT CHE1CHECK (DISCOUNT between 0 and 1 )
（12）定义一个检查其值符合某个匹配模式的检查约束（使用LIKE），并设计相应的更新语句检查该约束是否起作用
定义一个CHECK约束，检查国家表nation中的国家名称的最后一个字必须为“国”字。设计一个INSERT语句检查该约束是否有效。
alter table nation
add constraint namek check(name like'%国');
（13）定义一个检查其值符合某个正则表达式的检查约束（使用SIMILAR TO），并设计相应的更新语句检查该约束是否起作用
定义一个CHECK约束，检查CUSTOMER表中的PHONE必须由12位数字组成。并设计一个INSERT 语句或者是UPDATE语句检查该约束是否有效。
alter table customer
add constraint CHE3check(PHONE SIMILAR TO('__________'));
不为12位语句已终止
（14）定义一个UNIQUE约束，并设计相应的更新语句检查该约束是否起作用
在NATION表的NAME字段上定义一个UNIQUE约束，不允许国家名重复。设计一个INSERT语句检查约束是否有效。
alter table nation
add constraint CHE4UNIQUE(NAME);
（15）定义一个DEFAULT约束，设计一个INSERT语句检查该约束是否起作用。
在SUPPLIER表中的NATIONKEY字段上定义一个DEFAULT约束，默认取值为中国的国家代码。
并设计一个INSERT语句检查该约束是否有效。
ALTER TABLE SUPPLIER
ADD CONSTRAINT CHE5DEFAULT 1 FOR NATIONKEY
INSERT INTO SUPPLIER(SUPPKEY,NAME)
VALUES('1','张锋')
默认为1，如图示
思考题：冲突处理有四种解决方式：no action,restrict,set null,cascade
No action指在违约时直接回滚不作任何处理，restrict在违约时拒绝执行命令，set 在违约时把违约项设为空值，cascade当当删除或修改参照关系中某元组的主码值时，被参照关系中，那些外部码具有该值的元组也将被删除或修改，以保证参照完整性。
5总结与体会
5.1实验中出现的问题及其解决方案
因为时间过长而且知识点多且杂，在实现时多花了很多时间。为此专门看了一下PPT，了解了这一章讲了什么。有很多实验内容书上没有，为此专门查了很多资料，最终解决了这个问题。
5.2总结
数据库完整性约束是定义数据库一个很重要的东西，使用完整性约束可以避免数据库数据输入错误的问题，提高数据库运行效率。另一方面，数据库完整性约束可以更为方便的定义数据库，使数据库的规范程度提高。
5.3体会
这次实验叫上一次比较简单，但实现起来还是有一些难度，希望下次实验可以很好的解决这些问题。