Shulou(Shulou.com)05/31 Report--

This article mainly introduces the example analysis of MySQL constraint types, which is very detailed and has certain reference value. Friends who are interested must finish it!

Constraint

Constraints ensure the integrity and consistency of data

Constraints are divided into table-level constraints and column-level constraints

Constraint types include: NOT NULL (non-null constraint), PRIMARY KEY (primary key constraint), UNIQUE KEY (unique constraint), DEFAULT (default constraint), FOREIGN (foreign key constraint)

1. Primary key constraint

PRIMARY KEY

There can be only one primary key per data table

The primary key ensures the uniqueness of the record, and the value of the primary key is not duplicated.

The primary key is automatically NOT NULL

For example, create a student table, set the student number as the primary key, and view the table structure through SHWO COLUMNS FROM student after creating the table.

CREATE TABLE student (id int PRIMARY KEY,stu_name varchar (20))

two。 Unique constraint

UNIQUE KEY

Unique constraints can guarantee the uniqueness of records.

Fields with unique constraints can be null (NULL)

There can be multiple unique constraints per data table

For example, to create a teacher table, the id field is incremented and the tea_name is unique.

CREATE TABLE teacher (id int AUTO_INCREMENT PRIMARY KEY,tea_name varchar (20) NOT NULL UNIQUE KEY)

3. Default constraint

DEFAULT

When inserting a record, if the field is not explicitly assigned a value, the default value is automatically assigned

For example, create a course table with a default class time of 40 minutes

CREATE TABLE course (id int AUTO_INCREMENT PRIMARY KEY,cou_name varchar (20) NOT NULL UNIQUE KEY,time int DEFAULT 40); INSERT INTO course (cou_name) values ('language')

4. Non-empty constraint

NOT NULL

The force column cannot be a NULL value, and the constraint forces the field to always contain a value.

This means that you cannot insert a new record or update a record without adding a value to the field.

For example, create not null constraints in the Id column and the name column when the "Persons" table is created:

Create table Persons (id int not NULL,p_name varchar (20) not null, deparment varchar (20), address varchar (20), telNum varchar (20)) DESC Persons

5. Foreign key constraint

FOREIGN KEY

Maintain the consistency and integrity of the data

Realize an one-to-one or one-to-n relationship

1. Parent and child tables must use the same storage engine, and temporary tables are prohibited.

two。 The storage engine for data tables can only be InnoDB.

3. Foreign key columns and reference columns must have similar data types. The length of the number or whether there are signed bits must be the same; the length of the character can be different.

4. Foreign key columns and reference columns must be indexed. If no index exists for the foreign key column, MySQL automatically creates the index.

CREATE TABLE school (id int AUTO_INCREMENT PRIMARY KEY,sname varchar (20) NOT NULL); CREATE TABLE student2 (id int AUTO_INCREMENT PRIMARY KEY,sid int,FOREIGN KEY (sid) REFERENCES school (id))

Reference operations for foreign key constraints

CASCADE: deletes or updates from the parent table and automatically deletes or updates matching rows in the child table

The referenced column in the parent table deletes some data, and the row of the corresponding data is deleted in the child table

CREATE TABLE student3 (id int AUTO_INCREMENT PRIMARY KEY,sid int,FOREIGN KEY (sid) REFERENCES school (id) ON DELETE CASCADE)

SET NULL: delete or update rows from the parent table and set the foreign key column of the child table as NULL. If you use this item, you must ensure that the subtable column does not specify NOT NULL

Delete the data from the parent table and set the reference in the child table to NULL

RESTRICT: rejects delete or update operations on the parent table

NO ACTION: keyword for standard SQL, same as RESTRICT in MySQL

The above is all the content of the article "sample Analysis of MySQL constraint types". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.