What are the MySQL database constraint types?

Constraints in the database, as the name suggests, limit the data inserted into the database. The purpose of doing this is to ensure the validity and integrity of the data. This greatly improves the quality of data in the database, saves database space and time for calling data.

NOT NULL constraint is a column-level constraint that specifies that a column is not allowed to be null. It can only be defined using column-level constraint syntax.

The null value in SQL is not case-sensitive and has the following characteristics: values ​​of all data types can be null, including int, float, boolean, etc. The empty string is not equal to null, and 0 is not equal to null. If you want to set a non-null constraint for a specified column, just add "not null" after the column definition. For example, when creating a table:

create table t_test
(
   id int not null,
   name varchar(255) default 'xyz' not null,
   gender varchar(2) null
);

Add or delete non-null constraints when using alter table to modify the table, for example:

#增加非空约束alter table t_test modify gender varchar(2) not null;#取消非空约束alter table t_test modify gender varchar(2) null;#取消非空约束，并指定默认值alter table t_test modify name varchar(255) default 'abc' null;

2.UNIQUE: unique constraint, specify a column or Several column combinations cannot be repeated.

In the database, although duplicate values ​​are not allowed in unique constraint columns, multiple null values ​​can appear because null is not equal to null. Multiple unique constraints can be created in the same table, and unique constraints can also be composed of multiple columns. When a unique constraint is created for a column, MySQL will create a unique index for the column. If the unique constraint is not named, the unique constraint will default to the column name. Unique constraints can be established using column-level syntax or table-level syntax. Only through table-level syntax can you set combined constraints or specify constraint names for multiple columns.

Use column-level syntax to create unique constraints, just add the unique keyword after the column definition:

#创建表时建立唯一约束，使用列级语法建立create table unique_test
(
   #建立非空约束，意味着id不能为null   id int not null,
   #建立唯一约束，意味着多行数据的该列值不能相等   name varchar(255) unique
);

The format of table-level syntax to create unique constraints: [constraint constraint name] unique ( Column name [, column name,...]), the above table-level constraint syntax format can be placed in the create table statement in parallel with the column definition, or can be placed in the alter table statement using the add keyword to add:

#创建表时，使用表级语法创建唯一约束create table uniques_test2
(
   #为id建立非空约束，意味着id不能为null   id int not null,
   name varchar(255),
   pass varchar(255),
   #使用表级语法为name建立唯一约束   unique (name),
   #使用表级语法为pass建立唯一约束，并指定约束名为test2_uk   constraint test2_uk unique (pass)
);#创建表时，使用表级语法建立组合列的唯一约束create table unique_test3
(
   id int not null,
   name varchar(255),
   pass varchar(255),
   #指定name和pass两列组合不能重复   constraint test3_uk unique (name, pass)
);#在修改表时，使用add关键字来增加唯一约束alter table unique_test3 add unique (id, name);#在修改表时，使用modify关键字，来为单列设置唯一约束alter table unique_test3 modify name varchar(100) unique;

Delete unique constraints in MySQL:

alter table tableName drop index 约束名；
   例句：#删除unique_test3表中的test3_uk唯一约束alter table unique_test3 drop index test3_uk;

3.PRIMARY KEY: Primary key constraint, specifying the value of this column can uniquely identify the record.

"Foreign key" is a constraint that specifies that the row record is associated with a record in the main table. The main purpose is to ensure referential integrity.

5.CHECK (not supported by MySQL): Check constraints, specify a Boolean expression, which is used to specify that the value of the corresponding column must satisfy the expression.

The above is the detailed content of What are the MySQL database constraint types?. For more information, please follow other related articles on the PHP Chinese website!