Database constraints

Article Directory

Database constraints

Constraint type:

  • 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.

NULL constraint

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)
);
Insert picture description here

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.

Insert picture description here


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;

Insert picture description here


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

CHECK constraints

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