[MySQL learning notes] MySQL principle - column attribute integrity

1, Column attribute problem

auto_ The increment dimension must be a primary key

When creating a table, once you use auto_ For the increment description field, after a single piece of data corresponding to the primary key field is deleted, no data can be added to the corresponding content of this primary key field.

As shown in the following figure, if the row whose ID field is number 3 is deleted, the data with primary key id=3 cannot be inserted.

2, Primary Key function and enterprise purpose of Primary Key

After adding primary key to the determined primary key during creation, its value is not allowed to be NULL unless it is set to auto_increment

Nature: unique and unrepeatable

Advantages: ensure data integrity and speed up data query

Create a table without a primary key:

CREATE TABLE t(
    id int(20),
    name varchar(30)
    );

Add primary key:

ALTER TABLE t ADD PRIMARY KEY(id);

3, Delete, combine keys, select primary keys

Delete primary key:

ALTER TABLE t DROP PRIMARY KEY；

Key combination (not commonly used):

You can set two primary keys when creating a table

CREATE TABLE t(
    age int(20) PRIMARY KEY,
    name varchar(30) PRIMARY KEY
    );

Or change to two primary keys

ALTER TABLE t ADD PRIMARY KEY(age,name);

A single primary key is often used to distinguish. A unique index is selected as the primary key. A table cannot have multiple primary keys

4, Role of composite primary key

When there is only one primary key in the table, it is the only index;

When there are multiple primary keys in a table, it is called a composite primary key. The composite primary key union ensures the unique index

However, not all tables have unique identifiers. Therefore, multiple fields of the composite primary key are required to jointly identify uniqueness

The capacity expansion of the composite primary key is not good, and problems may occur

5, Function and addition of unique key

The content of non primary key fields is also unique

For example, the id primary key in the teacher table is unique, and the non primary key phone number is also unique

In this case, unique is used to identify its uniqueness

CREATE TABLE test9(
    id int PRIMARY KEY,
    phone varchar(20) UNIQUE
    );

Add unique key:

CREATE TABLE test10(
    id int(4),
    phone varchar(20)
    );

ALTER TABLE test10 ADD UNIQUE(phone);

6, Unique key extension

CREATE TABLE test11(
	id int,
	name varchar(20) UNIQUE, 
	phone varchar(20) UNIQUE
	);

Delete:

ALTER TABLE test11 DROP INDEX phone;

7, Difference between primary key and unique key

The fields defined by the primary key may be used in multiple tables

Fields defined by unique keys are only used in a single table

8, Code comments and sql comments

Code comments:

Use comment to comment when creating a table

CREATE TABLE test12(
	id int,
	name varchar(20) COMMENT 'full name'
	);

Notes in sql:

CREATE TABLE test12(
	id int(20), #this is primary key!
	name varchar(20) UNIQUE, -- this is name! --
	/*
	this is other way.
	*/

9, Database integrity

A table should have a unique primary key to constrain it
The appropriate data type should be selected
Consider whether the specified field can be empty
Some fields must be marked with default (for example, the absence of students from the exam is marked with default)

10, Introducing foreign keys

There is a common field in both tables

Constraints using foreign keys

11, Foreign key

Create a master table:

CREATE TABLE stu(
	stuId INT(4) PRIMARY KEY,
	name VARCHAR(20)
	);

Create a slave table:

Add foreign keys when creating:

CREATE TABLE eatery(
	id INT PRIMARY KEY,
	money DECIMAL(10,4),
	stuId int(4),
	FOREIGN KEY (stuId) REFERENCES stu(stuId)
	);

Add foreign keys later:

CREATE TABLE eatery_2(
	id INT PRIMARY KEY,
	money DECIMAL(10,4),
	stuId int(4)
	);

ALTER TABLE eatery_2 ADD FOREIGN KEY (stuId) REFERENCES stu(stuId);

Foreign keys are prohibited in concurrent projects

12, Structural design

The structure of the table is designed when it is created

Try to avoid changing the structure of relational database in the later stage

13, Correct the error and delete the foreign key

SHOW CREATE TABLE eatery;

To delete a foreign key:

ALTER TABLE entery DROP FOREIGN KEY eatery_ibfk_1;

View again:

SHOW CREATE TABLE eatery;

View table structure:

DESC eatery;

MUL here does not mean foreign key, but can be repeated

14, Introduction to the usage scenarios of three foreign key operations: strict, empty and cascade

Strict operation is the operation mentioned above

Empty operation, an entry in the main table is deleted, and the value of the bound foreign key is changed to NULL

In cascade operation, an entry in the main table is deleted, and all its bound foreign keys are deleted

Empty is generally reserved for the outside world to delete data

Cascading is generally reserved for the outside world to update data

15, Empty and cascade operation

Create a master table:

CREATE TABLE stu1(
	stuId INT(4) PRIMARY KEY,
	name VARCHAR(20)
	);

Create a slave table to achieve cascade and empty operations:

CREATE TABLE eatery1(
	id INT(20) PRIMARY KEY,
	money DECIMAL(10,4),
	stuId INT(4),
	FOREIGN KEY(stuId) REFERENCES stu(stuId) ON DELETE SET NULL ON UPDATE CASCADE
	);

SHOW CREATE TABLE eatery1;

Insert data in main table:

INSERT INTO stu1 VALUES(1,'Fleming'),(2,'Jerry');

View data:

SELECT stuId,name FROM stu1;

Insert data from table:

INSERT INTO eatery1 VALUES(1,20.5,2),(2,78.6,1),(3,99.9,2),(4,748.4,1),(5,748.4,2);

View data:

SELECT * FROM eatery1;

Try to change the data in the main table:

UPDATE stu1 SET stuId='4' WHERE name='Fleming';

To view data from a table:

SELECT * FROM eatery1;

You can see that the contents in the slave table have also changed, which is the impact of cascading operations.

Attempt to delete data from the main table:

DELETE FROM stu1 WHERE stuId='2';