数据库完整性

精选文档大连海事大学数据库原理课程实验大纲实验名称： 实验七 完整性 实验学时： 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);如图给点约束名PARTSUPPY.PARTKEY,和SUPPKEY为主键（3） 删除以上两个主键约束。删除零件表和零件供应关系表的主键。ALTER TABLE PARTSUPPDROP CONSTRAINT PARTSUPPY;利用约束名SUPPKEY删除约束ALTER TABLE PARTDROP CONSTRAINT PARTY利用约束名PARTY删除约束（4） 利用ALTER TABLE语句定义上述两个主键。利用ALTER TABLE增加part和partsupp表的主键。ALTER TABLE PARTADD CONSTRAINT PARTY PRIMARY KEY (PARTKEY);利用ALTER给表PART增加约束PARTYALTER TABLE PARTSUPPADD CONSTRAINT PARTSUPPY PRIMARY KEY(PARTKEY,SUPPKEY)利用ALTER给表增加约束PARTSUPPY主键PARTKEY,SUPPKEY4.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 FPARTALTER TABLE LINEITEMDROP CONSTRAINT FPARTSUPPALTER TABLE PARTSUPPDROP 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 ACTIONALTER TABLE LINEITEMADD CONSTRAINT FPS1 FOREIGN KEY(PARTKEY) REFERENCES PART ON DELETE CASCADE ON UPDATE NO ACTIONALTER TABLE LINEITEM ONADD CONSTRAINT FPS2 FOREIGN 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 CHE1 CHECK (DISCOUNT between 0 and 1 )（12） 定义一个检查其值符合某个匹配模式的检查约束（使用LIKE），并设计相应的更新语句检查该约束是否起作用？定义一个CHECK约束，检查国家表nation中的国家名称的最后一个字必须为“国”字。设计一个INSERT语句检查该约束是否有效。alter table nationadd constraint namek check(name like%国);（13） 定义一个检查其值符合某个正则表达式的检查约束（使用SIMILAR TO），并设计相应的更新语句检查该约束是否起作用？定义一个CHECK约束，检查CUSTOMER表中的PHONE必须由12位数字组成。并设计一个INSERT语句或者是UPDATE语句检查该约束是否有效。alter table customeradd constraint CHE3 check( PHONE SIMILAR TO(_);不为12位语句已终止（14） 定义一个UNIQUE约束，并设计相应的更新语句检查该约束是否起作用？在NATION表的NAME字段上定义一个UNIQUE约束，不允许国家名重复。设计一个INSERT语句检查约束是否有效。alter table nationadd constraint CHE4 UNIQUE(NAME);（15） 定义一个DEFAULT约束，设计一个INSERT语句检查该约束是否起作用。在SUPPLIER表中的NATIONKEY字段上定义一个DEFAULT约束，默认取值为中国的国家代码。并设计一个INSERT语句检查该约束是否有效。ALTER TABLE SUPPLIERADD CONSTRAINT CHE5 DEFAULT 1 FOR NATIONKEYINSERT INTO SUPPLIER(SUPPKEY,NAME)VALUES(1,张锋)默认为1，如图示思考题：冲突处理有四种解决方式：no action,restrict,set null,cascadeNo action指在违约时直接回滚不作任何处理，restrict在违约时拒绝执行命令，set 在违约时把违约项设为空值，cascade当当删除或修改参照关系中某元组的主码值时，被参照关系中，那些外部码具有该值的元组也将被删除或修改，以保证参照完整性。5 总结与体