Shulou(Shulou.com)06/03 Report--

This article mainly introduces "what is the meaning of MySQL constraint". In daily operation, I believe that many people have doubts about what MySQL constraint means. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful for you to answer the question of "what is the meaning of MySQL constraint?" Next, please follow the editor to study!

Catalogue

MySQL constraint operation

1. Non-empty constraint

two。 Unique constraint

3. Primary key constraint

4. Foreign key constraint

5. Cascading

MySQL constraint operation

Concept: limit the data in the table to ensure the correctness, validity and integrity of the data.

Classification:

Primary key: primary key

Non-empty constraints: not null

Unique constraint: unique

Foreign key constraints: foreign key

1. Non-empty constraint

Not null, the value cannot be empty.

Add a non-empty constraint when you create a 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

Delete a non-empty constraint

ALTER TABLE stu MODIFY NAME VARCHAR (20) 2. Unique constraint

Unique, the value cannot be repeated.

Add unique constraints when creating a table

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

Note: in mysql, a column qualified by a unique constraint can have multiple null values.

Delete unique constraint

ALTER TABLE stuDROP 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

Not empty and unique.

A table can have only one field as the primary key.

The primary key is the unique identity of the record in the table.

Add a primary key constraint when creating a table

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

Delete 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 an additional point of knowledge: automatic growth

Concept: if a column is numeric, you can use auto_increment to achieve automatic growth.

Example:

When you create a table, add a primary key constraint and complete the primary key growth automatically

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

Delete automatic growth

ALTER TABLE stuMODIFY id INT;# will only delete automatic growth, the primary key can not be deleted.

After creating the table, add automatic growth

ALTER TABLE stuMODIFY id INT AUTO_INCREMENT;4. Foreign key constraint

Foreign ley, let the table have a relationship with the table, so as to ensure the correctness of the data.

When you create a table, you can add foreign keys

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

Delete foreign key

ALTER TABLE table name DROP FOREIGN KEY foreign key name

After creating the table, add a foreign key

ALTER TABLE table name ADD CONSTRAINT foreign key name FOREIGN KEY (foreign key field name) REFERENCES main table name (main table column name); 5. Cascading

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 deletion

ON UPDATE CASCADE at this point, the study on "what is the meaning of MySQL constraint" is over, hoping to solve everyone's doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.