10. Database constraint - primary key constraint - unique constraint - non empty constraint - default value

Constraint overview

A constraint is actually a restriction used to decorate columns in a table This restriction ensures the correctness, validity and integrity of the data in the table.

Type of constraint

1. Primary key constraint (key)

1.1 function of primary key

It is used to mark the uniqueness of a record. Each table should have a primary key, and each table can only have one primary key. When the values of the name, age and score fields of some records are the same, it is impossible to distinguish these data, resulting in the redundancy of records in the database, which makes it difficult to manage data

Which field should be the primary key of the table? Usually, the business field is not used as the primary key. An id field is designed for each table separately, and the id is used as the primary key. Primary keys are for databases and programs, not for final customers. Therefore, it doesn't matter whether the primary key has meaning, as long as it is not repeated and not empty.

1.2 create primary key

# PRIMARY KEY: PRIMARY KEY
# Characteristics of primary key:
 1. Unique primary key field value(Unique and unrepeatable)
 2. Primary key column cannot be empty NULL
 3. There can only be one primary key in a table,But the primary key can be multiple fields (composite keys )

How to create a primary key:

-- Mode 1:When creating a table,Add primary key constraint (Field constraint area)
 format:
  create table Table name ( 
   Field name 1 field type field constraint area,  
   Field name 2 field type field constraint area
  );

-- Mode 2:When creating a table,Add a constraint to the constraint area(extend)
 Format 1:Single primary key
  create table Table name (
      Field name 1 field type,  
      Field name 2 field type,  
      primary key(Field name 1)  
  );

 Format 2:composite keys 
  create table Table name (
      Field name 1 field type,  
      Field name 2 field type,  
      primary key(Field name,Field name 1)  
  );

-- Mode 3:Add a primary key to an existing table(extend)
 alter table Table name add primary key(Field name);

Specific operation:

1.2.1 create a user table that contains fields (id, name) with id as the primary key

Method 1: when creating a table, add a primary key constraint in the constraint area of the field

-- Primary key constraint
-- Mode 1: When creating a table, add a primary key constraint in the constraint area of the field
CREATE TABLE user1(
 id INT PRIMARY KEY, # Add a primary key constraint in the constraint area of the field
 `name` VARCHAR(20)
);
-- Add data information
INSERT INTO user1 VALUES(1,'tom'); -- Normal insertion
INSERT INTO user1 VALUES(NULL,'tom'); -- Insert exception(Primary key cannot be empty): Column 'id' cannot be null
INSERT INTO user1 VALUES(1,'tom'); -- Insert exception(Primary key cannot be duplicate): Duplicate entry '1' for key 'PRIMARY'

The implementation is as follows:

-- Mode 1: When creating a table, add a primary key constraint in the constraint area of the field
mysql> CREATE TABLE user1(
    -> id INT PRIMARY KEY, # Add a primary key constraint in the constraint area of the field
    -> `name` VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.01 sec)

-- View all tables after creation
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| product       |
| stu3          |
| user1         |
+---------------+
3 rows in set (0.01 sec)

mysql> 
-- Normal insertion
mysql> INSERT INTO user1 VALUES(1,'tom');
Query OK, 1 row affected (0.00 sec)

mysql> select * from user1;
+----+------+
| id | name |
+----+------+
|  1 | tom  |
+----+------+
1 row in set (0.00 sec)

mysql> 

-- Insert exception(Primary key cannot be empty): Column 'id' cannot be null
mysql> INSERT INTO user1 VALUES(NULL,'tom');
ERROR 1048 (23000): Column 'id' cannot be null
mysql> 

-- Insert exception(Primary key cannot be duplicate): Duplicate entry '1' for key 'PRIMARY'
mysql> INSERT INTO user1 VALUES(1,'tom');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> 

Method 2: when creating a table, add a primary key constraint in the constraint area

-- Mode 2: When creating a table, add a primary key constraint in the constraint area
CREATE TABLE user2(
 id INT, 
 `name` VARCHAR(20),
 PRIMARY KEY (id)
);

The implementation is as follows:

-- Mode 2: When creating a table, add a primary key constraint in the constraint area
mysql> CREATE TABLE user2(
    -> id INT, 
    -> `name` VARCHAR(20),
    -> PRIMARY KEY (id) -- primary key 
    -> );
Query OK, 0 rows affected (0.01 sec)

-- see user2 And user1 Table structure of
mysql> desc user2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       | -- Can see id of key Value is PRI And user1 Table consistent
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> desc user1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       | 
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> 

Method 3: after creating a table, add a primary key constraint by modifying the table structure

-- Mode 3: After creating the table,Add a primary key constraint by modifying the table structure
CREATE TABLE user3(
 id INT, 
 `name` VARCHAR(20)
);
-- Modify table add primary key
ALTER TABLE user3 ADD PRIMARY KEY (id);

The implementation is as follows:

-- Mode 3: After creating the table,Add a primary key constraint by modifying the table structure
mysql> CREATE TABLE user3(
    -> id INT, 
    -> `name` VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.01 sec)
-- Modify table add primary key
mysql> ALTER TABLE user3 ADD PRIMARY KEY (id);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
-- see user3 Table structure, you can see id Already set as primary key
mysql> desc user3;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> 

1.2.2 create a table user table that contains fields (id, name) with id and name as joint primary keys

Above, we use id as a single primary key and multiple fields as a joint primary key, as follows:

image-20200527101914892

image-20200527101757783

-- composite keys 
CREATE TABLE user4(
 id INT, 
 `name` VARCHAR(20),
 PRIMARY KEY (id,`name`)
);
-- ensure id and name Field is not empty,Then jointly judge the uniqueness
INSERT INTO user4 VALUES(1,'tom'); -- Normal insertion
INSERT INTO user4 VALUES(2,"tom"); -- Normal insertion
INSERT INTO user4 VALUES(1,"rose"); -- Normal insertion
INSERT INTO user4 VALUES(2,"rose"); -- Normal insertion
INSERT INTO user4 VALUES(1,"tom"); -- Insert exception:Duplicate entry '1-tom' for key 'PRIMARY'
INSERT INTO user4 VALUES(NULL,"jack"); -- Insert exception:Column 'id' cannot be null
INSERT INTO user4 VALUES(3,NULL); -- Insert exception:Column 'name' cannot be null

"Note: there is only one primary key in a table. The primary key can be multiple fields, but we usually add a field id as the primary key "

The implementation is as follows:

-- composite keys : contain id And name Two fields are used as primary keys, and data in the table is required id And name The combination cannot be repeated
mysql> CREATE TABLE user4(
    -> id INT, 
    -> `name` VARCHAR(20),
    -> PRIMARY KEY (id,`name`)
    -> );
Query OK, 0 rows affected (0.02 sec)

-- ensure id and name Field is not empty,Then jointly judge the uniqueness
-- Normal insertion
mysql> INSERT INTO user4 VALUES(1,'tom');
Query OK, 1 row affected (0.01 sec)

-- Normal insertion: although name The field duplicates the previous data, but id atypism
mysql> INSERT INTO user4 VALUES(2,"tom");
Query OK, 1 row affected (0.01 sec)

-- Normal insertion: id Consistent with the first data, but name Inconsistent data
mysql> INSERT INTO user4 VALUES(1,"rose");
Query OK, 1 row affected (0.00 sec)

-- Normal insertion
mysql> INSERT INTO user4 VALUES(2,"rose");
Query OK, 1 row affected (0.00 sec)

-- Insert exception:Duplicate entry '1-tom' for key 'PRIMARY'
mysql> INSERT INTO user4 VALUES(1,"tom");
ERROR 1062 (23000): Duplicate entry '1-tom' for key 'PRIMARY'

-- Insert exception:Column 'id' cannot be null
mysql> INSERT INTO user4 VALUES(NULL,"jack");
ERROR 1048 (23000): Column 'id' cannot be null

-- Insert exception:Column 'name' cannot be null
mysql> INSERT INTO user4 VALUES(3,NULL);
ERROR 1048 (23000): Column 'name' cannot be null

1.3 delete primary key

ALTER TABLE table name DROP PRIMARY KEY;

Specific operation:

• Delete the primary key of user1 table

ALTER TABLE user1 DROP PRIMARY KEY;

The implementation is as follows:

-- Delete table structure before primary key
mysql> desc user1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       | -- id As primary key
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

-- delete user1 Primary Key 
mysql> alter table user1 drop primary key;
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

-- You can see the table structure after deleting the primary key id It's not a primary key anymore.
mysql> desc user1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> 

1.4 auto increment of primary key_ INCREMENT

If the primary key is added by ourselves, it is likely to be repeated. We usually want the database to automatically generate the value auto of the primary key field every time a new record is inserted_ Increment indicates automatic growth (the field type is an integer number)

Specific operation:

Create a student table user5 that contains fields (id, name) with id as the primary key and grows automatically

CREATE TABLE user5 (
 id INT PRIMARY KEY AUTO_INCREMENT,
 name VARCHAR(20)
);

• insert data

-- The primary key automatically grows from 1 by default
INSERT INTO user5 (name) VALUES ('Tang Monk');
INSERT INTO user5 (name) VALUES ('Sun WuKong');
INSERT INTO user5 VALUES (null,'Zhu Bajie');
INSERT INTO user5 VALUES (null,'Monk Sha');

The implementation is as follows:

-- establish user5 Table and set id The field is a self incrementing primary key
mysql> CREATE TABLE user5 (
    -> id INT PRIMARY KEY AUTO_INCREMENT,
    -> name VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.01 sec)

-- insert data
mysql> INSERT INTO user5 (name) VALUES ('Tang Monk');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO user5 (name) VALUES ('Sun WuKong');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO user5 VALUES (null,'Zhu Bajie');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO user5 VALUES (null,'Monk Sha');
Query OK, 1 row affected (0.00 sec)

-- see user5 In table id The data increases automatically from 1
mysql> select * from user5;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | Tang Monk      |
|  2 | Sun WuKong    |
|  3 | Zhu Bajie    |
|  4 | Monk Sha      |
+----+-----------+
4 rows in set (0.00 sec)

mysql> 

Difference between DELETE and TRUNCATE

DELETE Delete the data in the table without resetting AUTO_INCREMENT Value of
TRUNCATE Destroy the table, rebuild the table, AUTO_INCREMENT Reset to 1
 If you want to customize id Value of,You can use the following sql Make settings,Generally not required
ALTER TABLE Table name AUTO_INCREMENT=Starting value;

Let's demonstrate:

Demonstrates the impact of deleting table data on auto increment fields

-- delete Delete previous user5 Table data, self incrementing at this time id = 4
mysql> select * from user5;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | Tang Monk      |
|  2 | Sun WuKong    |
|  3 | Zhu Bajie    |
|  4 | Monk Sha      |
+----+-----------+
4 rows in set (0.00 sec)

-- delete Delete table data
mysql> delete from user5;
Query OK, 4 rows affected (0.00 sec)

-- Insert a piece of data and view the data in the table id From to 5. 
mysql> INSERT INTO user5 VALUES (null,'Monk Sha');
Query OK, 1 row affected (0.00 sec)

mysql> select * from user5;
+----+--------+
| id | name   |
+----+--------+
|  5 | Monk Sha   | -- explain delete Deleted table data will not be reset id But then the previous self increment continues to increase
+----+--------+
1 row in set (0.00 sec)

mysql> 

Demonstrate the impact of TRUNCATE deleting table data on auto increment fields

-- truncate Before table user5 data
mysql> select * from user5;
+----+--------+
| id | name   |
+----+--------+
|  5 | Monk Sha   |
+----+--------+
1 row in set (0.00 sec)

-- truncate delete user5 Table data
mysql> truncate table user5;
Query OK, 0 rows affected (0.01 sec)

-- Insert another piece of data and you can find the self increment id Reset to 1
mysql> INSERT INTO user5 VALUES (null,'Monk Sha');
Query OK, 1 row affected (0.00 sec)

mysql> select * from user5;
+----+--------+
| id | name   |
+----+--------+
|  1 | Monk Sha   |
+----+--------+
1 row in set (0.00 sec)

mysql> 

Modify the primary key auto increment initial value of the table

-- modify user5 The initial value of self increment in the table is 10
mysql> alter table user5 auto_increment=10;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- Insert another piece of data to see the self increment
mysql> INSERT INTO user5 VALUES (null,'Monk Sha');
Query OK, 1 row affected (0.01 sec)

mysql> select * from user5;
+----+--------+
| id | name   |
+----+--------+
|  1 | Monk Sha   |
| 10 | Monk Sha   | -- You can see that it has increased since 10.
+----+--------+
2 rows in set (0.00 sec)

mysql> 

2. UNIQUE constraint

The value of this field cannot be repeated in this table

2.1 basic format of unique constraints

Field name field type UNIQUE

2.2 achieving unique constraints

Specific operation:

• Create a student table st7, which contains fields (ID, name). The name column sets unique constraints. Students with the same name cannot appear

CREATE TABLE st7 (
 id INT,
 NAME VARCHAR(20) UNIQUE
);

• Add data and view the problem of data duplication

INSERT INTO st7 VALUES (1, 'army officer's hat ornaments');
INSERT INTO st7 VALUES (2, 'Xi Shi');
INSERT INTO st7 VALUES (3, 'Wang Zhaojun');
INSERT INTO st7 VALUES (4, 'Yang Yuhuan');

-- Insert the same name appears name repeat: Duplicate entry 'army officer's hat ornaments' for key 'name'
INSERT INTO st7 VALUES (5, 'army officer's hat ornaments');

-- Multiple occurrences null What happens when you get married? because null There is no value, so there is no duplication problem
INSERT INTO st7 VALUES (5, NULL);
INSERT INTO st7 VALUES (6, NULL);

The implementation is as follows:

-- Create student table st7, Include field(id, name),name This column sets unique constraints,Students with the same name cannot appear
mysql> CREATE TABLE st7 (
    -> id INT,
    -> NAME VARCHAR(20) UNIQUE
    -> );
Query OK, 0 rows affected (0.02 sec)

-- Insert four pieces of data
mysql> INSERT INTO st7 VALUES (1, 'army officer's hat ornaments');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO st7 VALUES (2, 'Xi Shi');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO st7 VALUES (3, 'Wang Zhaojun');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO st7 VALUES (4, 'Yang Yuhuan');
Query OK, 1 row affected (0.00 sec)

-- Insert the same name appears name repeat: Duplicate entry 'army officer's hat ornaments' for key 'name'
mysql> INSERT INTO st7 VALUES (5, 'army officer's hat ornaments');
ERROR 1062 (23000): Duplicate entry 'army officer's hat ornaments' for key 'NAME'

-- Multiple occurrences null What happens when you get married? because null There is no value, so there is no duplication problem
mysql> INSERT INTO st7 VALUES (5, NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO st7 VALUES (6, NULL);
Query OK, 1 row affected (0.00 sec)

-- Can see null Can be inserted repeatedly
mysql> select * from st7;
+------+-----------+
| id   | NAME      |
+------+-----------+
|    1 | army officer's hat ornaments      |
|    2 | Xi Shi      |
|    3 | Wang Zhaojun    |
|    4 | Yang Yuhuan    |
|    5 | NULL      |
|    6 | NULL      |
+------+-----------+
6 rows in set (0.00 sec)

mysql> 

3. Non NULL constraint NOT NULL

The modified field must be set with a value, which cannot be NULL

3.1 basic format of non empty constraints

Field name field type NOT NULL

3.2 implementation of non null constraints

Specific operation:

• Create a student table st8 that contains fields (id,name,gender), where name cannot be NULL

CREATE TABLE st8 (
 id INT,
 NAME VARCHAR(20) NOT NULL,
 gender CHAR(2)
);

• Add a complete record

INSERT INTO st8 VALUES (1, 'Guo Fucheng', 'male');
INSERT INTO st8 VALUES (2, 'dawn', 'male');
INSERT INTO st8 VALUES (3, 'Xue You Zhang', 'male');
INSERT INTO st8 VALUES (4, 'Lau Andy', 'male');

-- The name cannot be null if it is not assigned null: Column 'name' cannot be null
INSERT INTO st8 VALUES (5, NULL, 'male');

The implementation is as follows:

-- Create student table st8, Include field(id,name,gender)among name Cannot be NULL
mysql> CREATE TABLE st8 (
    -> id INT,
    -> NAME VARCHAR(20) NOT NULL,
    -> gender CHAR(2)
    -> );
Query OK, 0 rows affected (0.01 sec)

-- Insert four pieces of data
mysql> INSERT INTO st8 VALUES (1, 'Guo Fucheng', 'male');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO st8 VALUES (2, 'dawn', 'male');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO st8 VALUES (3, 'Xue You Zhang', 'male');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO st8 VALUES (4, 'Lau Andy', 'male');
Query OK, 1 row affected (0.00 sec)

-- The name cannot be null if it is not assigned null: Column 'name' cannot be null
mysql> INSERT INTO st8 VALUES (5, NULL, 'male');
ERROR 1048 (23000): Column 'NAME' cannot be null
mysql> 

-- You can see that after the non NULL constraint is set, st8 Table cannot be inserted null data
mysql> select * from st8;
+------+-----------+--------+
| id   | NAME      | gender |
+------+-----------+--------+
|    1 | Guo Fucheng    | male     |
|    2 | dawn      | male     |
|    3 | Xue You Zhang    | male     |
|    4 | Lau Andy    | male     |
+------+-----------+--------+
4 rows in set (0.00 sec)

mysql> 

4. The DEFAULT value is DEFAULT

When adding data to a table, if the data of this field is not specified, the default value is used

4.1 default value format

Field name field type DEFAULT default

4.2 implementing field defaults

Specific operation:

• Create a student table st9 that contains fields (id,name,address). The default value of the address is Guangzhou

CREATE TABLE st9 (
 id INT,
 NAME VARCHAR(20),
 address VARCHAR(50) DEFAULT 'Guangzhou'
);

• Add a record and use the default address

INSERT INTO st9 (id, NAME) VALUES (1, 'Lau Andy');

• Add a record without using the default address

INSERT INTO st9 VALUES (2, 'Xue You Zhang', 'Hong Kong, China');

The implementation is as follows:

-- Create a student table st9，Include field(id,name,address)， The default address is Guangzhou
mysql> CREATE TABLE st9 (
    -> id INT,
    -> NAME VARCHAR(20),
    -> address VARCHAR(50) DEFAULT 'Guangzhou' -- Set defaults
    -> );
Query OK, 0 rows affected (0.02 sec)

-- Insert a piece of data without filling in address Value of
mysql> INSERT INTO st9 (id, NAME) VALUES (1, 'Lau Andy');
Query OK, 1 row affected (0.00 sec)

-- You can see that the data is automatically set to the default value: Guangzhou
mysql> select * from st9;
+------+-----------+---------+
| id   | NAME      | address |
+------+-----------+---------+
|    1 | Lau Andy    | Guangzhou    |
+------+-----------+---------+
1 row in set (0.00 sec)

-- Add a record,Do not use default address
mysql> INSERT INTO st9 VALUES (2, 'Xue You Zhang', 'Hong Kong, China');
Query OK, 1 row affected (0.00 sec)

-- If a value is set, the default value is not written
mysql> select * from st9;
+------+-----------+---------+
| id   | NAME      | address |
+------+-----------+---------+
|    1 | Lau Andy    | Guangzhou    |
|    2 | Xue You Zhang    | Hong Kong, China    | -- Use the entered value
+------+-----------+---------+
2 rows in set (0.00 sec)

mysql>