实验08-参照完整性

# 实验8、参照完整性
## 8.1	实验目的
学习建立外键，以及利用FOREIGN KEY…REFERENCES子句以及各种约束保证参照完整性。
## 8.2	实验内容
1.	为演示参照完整性，建立表Course，令Cno为其主键，参考实验7建立表Stu_Union并在Stu_Union中插入数据。为下面的实验步骤做预先准备。
2.	建立表SC，令Sno和Cno分别为参照Stu_Union表以及Course表的外键，设定为级联删除，并令(Sno,Cno)为其主键。在不违反参照完整性的前提下，插入数据。
3.	演示违反参照完整性的插入数据。
4.	在Stu_Union中删除数据，演示级联删除。
5.	在Course中删除数据，演示级联删除。
6.	为了演示多重级联删除，建立Stu_Card表，令Card_id为其主键，令Stu_id为外键，参考Stu_Union表，并插入数据。
7.	为了演示多重级联删除，建立ICBC_Card表，令Bank_id为其主键，令Stu_card_id为外键，参考Stu_Card表，并插入数据。
8.	通过删除Stu_union表中的一条记录，演示4个表的多重级联删除。
9.	演示事务中进行多重级联删除失败的处理。修改ICBC_Card表的外键属性，使其变为On delete No action，演示事务中通过删除Stu_union表中的一条记录，多重级联删除失败，整个事务回滚到事务的初始状态。
10.	演示互参考问题及其解决方法。要建立教师授课和课程指定教师听课关系的两张表，规定一个教师可以授多门课，但是每个课程只能指定一个教师去听课，所以要为两张表建立相互之间的参照关系。
## 8.3	实验步骤
以系统管理员nbuuser登录到openGauss数据库db_uni，输入如下命令，运行并观察和记录结果。
1.	建立表Course，参考实验7建立表Stu_Union，并分别在表Course和Stu_Union插入数据。
* 建立表Course并插入数据
```sql
CREATE TABLE Course(
	Cno CHAR(4) NOT NULL UNIQUE,
	Cname VARCHAR(50) NOT NULL,
	Cpoints INT,
	CONSTRAINT PK_Course PRIMARY KEY(Cno));

INSERT INTO Course Values('C01','ComputerNetworks',2);	
INSERT INTO Course Values('C02','ArtificialIntelligence',3);
INSERT INTO Course Values('C03','Database',3);
INSERT INTO Course Values('C04','OS',3);
```

* 参考实验7建立表Stu_Union，在表Stu_Union中插入数据
```sql
CREATE TABLE Stu_Union(Sno CHAR(8) NOT NULL UNIQUE,
Sname CHAR(8),
Ssex CHAR(3),
Sage INT,
Sdept CHAR(20),
CONSTRAINT PK_Stu_Union PRIMARY KEY(Sno));

INSERT INTO Stu_Union VALUES('S09','李永','M',25,'EE');
INSERT INTO Stu_Union VALUES('S03','黄浩','F',25,'EE');
INSERT INTO Stu_Union VALUES('S05','汪浩','F',26,'EE');
INSERT INTO Stu_Union VALUES('S02','蒋欣','F',26,'EE');
INSERT INTO Stu_Union Values('S01','李用','M',24,'FF');
INSERT INTO Stu_Union VALUES('S07','李宁','F',26,'CS');
SELECT * FROM Stu_Union;
```
2.	建立表SC，令Sno和Cno分别为参照Stu_Union表以及Course表，作为外键，设定为级联删除，并令(Sno,Cno)为其主键。在不违反参照完整性的前提下，插入数据。
```sql
CREATE Table SC(
Sno CHAR(8),
Cno CHAR(4),
Scredit INT,
CONSTRAINT PK_SC PRIMARY KEY(Sno,Cno),
CONSTRAINT FK_SC_Sno FOREIGN KEY(Sno) REFERENCES Stu_Union (Sno) ON DELETE CASCADE,
CONSTRAINT FK_SC_Cno FOREIGN KEY(Cno) REFERENCES Course (Cno) ON DELETE CASCADE
);

INSERT INTO SC VALUES('S02','C01',2);
INSERT INTO SC VALUES ('S02','C02',2);
INSERT INTO SC VALUES ('S01','C01',2);
INSERT INTO SC VALUES ('S01','C02',2);
INSERT INTO SC VALUES ('S03','C03',2);
INSERT INTO SC VALUES ('S03','C04',3);
SELECT * FROM SC;
```

3.	违反参照完整性的插入数据
```sql
INSERT INTO SC VALUES(‘S99’,’C99’,2);
```
4.	在Stu_Union中删除数据，演示并分析级联删除。
```sql
DELETE FROM Stu_Union WHERE Sno='S01';
SELECT * FROM SC;
```
5.	在Course中删除数据，演示并分析级联删除。
```sql
DELETE FROM Course WHERE Cno='C02';
SELECT * FROM SC;
```
6.		为了演示多重级联删除，建立Stu_Card表，令Card_id为其主键，令Stu_id为外键，参考Stu_Union表，并插入数据。
```sql
CREATE TABLE Stu_Card(
Card_id CHAR(14),
Sno CHAR(8),
Remained_money DECIMAL(10,2),
Constraint PK_Stu_Card PRIMARY KEY(Card_id),
Constraint FK_Stu_Card_Sno FOREIGN KEY(Sno) REFERENCES Stu_union(Sno) ON DELETE CASCADE);

INSERT INTO Stu_Card VALUES('05212567','S03',400.25);
INSERT INTO Stu_Card VALUES('05212222','S09',600.50);

SELECT * FROM Stu_card;
```
7.	为了演示多重级联删除，建立ICBC_Card表，令Bank_id为其主键，令Stu_card_id为外键，参考Stu_Card表，并插入数据。
```sql
CREATE TABLE ICBC_Card(
	Bank_id CHAR(20),
	Stu_card_id CHAR(14),
	Restored_money DECIMAL(10,2),
	constraint PK_ICBC_Card PRIMARY KEY(Bank_id),
    constraint FK_ICBC_Card_Stu_id FOREIGN KEY(Stu_card_id) REFERENCES Stu_card(card_id) ON DELETE CASCADE
);

INSERT INTO ICBC_Card VALUES('9558844022312','05212567',15000.1);
INSERT INTO ICBC_Card VALUES('9558844023645','05212222',50000.3);

SELECT * FROM ICBC_Card;
```
8.	通过删除Stu_union表中的一条记录，演示4个表的多重级联删除。：
```sql
DELETE FROM Stu_union WHERE Sno='S03';
SELECT * FROM Stu_card;
SELECT * FROM ICBC_Card;
SELECT * FROM Stu_union;
SELECT * FROM SC;
```
9.	用gsql客户端工具（系统管理员nbuuser登录数据库db_uni）演示事务中进行多重级联删除失败的处理。
* 为演示多种级联删除情况，首先输入数据
```sql
INSERT INTO Stu_Union VALUES('S03','黄浩','F',25,'EE');
INSERT INTO SC VALUES ('S03','C03',2);
INSERT INTO SC VALUES ('S03','C04',3);
INSERT INTO Stu_Card VALUES('05212567','S03',400.25);
INSERT INTO ICBC_Card VALUES('9558844022312','05212567',15000.1);
SELECT * FROM Stu_card;
SELECT * FROM ICBC_Card;
SELECT * FROM Stu_union;
SELECT * FROM SC;
```

* 修改ICBC_Card表的外键属性，使其变为On delete No action。
```sql
ALTER TABLE ICBC_Card
DROP CONSTRAINT FK_ICBC_Card_Stu_id;
ALTER TABLE ICBC_Card
ADD CONSTRAINT FK_ICBC_Card_Stu_id FOREIGN KEY (Stu_card_id)
REFERENCES Stu_card(Card_id) ON DELETE NO ACTION;
```
* 演示事务中通过删除Stu_union表中的一条记录，多重级联删除失败，整个事务回滚到事务的初始状态
```sql
DELETE FROM Stu_union WHERE Sno='S03';
SELECT * FROM Stu_card;
SELECT * FROM ICBC_Card;
SELECT * FROM Stu_union;
SELECT * FROM SC;
```
输入如下SQL语句：
```sql
START  TRANSACTION;
DELETE FROM Stu_Card WHERE Card_id='05212222';
SELECT * FROM Stu_card;
SELECT * FROM ICBC_card;
Commit;
```
输入如下SQL语句：
```sql
SELECT * FROM Stu_card;
SELECT * FROM ICBC_card;
```

10.	演示互参考问题及其解决方法。要建立教师授课和课程指定教师听课关系的两张表，规定一个教师可以授多门课，但是每个课程只能指定一个教师去听课，所以要为两张表建立相互之间的参照关系。
* 在新建SQL终端窗口中输入如下SQL语句：
```sql
CREATE TABLE Listen_course(
Tno CHAR(6),
Tname VARCHAR(20),
Cno CHAR(4),
CONSTRAINT PK_listen_course PRIMARY KEY(Tno),
CONSTRAINT FK_listen_course FOREIGN KEY(Cno)
REFERENCES Teach_course(Cno)
);
CREATE TABLE Teach_course(
Cno CHAR(4),
Cname VARCHAR(30),
Tno CHAR(6),
CONSTRAINT PK_Teach_course PRIMARY KEY(Cno),
CONSTRAINT FK_Teach_course FOREIGN KEY(Tno)
REFERENCES Listen_course(Tno)
);
```
* 在新建SQL终端窗口中输入如下SQL语句：
```sql
CREATE TABLE Listen_course(
Tno CHAR(6),
Tname VARCHAR(20),
Cno CHAR(4),
CONSTRAINT PK_listen_course PRIMARY KEY(Tno)
);
```
* 在新建SQL终端窗口中输入如下SQL语句：
```sql
CREATE TABLE Teach_course(
Cno CHAR(4),
Cname VARCHAR(30),
Tno CHAR(6),
CONSTRAINT PK_Teach_course PRIMARY KEY(Cno),
CONSTRAINT FK_Teach_course FOREIGN KEY(Tno)
REFERENCES Listen_course(Tno)
);

ALTER TABLE Listen_course 
ADD CONSTRAINT FK_listen_course FOREIGN KEY(Cno)
REFERENCES Teach_course(Cno);
```
## 8.4	实验要求
1. 实验之前请细细阅读实验总体要求与说明指导书
2. 在openGauss环境中，完成以上实验(1)-(10)步所有SQL数据定义操作，并完成实验报告。

大数据学习

导航

最近发表

友情链接