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

1.实验目的

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

   2.实验内容和要求

定义实体完整性，删除实体完整性。能够写出两种方式定义实体完整性的 SQL 语句：创建表时、创建表后定义实体完整性。设计 SQL 语句验证完整性约束是否起作用。

定义参照完整性，定义参照完整性的违约处理，删除参照完整性。写出两种方式定义参照完整性的 SQL 语句：创建表时、创建表后定义参照完整性。

针对具体应用语义，选择 NULL/NOT NULL/DEFAULT/UNIQUE/CHECK 等，定义属性上的约束条件。

   3.实验步骤

（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),
	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),
	PRIMARY KEY(suppkey),	#表级完整性
	FOREIGN KEY(nationkey) REFERENCES nation(nationkey)
);

结果截图;

使用命令查看表

DESC 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)
);
ALTER TABLE supplier3 ADD CONSTRAINT pk_supplier3 PRIMARY KEY (suppkey);
ALTER TABLE supplier3 ADD CONSTRAINT fk_supplier3 FOREIGN KEY (nationkey) REFERENCES nation (nationkey);

结果截图：

 使用命令查看表

DESC Supplier3;

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

源代码：

CREATE TABLE partsupp1(
	partkey INT,
	suppkey INT,
	availqty INT,
	supplycost NUMERIC(10,2),
	comment VARCHAR(200),
	PRIMARY KEY(partkey,suppkey),	#表级完整性
	FOREIGN KEY(partkey) REFERENCES part(partkey),
	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),
	FOREIGN KEY(regionkey) REFERENCES region(regionkey)
);

结果截图：

使用命令查看表

DESC nation1;

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

源代码：

INSERT INTO nation1 (
	nationkey,
	NAME,
	regionkey,
	COMMENT
)
VALUES(
	1,
	'阿富汗',
	1,
	'Afghanistan'
);
INSERT INTO nation1 (
	nationkey,
	NAME,
	regionkey,
	COMMENT
)
VALUES(
	1,
	'阿富汗',
	1,
	'Afghanistan'
);

结果截图：

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

源代码：

ALTER TABLE nation1 DROP PRIMARY KEY;

结果截图：

 使用命令查看表

DESC 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 region(regionkey)
);
CREATE TABLE nation3(
	nationkey INT,
	name CHAR(25),
	regionkey INT,
	comment VARCHAR(150),
	PRIMARY KEY(nationkey),
	FOREIGN KEY(regionkey) REFERENCES region(regionkey)
);

 结果截图：

使用命令查看表

DESC region1;
DESC nation2;
DESC nation3;

 （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),
	linestatus CHAR(1),
	shipdate DATE,
	commitdate DATE,
	receiptdate DATE,
	shipinstruct CHAR(25),
	shipmode CHAR(10),
	comment VARCHAR(40),
	PRIMARY KEY(orderkey,linenumber),
	FOREIGN KEY(partkey,suppkey) REFERENCES partsupp(partkey,suppkey)
);

结果截图：

使用命令查看表

DESC lineitem1;

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

源代码：

ALTER TABLE nation3
	DROP FOREIGN KEY `nation3_ibfk_1`;

结果截图：

使用命令查看表

DESC nation3;

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

源代码：

INSERT INTO nation3
VALUES(
	1,
	'阿富汗',
	3,
	'Afghanistan'
);

现在对regionkey在region内的regionkey=1和在region外的regionkey=3都能够执行插入，可以证明外键已经被删除。

结果截图：

（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 NUMERIC(8,2),
	discount NUMERIC(3,2),
	tax NUMERIC(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(partkey,suppkey) REFERENCES partsupp(partkey,suppkey),
	CHECK(shipdate < receiptdate),
	CHECK(returnflag IN('A','R','N'))
);

结果截图：

使用命令查看表

DESC lineitem2;

修改 Lineitem2 的一条记录，验证是否违反 CHECK 约束。

源代码：

INSERT INTO lineitem2 (
	orderkey,
	linenumber,
	returnflag,
	shipdate,
	receiptdate
)
VALUES
	(
		115,
		240,
		'A',
		'2021-11-12',
		'2021-11-15'
	);
UPDATE lineitem2
SET returnflag='B'
WHERE orderkey=115;
UPDATE lineitem2
SET shipdate='2021-11-20'
WHERE orderkey=115;

结果截图：

根据下面的截图可以看出，CHECK发挥了作用，更新均失败