1、完整性约束讲解数据库实验报告三完整性约束1、实验目的1. 熟悉主键，外键， check等完整性约束；2. 掌握完整性约束的建立，删除，有效化和无效化；3. 熟悉相关的DDL 命令。4. 学会看数据库模型图。2、实验内容1. 主键约束a 按图1所示， 建立所有表的主键约束。b 运行下列SQL语句：insert into branch values(Xiangtan University,Xiangtan,4000000);insert into branch values(Xiangtan University,Xiangtan,5000000);记录结果并分析原因。2. 外键约束a. 按照图1
2、所示，建立所有的外键约束。b. 运行以下的两条SQL语句：insert into loan values(L-001,Xiangtan University,1000);insert into loan values(L-002,Rain Lake,2000);记录结果并分析原因。c 运行以下的SQL语句：delete branch;记录结果并分析原因。3. unique约束a. 在loan表上建立一个unique约束，保证amount是唯一的。b. 运行以下SQL语句：insert into loan values(L-002,Xiangtan University,2000);记录结果并分
3、析原因。c. 使得刚才建立的约束无效化，并再次运行上一条SQL语句，记录结果并分析原因。d. 删除掉这个约束。4. check约束a. 在loan表上建立一个check约束，保证amount必须为正数。b. 运行以下SQL语句：insert into loan values(L-003,Xiangtan University,-2000);记录结果并分析原因。3、实验结果1、建立所有表的主键约束。ALTER TABLE account ADD CONSTRAINT APK_num PRIMARY KEY (account_number);ALTER TABLE borrower ADD CON
4、STRAINT BCU_name PRIMARY KEY (customer_name,loan_number);ALTER TABLE branch ADD CONSTRAINT BH_name PRIMARY KEY (branch_name);ALTER TABLE customer ADD CONSTRAINT CC_name PRIMARY KEY (customer_name);ALTER TABLE depositor ADD CONSTRAINT DC_name PRIMARY KEY (customer_name,account_number);ALTER TABLE loa
5、n ADD CONSTRAINT LL_num PRIMARY KEY (loan_number);2、运行下列SQL语句：insert into branch values(Xiangtan University,Xiangtan,4000000);insert into branch values(Xiangtan University,Xiangtan,5000000);脚本：1 行 已插入在行 2 上开始执行命令时出错:insert into branch values(Xiangtan University,Xiangtan,5000000)错误报告:SQL 错误: ORA-0000
6、1: 违反唯一约束条件 (2013960833.BH_NAME)00001. 00000 - unique constraint (%s.%s) violated*Cause: An UPDATE or INSERT statement attempted to insert a duplicate key. For Trusted Oracle configured in DBMS MAC mode, you may see this message if a duplicate entry exists at a different level.*Action: Either remove
7、 the unique restriction or do not insert the key.分析：已经插入一行，再插入同名的主键列会出错。违反唯一约束条件。3、建立所有的外键约束。ALTER TABLE borrowerADD CONSTRAINT FK1FOREIGN KEY (customer_name) REFERENCES customer(customer_name);ALTER TABLE borrowerADD CONSTRAINT FK2FOREIGN KEY (loan_number) REFERENCES loan(loan_number);ALTER TABLE l
8、oanADD CONSTRAINT FK3FOREIGN KEY (branch_name) REFERENCES branch(branch_name);ALTER TABLE depositorADD CONSTRAINT FK4FOREIGN KEY (customer_name) REFERENCES customer(customer_name);ALTER TABLE depositorADD CONSTRAINT FK5FOREIGN KEY (account_number) REFERENCES account(account_number);ALTER TABLE accou
9、ntADD CONSTRAINT FK6FOREIGN KEY (branch_name) REFERENCES branch(branch_name);4、运行以下的两条SQL语句：insert into loan values(L-001,Xiangtan University,1000);insert into loan values(L-002,Rain Lake,2000);脚本：1 行 已插入在行 2 上开始执行命令时出错:insert into loan values(L-002,Rain Lake,2000)错误报告:SQL 错误: ORA-02291: 违反完整约束条件 (2
10、013960833.FK3) - 未找到父项关键字02291. 00000 - integrity constraint (%s.%s) violated - parent key not found*Cause: A foreign key value has no matching primary key value.*Action: Delete the foreign key or add a matching primary key.5、运行以下的SQL语句：delete branch;脚本：在行 1 上开始执行命令时出错:delete branch错误报告:SQL 错误: ORA-
11、02292: 违反完整约束条件 (2013960833.FK3) - 已找到子记录02292. 00000 - integrity constraint (%s.%s) violated - child record found*Cause: attempted to delete a parent key value that had a foreign dependency.*Action: delete dependencies first then parent or disable constraint.6、在loan表上建立一个unique约束，保证amount是唯一的。脚本：AL
12、TER TABLE loan 成功。7、运行以下SQL语句：insert into loan values(L-002,Xiangtan University,2000);8、使得刚才建立的约束无效化，并再次运行上一条SQL语句。脚本：在行 1 上开始执行命令时出错:insert into loan values(L-002,Xiangtan University,2000)错误报告:SQL 错误: ORA-00001: 违反唯一约束条件 (2013960833.LL_NUM)00001. 00000 - unique constraint (%s.%s) violated*Cause: An
13、 UPDATE or INSERT statement attempted to insert a duplicate key. For Trusted Oracle configured in DBMS MAC mode, you may see this message if a duplicate entry exists at a different level.*Action: Either remove the unique restriction or do not insert the key.9、删除掉这个约束。alter table loan drop unique (am
14、ount);10、在loan表上建立一个check约束，保证amount必须为正数。alter table loan add check(amount0);11、运行以下SQL语句：insert into loan values(L-003,Xiangtan University,-2000);脚本：在行 1 上开始执行命令时出错:insert into loan values(L-003,Xiangtan University,-2000)错误报告:SQL 错误: ORA-02290: 违反检查约束条件 (2013960833.SYS_C0051122)02290. 00000 - check constraint (%s.%s) violated*Cause: The values being inserted do not satisfy the named check *Action: do not insert values that violate the constraint.分析：插入的amount 数据小于0，违反检查约束条件出错。
