Add, modify, and delete constraints in MySQL

Constraints in MySQL, adding constraints, deleting constraints, and some other adornments:
A. Not null (non-null constraint)

Add a non-null constraint

1) Add directly when building a table

CREATE TABLE T_user (user_id INT (Ten) not NULL);

2) through the ALTER statement

ALTER TABLE t_user MODIFY user_id INT (Ten) is not NULL;

ALTER TABLE t_user Change user_id user_id INT (Ten) not NULL;

Delete a non-empty constraint

1) ALTER TABLE t_user MODIFY user_id INT (10);

2) ALTER TABLE t_user change user_id user_id INT (10);

Two. Unique (single constraint)

Add a UNIQUE Constraint

1) Add directly when building a table

CREATE TABLE T_user (user_id INT (Ten) UNIQUE);

CREATE TABLE T_user (

user_id INT (10),

User_name VARCHAR (30),

CONSTRAINT un_phone_email UNIQUE (user_id,user_name) #复合约束

);

CREATE TABLE T_user (

user_id INT (10),

UNIQUE KEY (user_id)

);

2) through the ALTER statement

ALTER TABLE t_user MODIFY user_id INT (Ten) UNIQUE;

ALTER TABLE t_user Change user_id user_id INT (Ten) UNIQUE;

ALTER TABLE t_user ADD UNIQUE (user_id);

ALTER TABLE t_user ADD UNIQUE KEY (user_id);

ALTER TABLE t_user ADD CONSTRAINT un_id UNIQUE (user_id);

ALTER TABLE t_user ADD CONSTRAINT un_id UNIQUE KEY (user_id);

Remove Uniqueness Constraint

ALTER TABLE t_user DROP INDEX user_id;

Note: Unique but can be empty (empty and empty unequal)

Three. PRIMARY key (primary KEY constraint)

Add a PRIMARY KEY constraint

1) Add directly when building a table

CREATE TABLE T_user (user_id INT (Ten) PRIMARY KEY);

CREATE TABLE T_user (

user_id INT (10),

User_name VARCHAR (30),

CONSTRAINT pk_id_name PRIMARY KEY (user_id,user_name) #复合约束

);

CREATE TABLE T_user (

user_id INT (10),

PRIMARY KEY (user_id)

);

2) through the ALTER statement

ALTER TABLE t_user MODIFY user_id INT (Ten) PRIMARY KEY;

ALTER TABLE t_user Change user_id user_id INT (Ten) PRIMARY KEY;

ALTER TABLE t_user ADD PRIMARY KEY (user_id);

ALTER TABLE t_user ADD CONSTRAINT pk_id PRIMARY KEY (user_id);

Delete a PRIMARY KEY constraint

1) ALTER TABLE t_user DROP PRIMARY KEY;

NOTE: PRIMARY KEY constraint equals (UNIQUE constraint + non-null constraint)

There is a maximum of one primary KEY constraint in a table, and if multiple primary keys are set, the following prompt appears:

Multiple PRIMARY KEY defined!!!

Before deleting a primary key constraint, if there is a self-growth need to remove the self-growth, you cannot delete the primary KEY constraint without deleting self-growth

Four. FOREIGN key (FOREIGN KEY constraint, the corresponding field can only be a primary KEY or a unique constraint decorated field)

First create two tables: class,students

Main Table:

CREATE TABLE Class (

cla_id INT (6) auto_increment PRIMARY KEY,

Cla_name VARCHAR (+) not NULL UNIQUE

);

From the table:

CREATE TABLE Students (

stu_id INT (Ten) auto_increment PRIMARY KEY,

Stu_name VARCHAR (+) not NULL,

Stu_score FLOAT (5,2) DEFAULT 0.0,

cla_id INT (10),

CONSTRAINT fk_cla_id FOREIGN KEY (cla_id) REFERENCES class (cla_id) #添加外键约束

);

You can also add this:

ALTER TABLE students ADD CONSTRAINT fk_cla_id froeign KEY (cla_id) REFERENCES class (cla_id);

Delete a FOREIGN KEY constraint

ALTER TABLE students DROP FOREIGN KEY fk_cla_id;

#外键中的级联关系有以下几种情况:

#ON Delete CASCADE Delete data from the primary table, the data from the table is deleted

#ON Update CASCADE updates the data in the primary table, the data from the table is updated with it

#ON Delete SET null to delete data from the primary table, the data from the table is empty

#默认 Delete data from a table before deleting data from the primary table, otherwise the primary table data is not deleted

CREATE TABLE Students (

stu_id INT (Ten) auto_increment PRIMARY KEY,

Stu_name VARCHAR (+) not NULL,

Stu_score FLOAT (5,2) DEFAULT 0.0,

cla_id INT (10),

CONSTRAINT fk_cla_id FOREIGN KEY (cla_id) REFERENCES class (cla_id) on DELETE CASCADE

);

CREATE TABLE Students (

stu_id INT (Ten) auto_increment PRIMARY KEY,

Stu_name VARCHAR (+) not NULL,

Stu_score FLOAT (5,2) DEFAULT 0.0,

cla_id INT (10),

CONSTRAINT fk_cla_id FOREIGN KEY (cla_id) REFERENCES class (cla_id) on UPDATE CASCADE

);

CREATE TABLE Students (

stu_id INT (Ten) auto_increment PRIMARY KEY,

Stu_name VARCHAR (+) not NULL,

Stu_score FLOAT (5,2) DEFAULT 0.0,

cla_id INT (10),

CONSTRAINT fk_cla_id FOREIGN KEY (cla_id) REFERENCES class (cla_id) on DELETE SET NULL

);

Note: When inserting data, insert the data from the main table before inserting the data from the table.

When you delete data, delete the data from the table, and then delete the data in the primary table.

Five. Check (checking constraints)

CREATE TABLE Class (

cla_id INT (6) auto_increment PRIMARY KEY,

Cla_name VARCHAR (+) is not NULL UNIQUE,

CHECK (cla_id>0)

);

Note: MySQL does not support checking constraints, but writing CHECK constraints will not error

Other:

I. auto_increment (self-growth)

Add self-growth

1) When creating a table, add

CREATE TABLE T_user (user_id INT (Ten) auto_increment PRIMARY KEY);

2) through the ALTER statement

ALTER TABLE t_user MODIFY user_id INT (Ten) auto_increment;

ALTER TABLE t_user Change user_id user_id INT (Ten) auto_increment;

Delete self-growth

ALTER TABLE t_user MODIFY user_id INT (10);

ALTER TABLE t_user Change user_id user_id INT (10);

Note: There can be is only one auto column and it must is defined as a key.

A table can have only one self-growing column, and the column must have a constraint defined (either a primary KEY constraint or a unique constraint, or a foreign key constraint, but not a non-null and check constraint)

However, self-growth is typically used with primary keys and can only be used in numeric types

Two. Zerofill (0 fill)

Add 0 Fill

1) When creating a table, add

CREATE TABLE T_user (user_id INT (Ten) Zerofill);

2) through the ALTER statement

ALTER TABLE t_user MODIFY user_id INT (Ten) Zerofill;

ALTER TABLE t_user Change user_id user_id INT (Ten) Zerofill;

Delete 0 Fill

ALTER TABLE t_user MODIFY user_id INT (10);

ALTER TABLE t_user Change user_id user_id INT (10);

Note: 0 fills will not display a bit other than a valid bit, such as an int (5) For a field data type, and a value of 2, the 0 fill will show 00002 zero.

However, this effect is not displayed in Navicat for MySQL, only in a DOS window

Three. Default

Add a DEFAULT constraint

1) When creating a table, add

CREATE TABLE T_user (user_id INT (Ten) DEFAULT 3);

2) through the ALTER statement

ALTER TABLE t_user MODIFY user_id INT (Ten) DEFAULT 2;

ALTER TABLE t_user Change user_id user_id INT (Ten) DEFAULT 2;

Delete a DEFAULT constraint

ALTER TABLE t_user MODIFY user_id INT (10);

ALTER TABLE t_user Change user_id user_id INT (10);

Four. UNSIGNED (unsigned bit)

Add unsigned

1) When creating a table, add

CREATE TABLE T_user (user_id INT (Ten) UNSIGNED);

2) through the ALTER statement

ALTER TABLE t_user MODIFY user_id INT (Ten) UNSIGNED;

ALTER TABLE t_user Change user_id user_id INT (Ten) UNSIGNED;

Delete unsigned

ALTER TABLE t_user MODIFY user_id INT (10);

ALTER TABLE t_user Change user_id user_id INT (10);

Note: Unsigned action on numeric types

#从查询information_schema中查询指定表中的约束

Use INFORMATION_SCHEMA;

SELECT constraint_name from table_constraints WHERE table_name= ' student ';

Add, modify, and delete constraints in MySQL