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

一、实验目的

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

二、实验内容和要求

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

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

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

三、实验步骤

(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);

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

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

INSERT INTO nation1 (

    nationkey,

    NAME,

    regionkey,

    COMMENT

)

VALUES(

    1,

    '巴勒斯坦',

    1,

    'Palestine'

);

第一条信息可以插入，但是当再次插入信息时报错：

结果说明实体完整性起作用了。

插入相同的信息肯定会由于实体完整性不可以插入，如果所插入的信息不完全相同但是若主键内容相同或者国家名字形同的话也是不可以插入的，所以实体完整性作用关键是对于主键的唯一性以及其他唯一性约束的属性。

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

ALTER TABLE nation1 DROP PRIMARY KEY;

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

);

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

ALTER TABLE nation3

    DROP FOREIGN KEY `nation3_ibfk_1`;

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

INSERT INTO nation3

VALUES(

    1,

    '阿富汗',

    3,

    'Afghanistan');

国家表（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)

);

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

CREATE TABLE lineitem2 (

    orderkey INT,

    partkey INT,

    suppkey INT,

    linenumber INT,

    quantity INT,

    extendedprice DECIMAL ( 8, 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）增加非空约束。

Partkey 和 suppkey都是lineitem2表的外键，修改外键可能会破坏参照完整性约束，因此会拒绝执行，无法增加非空约束。

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

由于lineitem2表还是个空表，所以我随便插入了一条信息

INSERT INTO lineitem2 (

    orderkey,

    linenumber,

    returnflag,

    shipdate,

    receiptdate

)

VALUES

    (

       210753,

       212,

       'A',

       '2023-11-01',

       '2023-11-13'

    );
UPDATE lineitem2

SET returnflag='B'

WHERE orderkey=0210753;

UPDATE lineitem2

SET shipdate='2023-11-30'


WHERE orderkey=0212;

这个错误信息表示在执行数据库操作时，违反了一个名为'lineitem2_chk_2'检查约束（Check Constraint。检查约束是用于确保数据满足特定条件的规则。通过检查约束，可以限制列中插入或更新的值必须满足一定的条件。当违反了这些条件时，数据库会报错并拒绝执行该操作。