MySQL Constraints Super Detailed Explanation

MySQL Constraint Operations

Concept: Limit the data in the table to ensure the correctness, validity and completeness of the data.

Classification:

• primary key
• Not null constraint: not null
• Unique constraint: unique
• foreign key constraint: foreign key

1. Non-null constraint

not null , the value cannot be empty.

Add a not null constraint when creating the table:

CREATE TABLE stu(
 id INT,
 NAME VARCHAR(20) NOT NULL
);

After creating the table, add a non-empty constraint

ALTER TABLE stu 
MODIFY NAME VARCHAR(20) NOT NULL;

Remove Not Null Constraint

ALTER TABLE stu 
MODIFY NAME VARCHAR(20);

2. Unique constraint

unique， the value cannot be repeated.

Add a unique constraint when creating a table

CREATE stu(
 id INT;
 phone_number VARCHAR(20) UNIQUE
);

Note: In mysql , the value of a column defined by a unique constraint can have multiple null .

Dropping a unique constraint

ALTER TABLE stu
DROP INDEX phone_number;

After creating the table, add a unique constraint

ALTER TABLE stu 
MODIFY phone_number VARCHAR(20) UNIQUE;

3. Primary key constraint

primary key，

• Non-empty and unique.
• A table can have only one field as the primary key.
• The primary key is the unique identifier of the records in the table.
  

Add a primary key constraint when creating a table

CREATE TABLE stu( 
 id INT PRIMARY KEY,
 NAME VARCHAR(20)
);

Deleting a primary key

ALTER TABLE stu 
DROP PRIMARY KEY;

After creating the table, add the primary key

ALTER TABLE stu 
MODIFY id INT PRIMARY KEY;

Here is a knowledge point: automatic growth

Concept: If a column is of numeric type, use auto_increment to achieve automatic growth.

example:

When creating a table, add a primary key constraint and complete the automatic growth of the primary key

CREATE TABLE stu(
 id INT PRIMARY KEY AUTO_INCREMENT,
 NAME VARCHAR(20)
);
#Automatically increase the value based on the last row of the current column.

Remove autogrowth

ALTER TABLE stu
MODIFY id INT;
#This will only delete the automatic growth, the primary key cannot be deleted.

After creating the table, add automatic growth

ALTER TABLE stu
MODIFY id INT AUTO_INCREMENT;

4. Foreign key constraints

foreign ley , creates relationships between tables to ensure the correctness of the data.

When you create a table, you can add a foreign key

CREATE TABLE tablename(
 ...
 Foreign key column CONSTRAINT Foreign key name FOREIGN KEY (foreign key column name) REFERENCES Primary table name (primary table column name)
);

Deleting a foreign key

ALTER TABLE table name DROP FOREIGN KEY foreign key name;

After creating the table, add the foreign key

ALTER TABLE table name ADD CONSTRAINT foreign key name FOREIGN KEY (foreign key field name) REFERENCES primary table name (primary table column name);

5. Cascade

Add cascade operation

ALTER TABLE table name ADD CONSTRAINT foreign key name FOREIGN KEY (foreign key field name) REFERENCES primary table name (primary table column name)
ON UPDATE CASCADE ON DELETE CASCADE;

Cascade delete

ON UPDATE CASCADE

This is the end of this article about the super detailed explanation of MySQL constraints. For more relevant MySQL constraints content, please search 123WORDPRESS.COM’s previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!