The constraint of MySQL foundation reinforcement--sql

Second, the experimental Step 2.1 constraint classification

Listen to the name. Constraint is a restriction that ensures the integrity and uniqueness of the table's data by restricting the data of the table's rows or columns.

In MySQL, there are usually these kinds of constraints:

Constraint type:	PRIMARY Key	Default Value	only	FOREIGN Key	Non-empty
Key words:	PRIMARY KEY	DEFAULT	UNIQUE	FOREIGN KEY	Not NULL

2.2 Creating a table with constraints

To facilitate the introduction of these kinds of constraints, we first set up a database.

Note: If you are learning from the previous section directly into this section, first delete the database created in the previous section mysql_shiyan and delete the statement as DROP DATABASE mysql_shiyan; .

Of course, you do not have to enter the command line to build this database, the experimental building has been prepared for you. First open the XFCE terminal, enter the command to enter the /home/shiyanlou/Desktop directory:

cd Desktop

Use the git command to download the required data files to a local folder:

clone https://github.com/shiyanlou/SQL3

When the download is complete, enter the command to open the MySQL service and log in with the root user:

#打开 MySQL 服务sudo service mysql start        #使用 root 用户登录mysql -u root                   

In the SQL3 directory that you just downloaded, there is a file MySQL-03-01.sql that contains the code to create a new database and mysql_shiyan then three tables in it, which department employee project contain various constraints.

(SQL3 directory on the desktop, you can use Gedit to view the MySQL-03-01.sql files inside.) ）

To load the data in the file, you need to enter the command in the MySQL console:

source /home/shiyanlou/Desktop/SQL3/MySQL-03-01.sql;

To view this database, enter the command show tables; , which is visible:

2.3 Primary Key

The primary key (PRIMARY key) is used to constrain a row in the table, and as a unique identifier for the row, the primary key is the key to the exact row in one table. The primary key cannot be duplicated and cannot be empty.

In the MySQL-03-01.sql , here is the primary key:

You can also define a primary key like this:

There is also a special primary key-the composite primary key. A primary key can be a column in a table, or it can be identified by two or more columns in a table, such as:

2.4 Default value constraints

The default value constraint, default, specifies that when a column with a default constraint is blank, the Insert data is empty, and the defaults are used.

In MySQL-03-01.sql , this code contains the DEFAULT constraint:

The default constraint is only reflected when using the INSERT statement (as described in the previous experiment), where the default constraint is populated with the value of the default, such as a statement:

# 正常插入数据INSERT INTO department(dpt_name,people_num) VALUES(‘dpt1‘,11);#插入新的数据，people_num 为空，使用默认值INSERT INTO department(dpt_name) VALUES(‘dpt2‘); 

Input command SELECT * FROM department; , the people_num of the second row in the visible table is populated with the default value (10):

2.5 UNIQUE constraints

A unique constraint is simple, which specifies that the value of a column specified in a table must not have duplicate values, that is, each value in this column is unique.

In MySQL-03-01.sql , there are also UNIQUE constraints:

Insert fails if there is a unique constraint when the newly inserted data and the existing data are duplicated by the INSERT statement, for example:

INSERT INTO employee VALUES(01,‘Tom‘,25,3000,110110,‘dpt1‘);INSERT INTO employee VALUES(02,‘Jack‘,30,3500,110110,‘dpt2‘); 

Results

2.6 Foreign KEY constraints

The foreign key (FOREIGN key) ensures both data integrity and the relationship between tables.

A table can have multiple foreign keys, and each foreign key must REFERENCES (reference) The primary key of another table, the column that is constrained by the foreign key, and the value must have a corresponding value in the column it references.

In INSERT, insert fails if the value of the foreign KEY constraint is not in the reference column, such as the following command, the Reference column (Dpt_name of the Department table) does not have DPT3 in it:

INSERT INTO employee VALUES(02,‘Jack‘,30,3500,114114,‘dpt3‘);

After the DPT3 is visible and changed to Dpt2 (Dpt2 in the Department table), the insert succeeds:

2.7 Non-null constraints

A non-null constraint (NOT NULL), which can be understood by a name, is a non-null constrained column that must not be empty when inserting a value.

Violations of non-null constraints in MySQL, will not error, there will only be warnings, such as the following statement:

#INSERT 成功 age 为空，因为没有非空约束，表中显示 NULLINSERT INTO employee(id,name,salary,phone,in_dpt) VALUES(03,‘Jim‘,3400,119119,‘dpt2‘); #警告 salary 被非空约束，值为空，表中显示0INSERT INTO employee(id,name,age,phone,in_dpt) VALUES(04,‘Bob‘,23,123456,‘dpt1‘); 

As a result, a warning appears, but the data is still inserted successfully, the lab building environment is 5.5.50 , the 5.6 version of MySQL will be error, prohibit inserting data that does not conform to the non-null constraint:

At this point the contents of the Employee table are:

Iii. Summary of the experiment

In this section of the experiment, a database instance is used to understand the characteristics of primary key, default value, foreign key, non-NULL, and UNIQUE constraints, which are the most common, and need to carefully understand the meaning of each constraint and the usage scenario. In the next section, we will go to the SELECT statement to learn.

The constraint of MySQL foundation reinforcement--sql