Analysis of MySQL database constraints and table design examples

Database constraints

not null

Specify that the storage of a certain column cannot be a null value

create table student (id int not null,name varchar(20));
Query OK, 0 rows affected (0.01 sec)
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

unique

Guarantee that a certain column must have a unique value , an error will be reported when inserting duplicate values

default

Specifies the default value when assigning values ​​to columns

create table student(id int,name varchar(20) default '匿名');

primary key primary key

The primary key constraint is not The combination of null and unique ensures that the assignment of a column cannot be null and is unique

auto_increment Auto-increment features:

1. If there is no record in the table, the auto-increment starts from 1

2. If there is data, it will be incremented from the previous record.

3. Insert and then delete the data. The incremented value will not be reused and will start from the deleted one. Auto-increment

create table student (id int primary key auto_increment,name varchar(20));
Query OK, 0 rows affected (0.01 sec)
mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> insert into student values(null,'张三');
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
+----+--------+
1 row in set (0.00 sec)

foreign key foreign key

Foreign key constraints, the data in table one must exist in table two, refer to the integrity criteria

Foreign key constraints Describes the "dependency relationship" between two columns of two tables

Foreign key constraints will affect the deletion of the table. For example, the class table of the following example is associated, so it cannot be easily deleted

mysql> create table class (
    -> id int primary key,
    -> name varchar(20) not null
    -> );
Query OK, 0 rows affected (0.04 sec)
mysql> create table student (
    -> id int primary key,
    -> name varchar(20) not null,
    -> email varchar(20) default 'unknow',
    -> QQ varchar(20) unique,
    -> classId int , foreign key (classId) references class(id)
    -> );
Query OK, 0 rows affected (0.03 sec)
mysql> desc class;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.02 sec)

mysql> desc student;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | NO   | PRI | NULL    |       |
| name    | varchar(20) | NO   |     | NULL    |       |
| email   | varchar(20) | YES  |     | unknow  |       |
| QQ      | varchar(20) | YES  | UNI | NULL    |       |
| classId | int(11)     | YES  | MUL | NULL    |       |
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

check

Specify a condition and use the condition to determine the value

But mysql does not support

create table test_user (
   id int,
   name varchar(20),
   sex varchar(1),
   check (sex ='男' or sex='女')
);

table design

One-to-one

The one-to-one design table is like the student table and the account table. One account corresponds to one student, and each student has only one account

Expression method

1 .These two entities can be represented by one table

2. They can be represented by two tables, one of which contains the id of the other table

One-to-many

A student should be in a class, and a class can contain multiple students

Representation method:

1. In the class table, add a new column to represent the students in this class What are the student IDs (mysql does not have an array type, redis can)

2. The class table remains unchanged, and a new column of classId is added to the student table

many-to-many

Many-to-many design table is like a student table and a course schedule. A student can choose multiple courses, and a course can also be selected by multiple students.

Representation method:

Use a related table , to represent the relationship between two entities

Many-to-many table creation instance

-- 学生表
mysql> create table test_student (
    -> id int primary key,
    -> name varchar(10) default 'unknow'
    -> );
Query OK, 0 rows affected (0.03 sec)
-- 选课表
mysql> create table test_course (
    -> id int primary key,
    -> name varchar(20) default 'unknow'
    -> );
Query OK, 0 rows affected (0.02 sec)
-- 成绩表
mysql> create table test_score (
    -> studentId int,
    -> courseId int,
    -> score int,
    -> foreign key (studentId) references test_student(id),
    -> foreign key (courseId) references test_course(id)
    -> );
Query OK, 0 rows affected (0.02 sec)

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

mysql> desc test_coures;
ERROR 1146 (42S02): Table 'java_5_27.test_coures' doesn't exist
mysql> desc test_course;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | unknow  |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> desc test_score;
+-----------+---------+------+-----+---------+-------+
| Field     | Type    | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| studentId | int(11) | YES  | MUL | NULL    |       |
| courseId  | int(11) | YES  | MUL | NULL    |       |
| score     | int(11) | YES  |     | NULL    |       |
+-----------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Insert data into the instance to implement many-to-many

mysql> insert into test_student values (1, 'listen');
Query OK, 1 row affected (0.01 sec)

mysql> insert into test_course values (1, '数学');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_student values (2, 'Faker');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_course values (2, '数学');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_score values(1, 1, 90);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_score values (1, 2, 99);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_score values (2, 1, 50);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_score values (2, 2, 60);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test_student;
+----+--------+
| id | name   |
+----+--------+
|  1 | listen |
|  2 | Faker  |
+----+--------+
2 rows in set (0.00 sec)

mysql> select * from test_course;
+----+--------+
| id | name   |
+----+--------+
|  1 | 数学   |
|  2 | 语文   |
+----+--------+
2 rows in set (0.00 sec)

mysql> select * from test_score;
+-----------+----------+-------+
| studentId | courseId | score |
+-----------+----------+-------+
|         1 |        1 |    90 |
|         1 |        2 |    99 |
|         2 |        1 |    50 |
|         2 |        2 |    60 |
+-----------+----------+-------+
4 rows in set (0.00 sec)

The above is the detailed content of Analysis of MySQL database constraints and table design examples. For more information, please follow other related articles on the PHP Chinese website!