数据库实验六：完整性语言实验

1、参照实验一供应商的表结构，创建供应商表(Supplier1)时定义实体完整性（列级实体完整性）。

CREATE TABLE Supplier1(
suppkey INT PRIMARY KEY,
name CHAR(100),
address VARCHAR(100),
nationkey INT,
phone CHAR(30),
acctbal NUMERIC(12,2),
comment VARCHAR(100),
CONSTRAINT fk_nationkey_supplier1 FOREIGN KEY (nationkey) REFERENCES nation(nationkey)
);

DESC Supplier1;

2、参照实验一供应商的表结构，创建供应商表(Supplier2)时定义实体完整性（表级实体完整性）。

CREATE TABLE Supplier2(
suppkey INT,
name CHAR(100),
address VARCHAR(100),
nationkey INT,
phone CHAR(30),
acctbal NUMERIC(12,2),
comment VARCHAR(100),
CONSTRAINT fk_nationkey_supplier2 FOREIGN KEY (nationkey) REFERENCES nation(nationkey),
PRIMARY KEY(suppkey)
);

DESCRIBE Supplier2;

3、参照实验一供应商的表结构，创建供应商表(Supplier3)，在创建表后再定义实体完整性。

CREATE TABLE Supplier3(
suppkey INT,
name CHAR(100),
address VARCHAR(100),
nationkey INT,
phone CHAR(30),
acctbal NUMERIC(12,2),
comment VARCHAR(100),
CONSTRAINT fk_nationkey_supplier3 FOREIGN KEY (nationkey) REFERENCES nation(nationkey)
);
DESC Supplier3;
ALTER TABLE Supplier3 CHANGE suppkey suppkey INT PRIMARY KEY;
DESC Supplier3;

4、参照实验一供应关系表的结构，定义供应关系表（PartSupp1）的实体完整性。

CREATE TABLE partsupp1(
partkey INT,
suppkey INT,
avialqty INT,
supplycost NUMERIC(10,2),
comment VARCHAR(200),
CONSTRAINT primarykey_partsupp1 PRIMARY KEY (partkey,suppkey),
CONSTRAINT fk_partkey_partsupp1 FOREIGN KEY (partkey) REFERENCES part(partkey),
CONSTRAINT fk_suppkey_partsupp1 FOREIGN KEY (suppkey) REFERENCES supplier(suppkey) 
);
DESC partsupp1;

5、参照实验一国家表的结构，定义国家表（nation1）的实体完整性，其中nationkey和name 都是候选码，选择nationkey 作主码，name 上定义唯一性约束。

CREATE TABLE nation1(
nationkey INT PRIMARY KEY,
name CHAR(25) UNIQUE,
regionkey INT,
comment VARCHAR(150),
CONSTRAINT fk_regionkey_nation1 FOREIGN KEY (regionkey) REFERENCES region(regionkey)
);
DESC nation1;

6、给国家表（nation1）增加两条相同记录，验证实体完整性是否起作用。

INSERT INTO nation1 VALUES(40,'中国',1,'');
SELECT * FROM nation1;
INSERT INTO nation1 VALUES(40,'中国',1,'');
SELECT * FROM nation1;

7、删除国家表（nation1）的主码。

ALTER TABLE nation1 DROP PRIMARY KEY;
DESC nation1;

撤销主键，但是依然是NOT NULL

8、参照实验一地区表和国家表的结构，先定义地区表（region1）的实体完整性，再定义国家表(nation2)的列级参照完整性和国家表(nation3)的表级参照完整性。

列级参照完整性没弄懂是个啥…感觉自己写的两个都是表级参照完整性

CREATE TABLE region1(
regionkey INT PRIMARY KEY,
name CHAR(25),
comment VARCHAR (150)
);
DESC region1;

CREATE TABLE nation2(
nationkey INT PRIMARY KEY,
name CHAR(25),
regionkey INT,
FOREIGN KEY (regionkey) REFERENCES region(regionkey),
comment VARCHAR(150)
);
DESC nation2;
SHOW CREATE TABLE nation2 \G;
#这里能看见如果：没有给外键命名，系统会给一个名字，而且和constrain的办法一样

#
CREATE TABLE nation5(
nationkey INT PRIMARY KEY,
name CHAR(25),
regionkey INT  REFERENCES region(regionkey),#并没有真的创建外键
comment VARCHAR(150)
);
DESC nation2;
#

CREATE TABLE nation3(
nationkey INT PRIMARY KEY,
name CHAR(25),
regionkey INT,
comment VARCHAR(150),
CONSTRAINT fk_regionkey_nation3 FOREIGN KEY (regionkey) REFERENCES region(regionkey)
);
DESC nation3;
SHOW CREATE TABLE nation3 \G;

9、参照实验一订单明细表的结构，定义订单明细表（Lineitem1）的参照完整性。

CREATE TABLE lineitem1(
orderkey INT,
partkey INT,
suppkey INT,
linenumber INT,
quantity INT,
extendedprice NUMERIC(8,2),
discount NUMERIC(3,2),
tax NUMERIC(3,2),
returnflag CHAR(1),
linestatues CHAR(1),
shipdate date,
commitdate date,
receiptdate date,
shipinstruct CHAR(25),
shipmode CHAR(10),
comment VARCHAR(40),
CONSTRAINT PRIMARY KEY (orderkey,linenumber),
CONSTRAINT FOREIGN KEY (partkey) REFERENCES part(partkey),
CONSTRAINT FOREIGN KEY (suppkey) REFERENCES supplier(suppkey),
CONSTRAINT FOREIGN KEY (suppkey,partkey) REFERENCES partsupp(suppkey,partkey)
);
DESC lineitem1;

10、删除国家表（nation3）的外码。

ALTER TABLE nation3 DROP FOREIGN KEY fk_regionkey_nation3;
DESC nation3;
#!!!!!看上去跟没删主码一样？？？？？大无语!!!!!!!
SHOW CREATE TABLE nation3 \G;
SHOW COLUMNS FROM nation3;

11、给国家表（nation3）插入一条记录，验证参照完整性是否起作用。

INSERT INTO nation3 VALUES(99999,'河北工业大学',1,'');
INSERT INTO nation3 VALUES(99999999,'河北工业大学智能学院',90,'');

前面已经执行过删除外键的操作，所以现在对regionkey在region内的regionkey=1和在region外的regionkey=90都能够执行插入

那么如果我再把外键加回来呢，一般推论应该是：不能插入了

 ALTER TABLE nation3 ADD CONSTRAINT fk_new  FOREIGN KEY (regionkey) REFERENCES region(regionkey);
 #ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`#sql-14b0_8`, CONSTRAINT `fk_new` FOREIGN KEY (`regionkey`) REFERENCES `region` (`regionkey`))

报错，然后查这个error，发现是因为nation3表已经有数值了而且和要参照的表的内个参照字段的值不相符（90），没法再给它设置外键，所以先删除这两条数据

delete from nation3 where regionkey=1 or regionkey=90;
select * from nation3;
ALTER TABLE nation3 ADD CONSTRAINT fk_new  FOREIGN KEY (regionkey) REFERENCES region(regionkey);
INSERT INTO nation3 VALUES(99999999,'河北工业大学智能学院',90,'');
INSERT INTO nation3 VALUES(99999,'河北工业大学',1,'');

这时，成功新建外键，插入90的时候在外键的作用下，拒绝执行

而插入1，则正常插入，因为1在region表内，被nation3参照

12、定义国家表（nation4）的regionkey 的缺省属性值为0 值，表示其他地区。

CREATE TABLE nation4(
nationkey INT PRIMARY KEY,
name CHAR(25),
regionkey INT DEFAULT 0,
FOREIGN KEY (regionkey) REFERENCES region(regionkey),
comment VARCHAR(150)
);
DESC nation4;

13、参照实验一订单明细表的结构，使用CHECK 定义订单明细表（Lineitem2）中某些属性应该满足的约束。如：装运日期< 签收日期，退货标记为A,R 或N 中某一个。修改Lineitem2 的一条记录，验证是否违反CHECK 约束。

CREATE TABLE lineitem2(
orderkey INT,
partkey INT,
suppkey INT,
linenumber INT,
quantity INT,
extendedprice NUMERIC(8,2),
discount NUMERIC(3,2),
tax NUMERIC(3,2),
returnflag CHAR(1),
linestatues CHAR(1),
shipdate date,
commitdate date,
receiptdate date,
shipinstruct CHAR(25),
shipmode CHAR(10),
comment VARCHAR(40),
CONSTRAINT PRIMARY KEY (orderkey,linenumber),
CONSTRAINT FOREIGN KEY (partkey) REFERENCES part(partkey),
CONSTRAINT FOREIGN KEY (suppkey) REFERENCES supplier(suppkey),
CONSTRAINT FOREIGN KEY (suppkey,partkey) REFERENCES partsupp(suppkey,partkey),
CHECK (shipdate<receiptdate),
CHECK (returnflag IN ('A','R','N'))
);
DESC lineitem2;
INSERT INTO lineitem2 (orderkey,linenumber,returnflag,shipdate,receiptdate) VALUES(15,20,'A','2021-12-15','2021-12-25');
SELECT * FROM lineitem2;
UPDATE lineitem2 SET returnflag='H' WHERE orderkey=15;
UPDATE lineitem2 SET shipdate='2021-12-30' WHERE orderkey=15;

满足check

看到的lineitem2_chk_2和lineitem2_chk_1的来源：

SHOW CREATE TABLE lineitem2 \G;

是给check语句分配的默认的名字