MySQL field constraints

Field constraints

Purpose: To ensure the validity, integrity and correctness of the data.
Classification:

• not null: not null constraint that this column is not null must be provided
• unique: the unique constraint that the value of this column cannot be repeated
• primary key: The primary key constraint uniquely marks a piece of data
• foreign key: foreign key constraints establish relationships between different tables

---

Not Null Constraint & Unique Constraint

not null: not null constraint that this column is not null must provide fields such as the following with not null or with unique:

 CREATE TABLE students(
    -> stu_num char(8) not null unique,
    -> stu_name varchar(20) not null,
    -> stu_age int not null,
    -> stu_tel char(11) not null unique,
    -> stu_qq varchar(11) unique
    -> );

primary key constraint

A data table can only have one primary key, but a primary key can be composed of multiple columns (joint primary key). In addition, the primary key must be non-null and unique, so after setting the primary key, there is no need to add non-null and unique constraints to the field. ,for example:
Writing one:

 CREATE TABLE students(
    -> stu_num char(8) not null primary key,
    -> stu_name varchar(20) not null,
    -> stu_age int not null,
    -> stu_tel char(11) not null unique,
    -> stu_qq varchar(11) unique
    -> );

Writing two:

 CREATE TABLE students(
    -> stu_num char(8) not null,
    -> stu_name varchar(20) not null,
    -> stu_age int not null,
    -> stu_tel char(11) not null unique,
    -> stu_qq varchar(11) unique,
    -> primary key(stu_num)
    -> );

Add a primary key constraint after creating the table <br> with modify

 ALTER table stus modify stu_num char(4) primary key

Primary key auto-increment

 CREATE tabletypes（
    type_id int primary key auto_increment,
    type_name varchar(20) not null,
）

Note: Auto-increment starts from 1, one record is added every day, and the counter is +1. After deleting a record, the counter will not be updated, so auto-increment only guarantees uniqueness, not continuity.

Joint primary key (infrequently used)

Combine multiple fields in a datatable

 CREATE table grades(
    stunum char(8),
    course_id int,
    score int,
    primary key(str_num, course_id
)

foreign key constraints

Expand the multi-table association later

Reference: bilibili