1. Experimental purpose

Master the definition and maintenance methods of entity integrity, reference integrity and user-defined integrity.

    2. Experimental contents and requirements

Define entity integrity and delete entity integrity. Be able to write two ways to define entity integrity   SQL   Statement: define entity integrity when and after creating a table. Design   SQL   Statement to verify that the integrity constraint works.

Define reference integrity, define default processing of reference integrity, and delete reference integrity. Write two ways to define referential integrity   SQL   Statement: define referential integrity when and after creating a table.

Select NULL/NOT for specific application semantics   NULL/DEFAULT/UNIQUE/CHECK   Define constraints on attributes.

    3. Experimental steps

(1) Referring to the table structure of the supplier in Experiment 1, define entity integrity (column level entity integrity) when creating the supplier table (Supplier1).

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

Result screenshot:

Viewing tables using commands

DESC Supplier1;

  (2) Referring to the table structure of the supplier in Experiment 1, define the entity integrity (table level entity integrity) when creating the supplier table (Supplier2).

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

Screenshot of results;

Viewing tables using commands

DESC Supplier2;

  (3) Referring to the supplier table structure of Experiment 1, create a supplier table (Supplier3), and then 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);

Result screenshot:

  Viewing tables using commands

DESC Supplier3;

  (4) Refer to the structure of the supply relationship table in Experiment 1 to define the entity integrity of the supply relationship table (partsuppl1).

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

Result screenshot:

Viewing tables using commands

DESC partsupp1

  (5) Referring to the structure of the country table in Experiment 1, define the entity integrity of the country table (nation1), where nationkey and name   All are candidate codes. Select nationkey   As primary code, name   A uniqueness constraint is defined on the.

Source code:

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

Result screenshot:

Viewing tables using commands

DESC nation1;

  (6) Add two identical records to the country table (nation1) to verify whether 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'
);

Result screenshot:

 

(7) Delete the primary code of the country table (nation1).

Source code:

ALTER TABLE nation1 DROP PRIMARY KEY;

Result screenshot:

  Viewing tables using commands

DESC nation1;

(8) Referring to the structure of region table and country table in Experiment 1, the entity integrity of region table (region1) is defined first, and then the column level reference integrity of country table (nation2) and the table level reference integrity of country table (nation3) are defined.

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

  Result screenshot:

Viewing tables using commands

DESC region1;
DESC nation2;
DESC nation3;

 

 

  (9) Refer to the structure of order details in Experiment 1 to define the reference integrity of order details (Lineitem1).

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

Result screenshot:

Viewing tables using commands

DESC lineitem1;

  (10) Delete the foreign code of the country table (nation3).

Source code:

ALTER TABLE nation3
	DROP FOREIGN KEY `nation3_ibfk_1`;

Result screenshot:

Viewing tables using commands

DESC nation3;

 

(11) Insert a record into the country table (nation3) to verify whether referential integrity works.

Source code:

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

Now you can insert regionkey=1 in the region and regionkey=3 outside the region, which can prove that the foreign key has been deleted.

Result screenshot:

(12) To define the country table (nation4)   regionkey   The default attribute value for is   0   Value indicating 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)
);

  Result screenshot:

Viewing tables using commands

DESC nation4;

(13) Refer to the structure of order details in Experiment 1 and use CHECK   Define the constraints that some attributes in the order details (Lineitem2) should meet. For example, if the shipment date is less than the signing date, the return mark is 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'))
);

Result screenshot:

Viewing tables using commands

 

DESC lineitem2;

Modify a record of Lineitem2 to verify whether the CHECK constraint is violated.

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;

Result screenshot:

As can be seen from the screenshot below, CHECK worked and the updates failed