Shulou(Shulou.com)06/01 Report--

Table integrity constraint: used to ensure the integrity of data

Common table constraints are: PRIMARY KEY (competitive primary key), FOREIGN KEY (FK foreign key), NOT NULL, UNIQUE KEY (unique key), AUTO_INCREMENT, DEFAULT (default)

One. DEFAULT, NOT NULL

Description: default: specify default value

Not null: empty is not allowed, just like when we register an account online, some content must be filled in and cannot be submitted if it is empty.

Example: create school database, create student table in school database, fields have student number id, name name, gender sex; set id, name is not allowed to be empty, sex is not allowed to be empty, and sex is set to male by default.

When you look at the structure of the student table after creating the table, you can see that the null is all no, and the default option sex field defaults to m

Now insert a piece of data, such as: insert into student value.

From the picture above, you can see that the data was successfully inserted. Insert a piece of data again: insert into student (id,name) value (1)

Only two fields are inserted, and there is no data insertion in the sex field, but because the field is not allowed to be empty and the default value is set to m, the default record is m

Two. Set unique constraint UNIQUE

Unique constraint: controls that the value of this column does not allow repetition and must be a unique value

Example: create the student2 table and set the id field to unique based on the student table

Create table student2 (id int unique, name varchar (50) not null, sex enum) default 'm'not null)

You can see that the id field key is UNI, and two records are inserted

As you can see, because unique is set in the id field, the id value of inserting both records is 1, so the insert Datagram error

Three. Set the primary key constraint PRIMARY KEY

The value of the primary key field is not allowed to be duplicated or empty, that is, UNIQUE + NOT NULL

The primary key has a single column primary key and a multi-column primary key (compound primary key), which means that two or more fields are set as the primary key.

Example: create the student3 table and set the field id as the primary key

Create table student3 (id int primary key, name varchar (50) not null, sex enum)

After the primary key is set, the id field cannot be inserted successfully until the value of the primary key is not empty and the same value cannot appear when the data is inserted.

(the compound primary key is the same, that is, multiple fields set primary key or not null unique)

Four. Set the foreign key constraint FOREIGN KEY

Foreign key function: set word table and parent table, synchronously update and delete parent and child tables

Example: create an employee (employees) table and a payroll (payroll) table in the test table, and set foreign keys in two tables

Employee table (parent table):

Payroll table (child table)

View the structure of these two tables:

After creating two tables, insert the records of tom and jack in the parent table employees

Insert a record of Lucy in the word table. You can see from the following figure that inserting data Lucy will report an error because a foreign key has been made and there is no Lucy information in the parent table.

Then insert tom into the payroll table, jack field: insert into payroll (id,pname,payroll) values

After inserting the value, change the tom in the parent table employees to tomer, and then look at the changes in the word table. The tom of the child table also becomes tomer.

Conclusion:

0. The function of the foreign key is to reference the value of a column in the parent table in the child table, so that the value of the foreign key in the child table depends on the parent table.

1. Only the primary key in the parent table can be used as the foreign key in the child table.

2. When the record of an employee in the parent table is modified, the child table is also modified synchronously.

3. When an employee's record is deleted from the parent table, the child table will also be deleted synchronously.

Five. Set the self-increment field value AUTO_INCREMENT

Example: create a zhi table in the test library and set the id to self-increment

Insert data after creating the table: insert into zhi (name,age) values ('tom',18), (' jack',19), ('lucy',20); # you can see that the inserted data has no id self-segment

By inserting the above fields, you can see that the id field automatically completes the data in the form of self-increment.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.