One . Introduce

Constraints are the same width as data types , All optional parameters

effect : Used to ensure the integrity and consistency of data
It is mainly divided into :

PRIMARY KEY Mark this field as the primary key of the table , You can uniquely identify records 
FOREIGN KEY Mark the field as the foreign key of the table
NOT NULL Indicates that the field cannot be empty
UNIQUE KEY The value indicating this field is unique
AUTO_INCREMENT Indicates that the field is a self growing type ( Data type is integer and primary key )
DEFAULT Set the default value for the corresponding field UNSIGNED Unsigned
ZEROFILL Use 0 fill

Use method and instruction

# Create a class , Class id It's primary key and self growing , Class name cannot be empty
create table class(
id int(4) primary key auto_increment ,
name char(20) not null
);
insert into class (name) values(" Class two, grade three "); # Insert a record
# Create a student table ,id Primary key , Self growth , Name cannot be empty , Gender default male , Class number cannot be empty , The foreign key of class number is class In the table id, Fractions are of the unsigned type .(0-255) create table student(
id int primary key auto_increment,
name char(6) not null,
sex enum(" male "," Woman ") default " male ",
class_id int(6) not null,
foreign key(class_id) references class(id),
score int(3) unsigned
); insert into student (name,sex,class_id,score)values("jack"," male ",1,75);
Foreign key Syntax :
foreign key( Fields to reference foreign keys ) references The name of the referenced table ( Referenced table fields );
Be careful :
If a field is not set not null, When inserting data , If you don't pass a value to this field , The default value is NULL, If set NOT NULL, The field cannot be empty , Otherwise, the report will be wrong

Two . not null and default

Could you empty ,null Said empty , The string
not null - Do not empty
null - Can be empty

default The default value is , You can specify default values when creating a column , If not actively set when inserting data , The default value is automatically added

create table tb1(id int not null defalut  2 ,num int not null)

3、 ... and . unique Uniqueness constraint

It's unique , Set the uniqueness of a field .

-----1. It's unique ---------
create table t2(
id int not null unique,
name char(10)
);
insert into t2 values(1,'jack');
insert into t2 values(1,'lucy');
# When creating the table above, put id Set a unique constraint . Then insert id=1, It's going to go wrong

Joint only , Setting multiple fields together is unique .

create table t20(id int not null unique auto_increment,host char(15),port int,unique(host,port)); # Set up host and port Field union is unique
insert into t23(host,port) values("172.16.0.253",1990);
insert into t23(host,port) values("172.16.0.253",1991);
insert into t23(host,port) values("172.16.0.252",1990); # 2 The fields together are unique .

Four . primary key( Primary key constraint )

primary key The value of the field is not empty and unique , Single column primary key and composite primary key can be made in a table ( Multi column primary key ), But a table can only have one primary key .

 create table t25(id int primary key auto_increment,name char(6),age int);
insert into t25(name,age) values("jack",18);
select * from t25;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | jack | 18 |
+----+------+------+ #id As the primary key of self growth

Single column primary key

 create table t24(
name char(10) not null,
host char(15),
port int,
primary key(host,port)
);
insert into t24 values("mysql","172.168.0.253",3306);
insert into t24 values("ftp","172.168.0.253",21);
insert into t24 values("mysql","172.168.0.253",3306); # Inserting the same will report an error
# Usage and unique It's the only union that's about the same

Multi column primary key

5、 ... and . auto_increment( Self growth constraints )

auto_increment Yes 2 Attributes :

1.auto_increment_increment  step

2.auto_increment_offset Starting offset

--------------------- Step size and start offset are not set ------------------------------
create table t26(id int primary key auto_increment,name char(6));
insert into t26(name) values("IT"),("HR"),("SALER");
SELECT * FROM T26;
+----+-------+
| id | name |
+----+-------+
| 1 | IT |
| 2 | HR |
| 3 | SALER |
+----+-------+ -------------------------- Set the starting step size to 100---------------------------------------
create table t27(id int primary key auto_increment,name char(6))auto_increment=100;
insert into t27(name) values("IT"),("HR"),("SALER");
SELECT * FROM T27;
+-----+-------+
| id | name |
+-----+-------+
| 100 | IT |
| 101 | HR |
| 102 | SALER |
+-----+-------+
------------------------------ Set the step size and start offset ------------------------------------
set session auto_increment_increment =1000;
Still 27 This table inserts a new value
insert into t27 (name) values("HHH"); #HHH The number of is already 1001 了
select * from t27;
+------+-------+
| id | name |
+------+-------+
| 100 | IT |
| 101 | HR |
| 102 | SALER |
| 1001 | HHH |
+------+-------+ # Set offset
set session auto_increment_offset =2;
insert into t27(name) values ("RRR"),("PPP"),("QQQ");
select * from t27;
+------+-------+
| id | name |
+------+-------+
| 100 | IT |
| 101 | HR |
| 102 | SALER |
| 1001 | HHH |
| 2002 | RRR |
| 3002 | PPP |
| 4002 | QQQ |
+------+-------+ # For each value added , The step size will increase 1000, The offset is 2

Be careful :offset The value of cannot be greater than increment Value , Otherwise it will not work .

6、 ... and . Foreign key constraints (foreign key)

There are three fields in the employee table : full name , Gender , department . Now the company has employees 20000 people ,3 Departments . This means there will be a lot of duplicate data for department names . You can use foreign keys to solve this problem . First, set up a department table . Then create the employee list , Put the Department in the employee list ID Set to foreign key , From the Department in the Department table ID that will do .

Start building tables , Be careful : Be sure to create the associated foreign key table first ( Departmental table ). In establishment ( The employee table ), The associated field must be unique , To insert a record into the associated table first .

 create table dep(id int primary key auto_increment,name char(15));
insert into dep(name) values("HR"),("IT"),("SALE");
mysql> SELECT * FROM DEP;
+----+------+
| id | name |
+----+------+
| 1 | HR |
| 2 | IT |
| 3 | SALE |
+----+------+
create table emp(id int primary key auto_increment,name char(6) not null,
sex enum("male","female"),depart_id int); insert into emp(name,sex,depart_id) values
("jack","male",2),
("lucy","female",1),
("lili","female",3),
("hanmeimei","female",1),
("lilei","male",2),
("erya","female",3); select * from emp;
+----+--------+--------+-----------+
| id | name | sex | depart_id |
+----+--------+--------+-----------+
| 1 | jack | male | 2 |
| 2 | lucy | female | 1 |
| 3 | lili | female | 3 |
| 4 | hanmei | female | 1 |
| 5 | lilei | male | 2 |
| 6 | erya | female | 3 |
+----+--------+--------+-----------+ show create table emp\G;
*************************** 1. row ***************************
Table: emp
Create Table: CREATE TABLE `emp` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` char(6) NOT NULL,
`sex` enum('male','female') DEFAULT NULL,
`depart_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `depart_id` (`depart_id`),
CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`depart_id`) REFERENCES `dep` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

Foreign key constraints (foreign key

The table created above , There is a disadvantage , If the Department id There is a change , The Department in the employee list id It's not going to change . If the Department disbands . Employee information will not be deleted .

 drop table emp;
create table emp(id int primary key auto_increment,
name char(6) not null,
sex enum("male","female"),
depart_id int,foreign key (depart_id) references dep(id)
on delete cascade
on update cascade); # Add these two sentences , In the Department ID When something changes , The Department in the employee list id I'll go along with the defense
update table dep id=101 where id=1; # Revision Department 1 Of id
select * from emp; +----+--------+--------+-----------+
| id | name | sex | depart_id |
+----+--------+--------+-----------+
| 1 | jack | male | 2 |
| 2 | lucy | female | 101 |
| 3 | lili | female | 3 |
| 4 | hanmei | female | 101 |
| 5 | lilei | male | 2 |
| 6 | erya | female | 3 |
+----+--------+--------+-----------+

Foreign key constraint optimization version

day 7-14 More articles on database integrity constraints

  1. Python Standard library 14 database (sqlite3)

    author :Vamei Source :http://www.cnblogs.com/vamei Welcome to reprint , Please also retain this statement . thank you ! Python Bring a lightweight relational database SQLite. This database uses SQL Language .S ...

  2. 【 Learn step by step Python】14. Database support

    Plain text can only achieve some simple and limited functions . If you want to achieve automatic serialization , You can also use shelve Module and pickle Module to achieve . however , If you want to automatically achieve concurrent data access , And more standard , A more general database (databas ...

  3. MySQL database ---- Integrity constraints

    One . Introduce Constraints are the same width as data types , All optional parameters effect : Used to ensure the integrity and consistency of data is mainly divided into : PRIMARY KEY (PK) Identify the field as the primary key of the table , Can be a unique identification record FOREIGN KEY ...

  4. 08-Mysql database ---- Integrity constraints

    summary :      1,not null Can't insert empty , Do not set nullable       2,unique  It's unique create table department(name char(10) unique); ...

  5. Database and SQL---- Summary of common knowledge points

    Database is also an inevitable test site in computer written interview , Especially banks and some traditional software companies . Here, according to the data , Make a summary of the related knowledge of database . I hope the students who have studied database but haven't used it for a long time can recall and retrieve it , Never learned. ...

  6. asp.net core Use EF7 Code First Create database , At the same time, use the command to create the database

    1. First download vs2015 Of Asp.Net Core(RC2) Plug in tools for (https://www.microsoft.com/net/core#windows)2. Create a asp.net Core Project ...

  7. MySql Basic principles and instructions of database

    1. What is a database A database is a warehouse for storing data , Its essence is a file system , Data stores data in a specific format , The user can go through SQL Add data to the database , modify , Delete and query operations . 2. brief introduction MySQL It's an open source ...

  8. Python Database of classic interview questions

    Database and cache 1. List the common relational databases and non relational databases ? Relational database ( You need to have a table structure ) mysql.oracle.splserver.postgresql.db2.sybase Non relational database ( yes ...

  9. ORACLE Responsibilities of Database Administrator

    ORACLE Responsibilities of Database Administrator One . summary ORACLE The database administrator should do the following to ORACLE The database system does regular monitoring : (1). Every day to ORACLE The running status of the database , Log files , Backup situation , The spatial usage of database ...

Random recommendation

  1. IIS7 The application pool is set to classic v2.0

    HTTP error 500.21 - Internal Server Error  The handler “PageHandlerFactory-Integrated” There is an error module... In its module list “ManagedPipe ...

  2. Lost Cows( Line segment tree POJ2182)

    Lost Cows Time Limit: 1000MS Memory Limit: 65536K Total Submissions: 10354 Accepted: 6631 Descriptio ...

  3. OC Basics -- Construction method

    OC There are two ways to construct classes in languages : One . Method 1 :[ Class name new] example :[Person new] shortcoming : Poor scalability , If be in, Person There are _age Member variables , When initializing, you want _age The value of 20,ne ...

  4. utilize Apply Array of parameters to improve the elegance of the code , And efficiency

    utilize Apply Array of parameters to improve the elegance of the code , And efficiency Function.apply() Skills in improving program performance Let's start with Math.max() Function start ,Math.max It can be followed by any parameter , Finally, return to ...

  5. jetbrains

    In the latest jetbrains Released IntelliJ IDEA 15. PyCharm 5.PhpStorm10.WebStorm 11 Wait for each version , But changed the registration method . The original registration code, including the registration machine, can no longer be used ...

  6. Springmvc+Spring+Hibernate Construction method

    Springmvc+Spring+Hibernate Construction method and example The first two articles , Respectively introduced Springmvc and Spring How to build it , In this paper, we build hibernate, And establish SSH The most basic code ...

  7. “&” Detailed explanation

    1. quote Reference is a variable ( The goal is ) An alias for , The operation on the reference is exactly the same as the direct operation on the variable . & As a reference , Must be initialized at definition time , If it is not initialized, it will compile and report an error . 2. Address fetch & As land acquisition ...

  8. Use Microsoft.ExceptionMessageBox.dll Capture WinForm Program exception information and pop-up display

    WinForm Program development , In the development mode, the exception handling is generally through debugging to find the unknown and cause of the exception . Let's say “ Divisor is 0” In this paper, we will give a detailed description of . Button The button events are as follows : private void bu ...

  9. Android TCP/IP Literacy course

    TCP/IP It's the communication protocol of the Internet . A communication protocol is a description of the rules that a computer must obey . Just following these rules . Computers can communicate with each other . The browser and server All in use TCP/IP Internet browsers and the Internet serve ...

  10. Do you want to use it gzip Optimize front end projects

    I'm doing project optimization these two days , be aware webpack There is one compression-webpack-plugin plug-in unit , It can be packed into gzip Format to the server , I learned that GZIP, Actually GZIP There are many points , We're just going to talk about the front end ...