Three minutes to understand the primary key, foreign key, non empty, unique and default constraints in mysql, and how to create a table

In the database , Data table is the most important in database 、 The most basic operation object , It is the basic unit of data storage . A data table is defined as a set of columns , Data is stored in a table in the format of rows and columns . Each line represents a unique record , Each column represents a field in the record .

One 、 Premise of operation

The data table must be created on the premise of having a database , First, you need to switch to the database , Use use Command to switch .

mysql> use yunweijia;
Database changed
mysql>

Two 、mysql establish / new table

create table < Table name > (
 Field 1, data type  [ Column level constraints ] [ The default value is ],
 Field 2, data type  [ Column level constraints ] [ The default value is ],
 Field 3, data type  [ Column level constraints ] [ The default value is ],
------
[ Table level constraints ]
)

For example, we create a table structure as follows ：

1、 First we need to create a database ;

mysql> create database test_db;
Query OK, 1 row affected (0.02 sec)

mysql>

2、 Then enter the database ;

mysql> use test_db;
Database changed
mysql>

3、 Create table ;

mysql> CREATE TABLE `test_user`  (
    ->   `id` int(0) NULL DEFAULT NULL COMMENT ' Employee number ',
    ->   `name` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT ' Employee name ',
    ->   `deptid` int(0) NULL DEFAULT NULL COMMENT ' Department number ',
    ->   `money` float NULL DEFAULT NULL COMMENT ' Wages '
    -> );
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql>

4、 See the table ;

mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| test_user         |
+-------------------+
1 row in set (0.00 sec)

mysql> desc test_user;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int         | YES  |     | NULL    |       |
| name   | varchar(25) | YES  |     | NULL    |       |
| deptid | int         | YES  |     | NULL    |       |
| money  | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql>

paraphrase ：

desc The command is to view mysql Table structure commands ;

3、 ... and 、 Use primary key constraints

Primary key , Also known as the main code , Is a combination of one or more columns in a table . Primary key constraint （Primary KeyConstraint） The data of primary key column is required to be unique , And cannot be empty . A primary key can uniquely identify a record in a table , You can combine foreign keys to define the relationship between different data tables , And it can speed up the speed of database query . The relationship between primary keys and records is the same as that between ID cards and people , They correspond to each other . There are two types of primary keys ： Single field primary key and Multi field union primary key .

1、 Single field primary key

A primary key consists of a field ,SQL The sentence format can be divided into the following two cases .

（1.1） Specify the primary key when defining columns

grammar ：

 Field name   data type  PRIMARY KEY [ The default value is ]

Example ：

mysql> CREATE TABLE `test_user_2`  (
    ->   `id` int(0)  PRIMARY KEY COMMENT ' Employee number ' ,
    ->   `name` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT ' Employee name ',
    ->   `deptid` int(0) NULL DEFAULT NULL COMMENT ' Department number ',
    ->   `money` float NULL DEFAULT NULL COMMENT ' Wages '
    -> );
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql>

See the result ：

mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| test_user         |
| test_user_2       |
+-------------------+
2 rows in set (0.00 sec)

mysql> desc test_user_2;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int         | NO   | PRI | NULL    |       |
| name   | varchar(25) | YES  |     | NULL    |       |
| deptid | int         | YES  |     | NULL    |       |
| money  | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql>

（1.2） Specify the primary key after defining all columns ;

grammar ：

[CONSTRAINT < Constraint name >] PRIMARY KEY [ Field name ]

Example ：

mysql> CREATE TABLE `test_user_3`  (
    ->   `id` int(0) COMMENT ' Employee number ' ,
    ->   `name` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT ' Employee name ',
    ->   `deptid` int(0) NULL DEFAULT NULL COMMENT ' Department number ',
    ->   `money` float NULL DEFAULT NULL COMMENT ' Wages ',
    ->  PRIMARY KEY(id)
    -> );
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql>

See the result ：

mysql> show  tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| test_user         |
| test_user_2       |
| test_user_3       |
+-------------------+
3 rows in set (0.00 sec)

mysql> desc test_user_3;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int         | NO   | PRI | NULL    |       |
| name   | varchar(25) | YES  |     | NULL    |       |
| deptid | int         | YES  |     | NULL    |       |
| money  | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql>

2、 Multi field union primary key

A primary key is a combination of multiple fields , The rules of grammar are as follows ：

PRIMARY KEY [ Field 1,  Field 2, ..., Field n]

Example ：

mysql> CREATE TABLE `test_user_4`  (
    ->   `id` int(0) COMMENT ' Employee number ' ,
    ->   `name` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci COMMENT ' Employee name ',
    ->   `deptid` int(0) COMMENT ' Department number ',
    ->   `money` float NULL DEFAULT NULL COMMENT ' Wages ',
    ->     PRIMARY KEY(name, deptid)
    -> );
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql>

See the result ：

mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| test_user         |
| test_user_2       |
| test_user_3       |
| test_user_4       |
+-------------------+
4 rows in set (0.00 sec)

mysql> desc test_user_4;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int         | YES  |     | NULL    |       |
| name   | varchar(25) | NO   | PRI | NULL    |       |
| deptid | int         | NO   | PRI | NULL    |       |
| money  | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql>

After statement execution , You create a file called test_user_4 Data sheet for ,name Fields and deptid Fields are grouped together to form test_user_4 Multi field joint primary key .

Four 、 Use foreign key constraints

Foreign keys are used to establish a connection between the data of two tables , It can be one or more columns . A table can have one or more foreign keys . Foreign keys correspond to referential integrity , The foreign key of a table can be null , If not null , Then each foreign key value must be equal to a value of the primary key in another table .

mysql What are Chinese and foreign keys ？

Foreign keys ： First, it is a field in the table , Although it may not be the primary key of this table , But to correspond to the primary key of another table . The main function of foreign keys is to ensure the integrity of data references , After defining foreign keys , It is not allowed to delete a row that has an association relationship in another table . The function of foreign keys is to maintain the consistency of data 、 integrity .

What is the main table ？ What is from the table ？

Main table （ Parent table ）： For two tables that have an association , In the associated field The table where the primary key is located is the primary table .

From the table （ Sub table ）： For two tables that have an association , In the associated field The table where the foreign key is located is from table .

How to be in mysql Create foreign keys in ?

grammar ：

[CONSTRAINT < Foreign key name >] FOREIGN KEY  Field name 1 [ , Field name 2,…]
REFERENCES < Main table name >  Primary key column 1 [ , Primary key column 2,…]

Foreign key name Name of the foreign key constraint defined for , In a table Cannot have foreign keys with the same name ;

Field name Indicates the fields and columns that need to add foreign key constraints to the sub table ;

Main table name That is, the name of the table on which the foreign key of the word table depends ;

Primary key column Represents the primary key column defined in the main table , Or column combination .

Let's build a new one test_dept Table of , The table structure is as follows ：

Field name	data type	remarks
id	INT	Department number
name	VARCHAR（30）	Department name
location	VARCHAR（50）	Department position

First, let's create this table ：

mysql> CREATE TABLE `test_dept` (
    ->   `id` int(0) PRIMARY KEY COMMENT ' Department number ',
    ->   `name` varchar(30) CHARACTER set utf8mb4 COLLATE utf8mb4_0900_ai_ci COMMENT ' Department name ',
    ->   `localhost` varchar(50) CHARACTER set utf8mb4 COLLATE utf8mb4_0900_ai_ci COMMENT ' Department position '
    -> );
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| test_dept         |
| test_user         |
| test_user_2       |
| test_user_3       |
| test_user_4       |
+-------------------+
5 rows in set (0.00 sec)

mysql>

Define data table test_user_5, Let his key deptid Relating to as a foreign key test_dept The primary key id, So our SQL The statement should be written like this ;

mysql> CREATE TABLE `test_user_5`  (
    ->   `id` int(0) PRIMARY KEY COMMENT ' Employee number ' ,
    ->   `name` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci COMMENT ' Employee name ',
    ->   `deptid` int(0) COMMENT ' Department number ',
    ->   `money` float NULL DEFAULT NULL COMMENT ' Wages ',
    ->   CONSTRAINT fk_user_dept FOREIGN KEY(deptid) REFERENCES test_dept(id)
    -> );
Query OK, 0 rows affected, 2 warnings (0.07 sec)

mysql>

Through the above sentence , You can see , We created a test_user_5 Table of , The primary key is id, And created a named fk_user_dapt The foreign key constraint of , The foreign key name is deptid, It depends on test_dept Primary key in table ID.

Be careful

The foreign key of the child table must be associated with the primary key of the parent table , And the data type of the associated field must match , If the types are different , When creating a sub table , There will be errors ERROR 1005 (HY000):Can't create table 'database.tablename'(errno: 150).

5、 ... and 、 Use non empty constraints

Non empty constraint （Not Null Constraint） The value of the field cannot be empty . For fields with non null constraints , If the user does not specify a value when adding data , The database system will report an error .

For the rest, go to VX official account “ Operation and maintenance home ” , reply “193” see .

------ “ Operation and maintenance home ” , reply “193” ------

------ “ Operation and maintenance home ” , reply “193” ------

------ “ Operation and maintenance home ” , reply “193” ------

linux introduction pdf,linux Hardware requirements , To configure linux Time server ,linux The system disk is full ,linux Multiple execution ,linuxcrc command ;
linux Copy file command ,linux You can download games ,linux Revision time ,linux The import table csv file , How to switch linux Graphical interface of ;
linux See if it is installed db2,linux Cannot find process entry record ,linux How to see the port number ,linuxgcc4.8,linux System host layout ,linux Transfer documents except ssh, Configuration bridging linux;