MySQL Constraint Types and Examples

Time:2019-6-16

constraint 

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

1. Primary key constraints

  • PRIMARY KEY
  • There can only be one primary key per table
  • The primary key guarantees the uniqueness of the record, and the value of the primary key does not duplicate.
  • The primary key is automatically NOT NULL

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


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

2. Unique Constraints

  • UNIQUE KEY
  • Unique constraints guarantee the uniqueness of records
  • Unique constrained fields can be null values (NULL)
  • Each data table can have multiple unique constraints

For example, create a teacher table with the ID field increasing and the tea_name unique


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

3. Default constraints

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

For example, create a coursetable 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 ('language');

4. Non-empty constraints

  • NOT NULL
  • Forced columns cannot be NULL values, and constrained fields always contain values.
  • This means that if you don’t add values to fields, you can’t insert new records or update records.

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


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
  • Implementing 1-to-1 or 1-to-N relationships

1. Parent and child tables must use the same storage engine, and temporary tables are prohibited.
2. The data table storage engine can only be InnoDB
3. Foreign key columns and reference columns must have similar data types. The length of a number or whether there are symbolic bits must be the same; the length of a character can be different.
4. Foreign key columns and reference columns must be indexed. MySQL automatically creates an index if the foreign key column does not exist.


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)
);

Reference operations for foreign key constraints

  • CASCADE: Delete or update matching rows from parent tables and automatically delete or update matching rows from child tables
  • The column referenced in the parent table deletes a data, and the row of the corresponding data is deleted in 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: Delete or update rows from the parent table and set the foreign key of the child table as NULL. If you use this item, you must ensure that the subtable column does not specify NOT NULL
  • Delete data from parent table and set references in child table to NULL
  • RESTRICT: Deny deletion or update of parent table
  • NO ACTION: Standard SQL keyword, same as RESTRICT in MySQL

summary

Above is the whole content of this article. I hope the content of this article has some reference value for your study or work. Thank you for your support to developpaer. If you want to know more about it, please check the links below.

Recommended Today

Interviewer: young man, what do you think of the principle of distributed system

1 Concept 1.1 model 1.2 copies 1.3 indicators for measuring distributed systems 2. Principle of distributed system 2.1 data distribution 2.2 basic copy agreement 2.3 lease mechanism 2.4 quorum mechanism 2.5 log technology 2.6 two phase submission protocol 2.7 MVCC 2.8 Paxos protocol 2.9 CAP 1 Concept 1.1 model node In a specific project, a […]