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

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 decimal(12,2),
  comment varchar(100),
  foreign key(nationkey) references nation(nationkey)
)

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

create table Supplier2
(
  suppkey int,
  name char(100),
  address varchar(100),
  nationkey int,
  phone char(30),
  acctbal decimal(12,2),
  comment varchar(100),
  foreign key(nationkey) references nation(nationkey),
  primary key(suppkey)
)

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

create table Supplier3
(
  suppkey int,
  name char(100),
  address varchar(100),
  nationkey int,
  phone char(30),
  acctbal decimal(12,2),
  comment varchar(100),
  foreign key(nationkey) references nation(nationkey)
);

alter table Supplier3 add constraint sup_primarykey primary key(suppkey);

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

create table PartSupp1
(
  partkey int,
  suppkey int,
  availqty int,
  supplycost decimal(10,2),
  comment varchar(200),
  primary key(partkey,suppkey),
  foreign key(partkey) references part(partkey),
  foreign key(suppkey) references supplier(suppkey)
)

（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)
)

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

insert into nation1 values(40,'中国',1,'');
insert into nation1 values(40,'中国',1,'');

  第一次插入：

  第二次插入不允许插入，证明实体完整性起了作用：

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

alter table nation1 drop primary key

（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 nation3_foreignkey foreign key(regionkey) references region1(regionkey)
);

 

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

create table Lineitem1
(
  orderkey int,
  partkey int,
  suppkey int,
  linenumber int,
  quantity int,
  extendedprice decimal(8,2),
  discount 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)
)

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

alter table nation3 drop foreign key nation3_foreignkey

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

insert into nation3 values(40,'中国',6666,'')

    可以插入，参照完整性约束已经删除，不起作用 

（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 region1(regionkey)
)

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

create table Lineitem2
(
  orderkey int,
  partkey int,
  suppkey int,
  linenumber int,
  quantity int,
  extendedprice decimal(8,2),
  discount 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 partkey int not null;

alter table Lineitem2 change column suppkey suppkey int not null;

 

    修改外键可能会破坏参照完整性约束，因此均会拒绝执行。

（15）修改 Lineitem2 的记录，验证第（13、14）题中定义的约束是否起作用。

    插入一条数据：

insert into Lineitem2 values(1,44930,18848,1,61,165371.00,0.09,0.18,'A','','2023-10-11','2023-10-12','2023-10-13','','','');

 

    符合约束条件可以插入 

    更新数据：

update Lineitem2 set returnflag = 'B' where orderkey = 1;

    由于returnflag的check约束无法更新 

update Lineitem2 set shipdate = '2023-10-14' where orderkey = 1;

    由于shipdate的check约束无法更新 

update Lineitem2 set partkey = null where orderkey = 1;

    由于14题中约束无法创建，所以没有notnull的约束，可以更新

update Lineitem2 set orderkey = 9999999 where suppkey = 18848;

    由于orderkey的外键约束，无法更新

update Lineitem2 set partkey = 9999999 where orderkey = 1;

    由于partkey的外键约束，无法更新

update Lineitem2 set suppkey = 9999999 where orderkey = 1;

    由于suppkey的外键约束，无法更新 

再次插入一条数据：

insert into Lineitem2 values(1,1000,1000,1,6,165371.00,0.09,0.18,'R','','2023-10-11','2023-10-12','2023-10-13','','','');

    这次由于主键约束，不允许主键重复，无法插入