1. The experiment purpose

Master physical integrity 、 Definition and maintenance methods of reference integrity and user-defined integrity .

   2. The contents and requirements of the experiment

Define entity integrity , Delete entity integrity . Be able to write two ways to define entity integrity  SQL  sentence ： Create table time 、 Define entity integrity after creating the table . Design  SQL  Statement to verify whether the integrity constraint works .

Defining referential integrity , Define default handling for reference integrity , Delete referential integrity . Write two ways to define referential integrity  SQL  sentence ： Create table time 、 Define referential integrity after creating a table .

Specific application semantics , choice NULL/NOT NULL/DEFAULT/UNIQUE/CHECK  etc. , Define constraints on attributes .

   3. The experimental steps

（1） Refer to the table structure of the supplier in Experiment 1 , Create supplier table (Supplier1) Define entity integrity when （ Column level entity integrity ）.

Source code ：

CREATE TABLE supplier1(
	suppkey INT PRIMARY KEY, # Column level integrity 
	name CHAR(100),
	address VARCHAR(100),
	nationkey INT,
	phone CHAR(30),
	acctbal NUMERIC(12,2),
	comment VARCHAR(100),
	FOREIGN KEY(nationkey) REFERENCES nation(nationkey)
);

Results screenshots ：

Use the command to view the table

DESC Supplier1;

 （2） Refer to the table structure of the supplier in Experiment 1 , Create supplier table (Supplier2) Define entity integrity when （ Table level entity integrity ）.

Source code ：

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),	# Table level integrity 
	FOREIGN KEY(nationkey) REFERENCES nation(nationkey)
);

Results screenshots ;

Use the command to view the table

DESC Supplier2;

 （3） Refer to the table structure of the supplier in Experiment 1 , Create supplier table (Supplier3), Define entity integrity after creating the table .

Source code ：

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

Results screenshots ：

  Use the command to view the table

DESC Supplier3;

 （4） Refer to the structure of the supply relationship table in Experiment 1 , Define supply relationship table （PartSupp1） Physical integrity of .

Source code ：

CREATE TABLE partsupp1(
	partkey INT,
	suppkey INT,
	availqty INT,
	supplycost NUMERIC(10,2),
	comment VARCHAR(200),
	PRIMARY KEY(partkey,suppkey),	# Table level integrity 
	FOREIGN KEY(partkey) REFERENCES part(partkey),
	FOREIGN KEY(suppkey) REFERENCES supplier(suppkey)
);

Results screenshots ：

Use the command to view the table

DESC partsupp1

 （5） Refer to the structure of the national table in Experiment 1 , Define country table （nation1） Physical integrity of , among nationkey and name  They're all candidate codes , choice nationkey  As master code ,name  Define uniqueness constraints on .

Source code ：

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

Results screenshots ：

Use the command to view the table

DESC nation1;

 （6） Give the country a table （nation1） Add two same records , Verify that entity integrity works .

Source code ：

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

Results screenshots ：

（7） Delete country table （nation1） The main code of .

Source code ：

ALTER TABLE nation1 DROP PRIMARY KEY;

Results screenshots ：

  Use the command to view the table

DESC nation1;

（8） Refer to the structure of region table and country table in Experiment 1 , First define the region table （region1） Physical integrity of , Redefine the country table (nation2) Column level reference integrity and country table (nation3) Table level referential integrity .

Source code ：

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

  Results screenshots ：

Use the command to view the table

DESC region1;
DESC nation2;
DESC nation3;

 （9） Refer to the structure of order details in Experiment 1 , Define order details （Lineitem1） Referential integrity of .

Source code ：

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

Results screenshots ：

Use the command to view the table

DESC lineitem1;

 （10） Delete country table （nation3） The outer code of .

Source code ：

ALTER TABLE nation3
	DROP FOREIGN KEY `nation3_ibfk_1`;

Results screenshots ：

Use the command to view the table

DESC nation3;

（11） Give the country a table （nation3） Insert a record , Verify that referential integrity works .

Source code ：

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

Right now regionkey stay region Internal regionkey=1 And in region External regionkey=3 Can perform insertion , It can be proved that the foreign key has been deleted .

Results screenshots ：

（12） Define country table （nation4） Of  regionkey  The default attribute value of is  0  value , Indicates other regions .

Source code ：

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

  Results screenshots ：

Use the command to view the table

DESC nation4;

（13） Refer to the structure of order details in Experiment 1 , Use CHECK  Define order details （Lineitem2） Constraints that some attributes in should satisfy . Such as ： Date of shipment < Date of receipt , The return is marked as A,R  or N  One of them .

Source code ：

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

Results screenshots ：

Use the command to view the table

DESC lineitem2;

modify Lineitem2 A record of , Verify if there is a violation of CHECK constraint .

Source code ：

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;

Results screenshots ：

According to the screenshot below, you can see ,CHECK It worked , All updates failed