Home > Database > Mysql Tutorial > How to add constraints in mysql?

How to add constraints in mysql?

青灯夜游
Release: 2020-09-08 16:13:06
Original
16106 people have browsed it

In the MYSQL database, you can perform some operations on various items of the table when creating a table, such as adding primary key constraints or non-null constraints; you can also add constraints and delete constraints after creating the table. The following article will give you a detailed understanding, I hope it will be helpful to you.

How to add constraints in mysql?

What are constraints?

Constraints are actually restrictions on the data in the table; the purpose is to ensure that the records in the table are complete and valid.

Commonly used constraints are:

1, non-null constraint (not null)

2, unique constraint (unique)

3, primary key constraint ( primary key)

4. Foreign key constraint(foreign key)

5. Check constraint (currently not supported by MySQL, supported by Oracle)

##mysql Methods to add and delete constraints:

1. Add constraints when creating the table

create table table_name(
列名1  数据类型 (int) primary key auto_increment,
列名2 数据类型  not null,
列名3 数据类型   unique,
列名4 数据类型  default '值',
constraint  索引名 foreign key(外键列)  references 主键表(主键列)
on delete cascade | on delete set null
)
Copy after login

2. Add constraints after the table creation is completed And delete constraints

1), non-null constraints

Add non-null constraints

alter  table table_name modify 列名 数据类型  not null
Copy after login

Delete non-null constraints

alter table table_name modify 列名 数据类型 null
Copy after login

2), unique Constraint

Add unique constraint

alter table table_name add unique 约束名(字段)
Copy after login

Delete unique constraint

alter table table_name drop key 约束名
Copy after login

3), primary key constraint


Add primary key constraint

alter table  table_name add primary key (字段)
Copy after login

Delete primary key constraints

alter table table_name drop primary key
Copy after login

4), foreign key constraints

Add foreign key constraints

alter table table_name add constraint 约束名 foreign key(外键列)
Copy after login

Delete foreign key constraints

alter table table_name drop foreign key 约束名
Copy after login

5), automatic growth Constraints

Add automatic growth constraints

alter table table_name  modify 列名 int  auto_increment
Copy after login

Delete automatic growth constraints

alter table table_name modify 列名 int
Copy after login

Recommended related video tutorials: "

MySQL Tutorial"

The above is the detailed content of How to add constraints in mysql?. For more information, please follow other related articles on the PHP Chinese website!

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template