MySQL Database learning "Fifth" integrity constraints

First, Introduction

Constraints are the same as the width of the data type, which are optional parameters

Role: To ensure the integrity and consistency of data
Mainly divided into:

PRIMARY Key (PK) identifies the    field as the primary key of the table and can uniquely identify the record foreign key (FK) identifies the    field as the foreign key of the table    not null identity The field cannot be empty unique key (UK)    The value that identifies the field is unique auto_increment the    value of the field is automatically grown (integer type, and the primary key)    default for this field is set to the defaults unsigned unsigned zerofill using 0 padding

Description

1. Allow null, default NULL, set not NULL, field not allowed to be empty, must be assigned a value of 2. Whether a field has a default value, the default value is NULL, if the record is inserted without assigning a value to the field, this field uses the default value of the Sex enum (' Male ', ' female ') not null default ' male ' age int unsigned NOT NULL Default 20 must be positive (unsigned) is not allowed to be null defaults to 203. Is the key primary key primary key foreign key foreign key index (Index,unique ...)

Two, not NULL and default

Nullable, NULL indicates NULL, non-string
Not null-non-nullable
Null-Nullable

Default defaults, default values can be specified when columns are created, and default values are automatically added when data is inserted if not actively set
CREATE TABLE TB1 (
ID int NOT NULL Defalut 2,
num int NOT NULL
)

Third, UNIQUE constraints (uniqueness constraints)

Single row unique

-----1. Single-column unique---------CREATE TABLE t2 (ID int not null unique,name char); INSERT into T2 values (1, ' Egon '); INSERT into T2 v Alues (1, ' Alex '); #上面创建表的时候把id设置了唯一约束. So when you insert id=1, it's going to go wrong.

Multi-Column Unique

-----2. Multiple columns unique---------#255.255.255.255create table server (ID int primary key auto_increment,name char (TEN), host char (15), #主机ipport int, #端口constraint host_port unique (host,port) #constraint Host_port This is only used to set the name of a unique constraint, or it can be set without default); insert into server (Name,host,port) VALUES (' ftp ', ' 192.168.20.11 ', 8080); INSERT into server (Name,host,port) VALUES (' https ', ' 192.168.20.11 ', 8081); #ip和端口合起来唯一select * from server;

Iv. primary KEY (PRIMARY KEY constraint)

The value of the primary key field is not null and unique

In a table you can:

Single-row key
Multi-column Master key (composite primary key)

But there can only be one primary key in a table primary key

============ single-row key ===============
#方法一: Not Null+unique
CREATE TABLE Department1 (
ID int not NULL unique, #主键
Name varchar () is not null unique,
Comment varchar (100)
);

mysql> desc department1;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| ID | Int (11) | NO | PRI | NULL | |
| name | varchar (20) | NO | UNI | NULL | |
| Comment | varchar (100) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
Rows in Set (0.01 sec)

#方法二: Use primary key after a field
CREATE TABLE Department2 (
ID int primary KEY, #主键
Name varchar (20),
Comment varchar (100)
);

mysql> desc Department2;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| ID | Int (11) | NO | PRI | NULL | |
| name | varchar (20) | YES | | NULL | |
| Comment | varchar (100) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
Rows in Set (0.00 sec)

#方法三: Primary key is defined separately after all fields
CREATE TABLE Department3 (
ID int,
Name varchar (20),
Comment varchar (100),
Constraint Pk_name primary key (ID); #创建主键并为其命名pk_name

mysql> desc Department3;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| ID | Int (11) | NO | PRI | NULL | |
| name | varchar (20) | YES | | NULL | |
| Comment | varchar (100) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
Rows in Set (0.01 sec)

Single-Column primary key

 1 ============ single row key =============== 2 #方法一: not null+unique 3 CREATE TABLE DEPARTMENT1 (4 ID int. NOT NULL unique, #主键 5 nam e varchar (c) NOT null unique, 6 comment varchar (100) 7); 8 9 mysql> desc department1;10 +---------+--------------+------+-----+---------+-------+11 | Field | Type | Null | Key | Default | Extra |12 +---------+--------------+------+-----+---------+-------+13 | ID | Int (11) | NO | PRI |       NULL | |14 | name | varchar (20) | NO | UNI |       NULL | |15 | Comment | varchar (100) |     YES | |       NULL | |16 +---------+--------------+------+-----+---------+-------+17 rows in Set (0.01 sec) #方法二: Use primary after a field key20 CREATE TABLE Department2 (ID int primary KEY, #主键22 name varchar (), comment varchar); mysql> desc de partment2;27 +---------+--------------+------+-----+---------+-------+28 | Field | Type | Null | Key | Default | Extra |29 +---------+--------------+------+-----+---------+-------+30 | ID | Int (11) | NO | PRI |       NULL | |31 | name | varchar (20) |     YES | |       NULL | |32 | Comment | varchar (100) |     YES | |       NULL | |33 +---------+--------------+------+-----+---------+-------+34 rows in Set (0.00 sec) #方法三: defined separately after all fields primary KEY37 CREATE TABLE Department3 (int,39 name varchar), comment varchar (+), constraint Pk_name primary key (ID) ; #创建主键并为其命名pk_name42 mysql> desc department3;44 +---------+--------------+------+-----+---------+-------+45 | Field | Type | Null | Key | Default | Extra |46 +---------+--------------+------+-----+---------+-------+47 | ID | Int (11) | NO | PRI |       NULL | |48 | name | varchar (20) |     YES | |       NULL | |49 | Comment | varchar (100) |     YES | |       NULL | |50 +---------+--------------+------+-----+---------+-------+51 rows in Set (0.01 sec) 52 53 single-column primary key

================== multi-row key ================
CREATE TABLE service (
IP varchar (15),
Port char (5),
Service_Name varchar (TEN) is not NULL,
Primary KEY (Ip,port)
);

mysql> desc Service;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| IP | varchar (15) | NO | PRI | NULL | |
| Port | CHAR (5) | NO | PRI | NULL | |
| service_name | varchar (10) | NO | | NULL | |
+--------------+-------------+------+-----+---------+-------+
Rows in Set (0.00 sec)

mysql> INSERT INTO service values
(' 172.16.45.10 ', ' 3306 ', ' mysqld '),
(' 172.16.45.11 ', ' 3306 ', ' mariadb ')
;
Query OK, 2 rows Affected (0.00 sec)
Records:2 duplicates:0 warnings:0

mysql> INSERT into service values (' 172.16.45.10 ', ' 3306 ', ' nginx ');
ERROR 1062 (23000): Duplicate entry ' 172.16.45.10-3306 ' for key ' PRIMARY '

Multi-Column Primary key

 1 ================== Multi-row key ================ 2 CREATE TABLE service (3 IP varchar (), 4 port char (5), 5 service_name Varcha R (Ten) not NULL, 6 primary KEY (Ip,port) 7); 8 9 mysql> desc service;11 +--------------+-------------+------+-----+---------+-------+12 | Field | Type | Null | Key | Default | Extra |13 +--------------+-------------+------+-----+---------+-------+14 | IP | varchar (15) | NO | PRI |       NULL | |15 | Port | CHAR (5) | NO | PRI |       NULL | |16 | service_name | varchar (10) |     NO | |       NULL | |17 +--------------+-------------+------+-----+---------+-------+18 rows in Set (0.00 sec) mysql> INSERT into Ser Vice Values21 (' 172.16.45.10 ', ' 3306 ', ' mysqld '), (' 172.16.45.11 ', ' 3306 ', ' mariadb '), Qu Ery OK, 2 rows Affected (0.00 sec) Records:2 duplicates:0 warnings:026 mysql> INSERT INTO service values (' 17 2.16.45.10 ', ' 3306 ', ' nginx '); ERROR 1062 (23000): DUPLIcate entry ' 172.16.45.10-3306 ' for key ' PRIMARY ' 

V. auto_increment (self-increasing constraint)

Step increment and start offset offset:auto_increment_increment,auto_increment_offset

3.--------Offset: auto_increment_offset---------============== CREATE TABLE dep (ID int primary KEY auto_ when no offset is set) Increment,name char); INSERT into DEP (name) VALUES (' IT '), (' HR '), (' EFO '); select * FROM dep;================ Set the self-increment to start with 10 CREATE TABLE DEP1 (ID int primary key auto_increment,name char) auto_increment = 10;insert into dep1 (name) v Alues (' IT '), (' HR '), (' EFO '); select * from dep1;===============auto_increment_increment: Increment step CREATE TABLE dep3 (ID int Primary key Auto_increment, name char (10)); session: Connecting to the server via the client (once a link is called a session) Set session auto_increment_increment = 2; #会话级, set global auto_increment_increment=2 is valid only for the current session; #全局, all sessions are valid INSERT into DEP3 (' IT '), (' HR '), (' SALE '), (' Boss '),-----------view variable----------show variables Like '%auto_in% '; #查看变量. As long as it contains auto_in. =========auto_increment_offset: Offset +auto_increment_increment: Step =========== Note: if Auto_increment_ The value of offset is greater than the value of auto_increment_increment, then the value of Auto_increment_offset is ignored set session Auto_increment_offset=2;set session Auto_increment_incremenT=3;show variables like '%auto_in% '; CREATE table DEP4 (ID int primary key auto_increment,name char), insert into DEP4 (NA Me) VALUES (' IT '), (' HR '), (' SALE '), (' Boss ');

Vi. foreign KEY (FOREIGN KEY constraint)

The Employee Information table has three fields: work number name Department

The company has 3 departments, but there are 100 million of employees, that means the department this field needs to be repeated storage, the longer the department name, the more wasted

Workaround:

We can definitely define a departmental table.

The Employee Information table is then associated with the table, and how it is associated, that is, foreign key

As a simple representation of the relationship between the employee table and the department table, that is, the employee table (DEP_ID) to associate the ID field of the Department table

Many-to-one (one field of a table multiple records is associated with only one field of another table): The employee has the department, the department has a lot of information, so separate a department table, the ID of the department table and the dep_id of the employee table are related. (Dep_id the ID field of the Department table to associate (Note:1. First build the associated table,        2. The associated field must be unique        3. Insert a record into the associated table first) First build a departmental table (associated table) CREATE TABLE DEP (ID int not null unique, #id int primary key auto_increment,name varchar (10), Comment varchar 0)); Build the Employee Table (association table) CREATE TABLE emp_info (ID int primary KEY auto_increment,name varchar), dep_id int,constraint fk_depid_ ID foreign KEY (dep_id) references dep (ID) #references: Association on DELETE Cascade  #关联的表删了, associated table also deleted on UPDATE Cascade  # The associated tables have been modified, the associated tables have also been modified); #先给被关联的表初始化记录insert into DEP values (1, ' The great man Fraternity Technology Limited division ', ' Good ... '), (2, ' Alex Human Resources ', ' can't recruit People '), (3, ' Sales department ', ' Can't sell anything '); INSERT into emp_info values (1, ' Egon ', 1), (2, ' alex1 ', 2), (3, ' Alex2 ', 2), (4, ' alex3 ', 2), (5, ' Lee Tank ', 3), (6, ' Liu ', 3 ), (7, ' rockets ', 3), (8, ' Forest Bullets ', 3), (9, ' Gatling ', 3); #修改update dep set ID =301 WHERE id = 2;select * from Dep;delect * from Em_info; Scattered, the staff will go, is the department table is gone, the staff table is not.

Running results such as:

View the Created table

Modify id=301

View associated tables and associated tables

MySQL Database learning "Fifth" integrity constraints