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

1.实验目的

掌握实体完整性、参照完整性和自定义完整性的定义和维护方法。

2.实验内容和要求

定义实体完整性，删除实体完整性。能够写出两种方式定义实体完整性的 SQL 语句：创建表时、创建表后定义实体完整性。设计 SQL
语句验证完整性约束是否起作用。 定义参照完整性，定义参照完整性的违约处理，删除参照完整性。写出两种方式定义参照完整性的 SQL
语句：创建表时、创建表后定义参照完整性。 针对具体应用语义，选择 NULL/NOT NULL/DEFAULT/UNIQUE/CHECK
等，定义属性上的约束条件。

3.实验步骤和实验结果

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

CREATE TABLE supplier1 (
	suppkey INT PRIMARY KEY,#列级完整性
	NAME CHAR ( 100 ),
	address VARCHAR ( 100 ),
	nationkey INT,
	phone CHAR ( 30 ),
	acctbal DECIMAL ( 12, 2 ),
	COMMENT VARCHAR ( 100 ),
CONSTRAINT nationkey——supplier1 FOREIGN KEY ( nationkey ) REFERENCES nation ( nationkey ) 
);

查看supplier1:
DESC supplier1;

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

 CREATE TABLE supplier2 (
	suppkey INT,
	NAME CHAR ( 100 ),
	address VARCHAR ( 100 ),
	nationkey INT,
	phone CHAR ( 30 ),
	acctbal DECIMAL ( 12, 2 ),
	COMMENT VARCHAR ( 100 ),
	PRIMARY KEY ( suppkey ),#表级完整性
CONSTRAINT nationkey_supplier2 FOREIGN KEY ( nationkey ) REFERENCES nation ( nationkey ) 
);

查看supplier2:
DESC supplier2;

（3）参照实验一供应商的表结构，创建供应商表(Supplier3)，先创建表再定义实体完整性:
创建表supplier3:

CREATE TABLE supplier3 (
	suppkey INT,
	NAME CHAR ( 100 ),
	address CHAR ( 100 ),
	nationkey INT,
	phone CHAR ( 30 ),
	acctbal DECIMAL ( 12, 2 ),
	COMMENT VARCHAR ( 100 ) 
);
ALTER TABLE supplier3 ADD CONSTRAINT pk_suppkey PRIMARY KEY ( suppkey );
ALTER TABLE supplier3 ADD CONSTRAINT pk_nationkey FOREIGN KEY ( nationkey ) REFERENCES nation ( nationkey );

查看supplier3:
DESC supplier3;
未定义实体完整性之前：

定义实体完整性之后：

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

CREATE TABLE partsupp1(
  partkey INT,
	suppkey INT,
	avaiqty INT,
	supplycost DECIMAL(10,2),
	comment VARCHAR(200),
	PRIMARY KEY(partkey,suppkey),
	CONSTRAINT partkey_partsupp1 FOREIGN KEY(partkey) REFERENCES part(partkey),
	CONSTRAINT suppkey_partsupp1 FOREIGN KEY(suppkey) REFERENCES supplier(suppkey)
	);

查看partsupp1:
DESC partsupp1;

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

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

查看nation1:
DESC nation1;

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

第二次：

结果说明实体完整性起作用了。
（7）删除国家表（nation1）的主码：
删除主码：
ALTER TABLE nation1 DROP PRIMARY KEY;#对表nation1进行删除操作

查看nation1:

现在nation1不再有主码。
（8）参照实验一地区表和国家表的结构，先定义地区表（region1）的实体完整性，再定义国家表(nation2)的列级参照完整性和国家表(nation3)的表级参照完整性：
建表：

CREATE TABLE region1 ( regionkey INT PRIMARY KEY, NAME CHAR ( 25 ), COMMENT VARCHAR ( 150 ) );
CREATE TABLE nation2 (
	nationkey INT PRIMARY KEY,
	NAME CHAR ( 25 ),
	regionkey INT,
	COMMENT VARCHAR ( 150 ),
	FOREIGN KEY ( regionkey ) REFERENCES region1 ( regionkey ) #列级完整性
	
);
CREATE TABLE nation3 (
	nationkey INT,
	NAME CHAR ( 25 ),
	regionkey INT,
	COMMENT VARCHAR ( 25 ),
	PRIMARY KEY ( nationkey ),
	CONSTRAINT regionkey_nation3 FOREIGN KEY ( regionkey ) REFERENCES region1 ( regionkey ) #表级完整性

);

查看：

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

CREATE TABLE lineitem1 (
	orderkey INT,
	partkey INT,
	suppkey INT,
	linenumber INT,
	quantity INT,
	extendedprice DECIMAL ( 3, 2 ),
	tax DECIMAL ( 3, 2 ),
	returnflag CHAR ( 1 ),
	linestatus CHAR ( 1 ),
	shipdate DATE,
	commitdate DATE,
	receiptdate DATE,
	shipinstruct CHAR ( 25 ),
	shipmode CHAR ( 10 ),
	COMMENT VARCHAR ( 40 ),
	PRIMARY KEY ( orderkey, linenumber ),
	FOREIGN KEY ( orderkey ) REFERENCES orders ( orderkey ),
	FOREIGN KEY ( partkey ) REFERENCES part ( partkey ),
FOREIGN KEY ( suppkey ) REFERENCES supplier ( suppkey ) 
);

查看：
DESC lienitem1;

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

ALTER TABLE nation3 DROP FOREIGN KEY regionkey_nation3;

删除nation3的外码破坏了表级参照完整性，拒绝执行，因此无法删除。
（11）给国家表（nation3）插入一条记录，验证参照完整性是否起作用：

验证成功。
（12）定义国家表（nation4）的 regionkey 的缺省属性值为 0 值，表示其他地区：
创建表：

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

查看：
DESC nation4;

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

CREATE TABLE lineitem2 (
	orderkey INT,
	partkey INT,
	suppkey INT,
	linenumber INT,
	quantity INT,
	extendedprice DECIMAL ( 3, 2 ),
	tax DECIMAL ( 3, 2 ),
	returnflag CHAR ( 1 ),
	linestatus CHAR ( 1 ),
	shipdate DATE,
	commitdate DATE,
	receiptdate DATE,
	shipinstruct CHAR ( 25 ),
	shipmode CHAR ( 10 ),
	COMMENT VARCHAR ( 40 ),
	PRIMARY KEY ( orderkey, linenumber ),
	FOREIGN KEY ( orderkey ) REFERENCES orders ( orderkey ),
	FOREIGN KEY ( partkey ) REFERENCES part ( partkey ),
	FOREIGN KEY ( suppkey ) REFERENCES supplier ( suppkey ),
	CHECK ( shipdate < receiptdate ),
	CHECK (
	returnflag IN ( 'A', 'R', 'N' )) 
);

查看：

（14）参照实验一订单明细表的结构，为订单明细表（Lineitem2）中某些属性（零件编号 partkey，和供应商编号 suppkey）增加非空约束：

ALTER TABLE lineitem2 CHANGE COLUMN partkey NOT NULL;
ALTER TABLE lineitem2 CHANGE COLUMN suppkey NOT NULL;

修改外键可能会破坏参照完整性约束，因此会拒绝执行。
（15）修改 Lineitem2 的记录，验证第（13、14）题中定义的约束是否起作用：
插入一条记录：

验证：

4.实验总结

（1）可能破坏参照完整性的情况及违约处理：