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 nation3DROP 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发挥了作用，更新均失败

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！