- Database constraints
- Constraint type:
- NULL constraint
- UNIQUE: unique constraint
- DEFAULT: Default value constraint
- PRIMARY KEY: Primary key constraint
- Foreign key constraint
- CHECK constraints
- Various operations of the two tables
- NOT NULL-Indicates that a column cannot store NULL values.
- UNIQUE-to ensure that each row of a column must have a unique value.
- DEFAULT-Specifies the default value when no value is assigned to the column.
- PRIMARY KEY-a combination of NOT NULL and UNIQUE. Ensure that a column (or a combination of two columns and multiple columns) has a unique identifier, which helps to find a specific record in the table more easily and quickly.
- FOREIGN KEY-The referential integrity that guarantees that the data in one table matches the value in another table.
- CHECK-to ensure that the values in the column meet the specified conditions. For MySQL database, the CHECK clause is analyzed, but the CHECK clause is ignored.
When creating a table, you can specify that a column is not empty
drop table if exists student; create table student( id int ， sn int, name varchar(20) not null, qq_mail varchar(20) );
UNIQUE: unique constraint
Specifies that the sn column is unique and not repeated
drop table if exists student; create table student( id int not null; sn int unique, name varchar(20), qq_mail varchar(20) );
DEFAULT: Default value constraint
When you specify to insert data, the name column is empty, and the default value is unkown
drop table if exists student; create table student( id int not null; sn int unique, name varchar(20) default 'unkown', qq_mail varchar(20) );
PRIMARY KEY: Primary key constraint
Specify the id column as the primary key
drop table if exists student; create table student( id int not null primary key; sn int unique, name varchar(20), qq_mail varchar(20) );
For the primary key of integer type, it is often used with self-increasing auto_increment. When the corresponding field of the inserted data does not give a value, the maximum value is used.
Primary key constraint: It is a combination of NOT NULL and UNIQUE. In other words, when a field is modified by PRIMARY KEY, then this field cannot be empty and is unique! ! ! Because it is unique, the general match: auto_increment;
id int primary key auto_increment,
After the table is created, there is no data in the table. When the insert is executed for the first time, the current primary key, that is, ID, will automatically start from 1.
When I delete the newly inserted data and insert it again, it will start adding 1 to the original basis, that is, the ID of the last inserted statement;
only when you delete the entire table, then when When you insert it again, it starts from 1! ! !
alter can modify the type of field
Foreign key constraint
The foreign key is used to associate the primary key or unique key of other tables, the syntax:
foreign key(字段名)references 主表（列）
Example: create a class table classes, id is the primary key to
create a class table, when MySQL keywords are used as fields, you need to use `` to identify
drop table if exists classes; create table classes( id int primary key auto_increment, name varchar(20), `desc` varchar(200) );
Create student table student, one student corresponds to one class, and one class corresponds to multiple students. Use id as the primary key,
classes_id as the foreign key, associated with the class table id
drop table if exists student; create table student( id int primary key auto_increment, sn int unique, name varchar(20) default 'unkown', qq_mail int, classes_id int, foreign key(classes_id) references classes(id) );
drop table if exists test_user; create table test_user ( id int, name varchar(20), sex varchar(1), check (sex ='男' or sex='女') );
When inserting data in the future, you can only insert sex as male or female.
Various operations of the two tables
Now we have 2 tables, a class table and a student table.
Build a table: first create the main table,
insert data: insert the main table first, delete
data : which table data is deleted first? ? ? Delete the child table first. However, it is also possible to delete the main table, provided that the id in the main table is not associated. If it is connected, it will fail