(Big Data Engineer Learning path) Fourth Step SQL Foundation course----constraints

First, Introduction

A constraint is a restriction that ensures the integrity and uniqueness of a table's data by restricting the data on the table's rows or columns. This section of the experiment is familiar with several constraints in MySQL in operation.

II. content 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. Create a table with constraints

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

Of course, you do not have to enter the command line to build this database, the experimental building has been prepared for you. Enter the command first to enter desktop:

cd Desktop

git clone http://git.shiyanlou.com/shiyanlou/SQL3

When the download is complete, enter "CD ~" (note that there are spaces) to return to the original directory, then enter the command to open the MySQL service and log in with the root user:

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

Just download the SQL3 directory from GitHub, there is a file "Mysql-03-01.sql", which contains the code can create a new database "Mysql_shiyan", and then in which three tables were created "department", "employee", " Project, which contains a variety of constraints.

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

Run this file and enter the command:

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

To view this database, enter the command "show tables;", Visible:

3. Primary key

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

In Mysql-03-01.sql, here are the primary keys:

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:

4. Default value Constraint

The default value constraint specifies what to do when there are columns with the default constraint, and when the insert data is empty.

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);INSERT INTO department(dpt_name) VALUES(‘dpt2‘); #people_num为空

Enter the command "select * from department;", People_num of the second row in the visible table is populated with the value of default (10):

5. Unique Constraint

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

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:

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

As a result, a warning appears, but the data is still inserted successfully:

At this point the contents of the Employee table are:

(Big Data Engineer Learning path) Fourth Step SQL Foundation course----constraints