MySQL constraint types and examples

constraint

• Constraints ensure data integrity and consistency
• Constraints are divided into table-level constraints and column-level constraints
• Constraint types include: NOT NULL (non-empty constraint), PRIMARY KEY (primary key constraint), UNIQUE KEY (unique constraint), DEFAULT (default constraint), FOREIGN (foreign key constraint)

1. Primary key constraint

• PRIMARY KEY
• Each data table can only have one primary key
• The primary key ensures the uniqueness of the record, and the value of the primary key is not repeated
• The primary key is automatically NOT NULL

For example, create a student table and set the student number as the primary key. After creating the table, view the table structure through SHWO COLUMNS FROM student

CREATE TABLE student(
id int PRIMARY KEY,
stu_name varchar(20)
);

2. Unique constraint

• UNIQUE KEY
• The unique constraint can ensure the uniqueness of the record
• The unique constraint field can have a null value (NULL)
• Each data table can have multiple unique constraints

For example, create a teacher table with an auto-increment id field and a unique tea_name field.

CREATE TABLE teacher(
id int AUTO_INCREMENT PRIMARY KEY,
tea_name varchar(20) NOT NULL UNIQUE KEY
);

3. Default Constraints

• DEFAULT
• When inserting a record, if no value is explicitly assigned to the field, a default value is automatically assigned.

For example, create a course table with a default class time of 40 minutes.

CREATE TABLE course(
id int AUTO_INCREMENT PRIMARY KEY,
cou_name varchar(20) NOT NULL UNIQUE KEY,
time int DEFAULT 40
);
INSERT INTO course(cou_name) values('Chinese');

4. Not Null Constraint

• NOT NULL
• Enforces that a column cannot have a NULL value, and constraints enforce that a field always contains a value.
• This means that you cannot insert new records or update records without adding values ​​to the fields.

For example, when creating the "Persons" table, create not null constraints in the Id column and the name column:

create table Persons(
id int not NULL,
p_name varchar(20) not null, 
deparment varchar(20),
address varchar(20),
telNum varchar(20)
)
DESC Persons;

5. Foreign key constraints

• FOREIGN KEY
• Maintain data consistency and integrity
• Implement 1 to 1 or 1 to n relationship

1. The parent table and child table must use the same storage engine, and temporary tables are prohibited.
2. The storage engine of the data table can only be InnoDB
3. Foreign key columns and reference columns must have similar data types. The length of the numbers and whether they have a sign bit must be the same; the length of the characters can be different.
4. Indexes must be created for foreign key columns and reference columns. If an index does not exist on the foreign key column, MySQL will automatically create one.

CREATE TABLE school(
id int AUTO_INCREMENT PRIMARY KEY,
sname varchar(20) NOT NULL
);
CREATE TABLE student2(
id int AUTO_INCREMENT PRIMARY KEY,
sid int,
FOREIGN KEY (sid) REFERENCES school(id)
);

Referential Operations on Foreign Key Constraints

• CASCADE : Delete or update from the parent table and automatically delete or update matching rows in the child table
• If a data is deleted from the referenced column in the parent table, the row with the corresponding data is deleted from the child table.

CREATE TABLE student3(
id int AUTO_INCREMENT PRIMARY KEY,
sid int,
FOREIGN KEY (sid) REFERENCES school(id) ON DELETE CASCADE
);

• SET NULL: Deletes or updates rows from the parent table and sets the foreign key columns of the child table to NULL. If this option is used, you must ensure that the child table columns do not specify NOT NULL.
• Delete data from the parent table and set the reference in the child table to NULL
• RESTRICT: Reject delete or update operations on the parent table
• NO ACTION: A standard SQL keyword that is equivalent to RESTRICT in MySQL.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links