Outside of MySQL key

Integrity constraints

concept: constraints are the same as the width of the data type, which are optional parameters

function: used to ensure the integrity and accuracy of data between tables and tables

Can be divided into the following types:

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

Unique KEY (UK) identifies the value of the field as distinct

Not NULL identifies the field cannot be empty

Auto_increment identifies the value of this field automatically grows (integer type, and primary key)

Default defaults for this field

PRIMARY KEY

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

1 single-row key

Method One: Not null+unique    CREATE TABLE department1 (                ID int. NOT NULL unique,    #主键                name varchar (a) NOT null Uniqu E,                 comment varchar (+)                );    

Method Two: Use primary key after a certain field  (recommended)
    CREATE TABLE Department2 (
                ID int primary KEY,         #主键
                Name varchar (20),
　　　　　　　　   Comment varchar (100)
);

Method Three: Define primary key separately after all fields
CREATE TABLE Department3 (
ID int,
Name varchar (20),
Comment varchar (100),
Primary KEY (ID)
# Constraint Pk_name primary key (ID) #创建主键并为其命名pk_name
);

2 Multi-column master key (composite primary key)

CREATE TABLE service (IP varchar (), Port char (5), service_name varchar (TEN) NOT NULL, Primary KEY (Ip,port));

FOREIGN KEY

Use conditions for foreign keys:

1. Two table types must be InnoDB tables, the MyISAM table temporarily does not support foreign keys (it is said that later versions may be supported, but at least not currently supported);

2. The foreign key column must be indexed, MySQL 4.1.2 later version will automatically create the index when the foreign key is established, but if the earlier version needs to display the establishment;

3. The columns of the two tables of the foreign-key relationship must be of similar data types, i.e., columns that can be converted to each other, such as int and tinyint, and int and char are not allowed;

Basic operations

1 Creating Appearances:

The first method: Establish a FOREIGN key CREATE TABLE table_name (' field ' type,    ' field ' type,     ...  ...    


REFERENCES Main Table (field);

2 deleting foreign keys

ALTER table name DROP FOREIGN key FOREIGN key name

Table-to-table correspondence relationship

With foreign keys, we can quickly establish a relationship between tables

• Many to one: Left table multiple records corresponding to the right table one record
• One-to-many: Left table a record corresponding to the right table multiple records
• One: Left table a record corresponding to the right table one record
• Many-to-many: Left table multiple records corresponding to the right table more than one record

Example: Publishing house, author information, book publishing house publishing multiple books (one-to-many) an author write a book (one to one) multiple publishers publish multiple books of the same author (many to many)

Many-to-one:

# First, the associated table should be created
Main Table    CREATE TABLE press (ID int primary key auto_increment, name varchar (20)); CREATE table book from table (id int primary k EY auto_increment, name varchar, press_id int NOT NULL, foreign key (press_id) references press (ID) on delete casca De  
ON UPDATE cascade
);
....

Many-to-many:

Create TABLE author (    ID int primary key auto_increment,    name varchar)    ; # Creates a new table to hold the relationship between the author table and the Book table create table Author2book (    ID int not null unique auto_increment,    author_id int not NULL,    BOOK_ID int. NOT NULL,    Constra int Fk_author foreign KEY (author_id) references author (id)  #创建外键并命名为fk_author on    delete Cascade    on UPDATE cascade,    constraint Fk_book foreign key (book_id) references book (ID) on the    delete cascade on    update Cascade,    Primary KEY (author_id,book_id)    );   

One:

CREATE TABLE customer (    ID int primary key auto_increment,    name varchar () NOT NULL,    QQ varchar (TEN) not null,< C3/>phone char (+) not null    ); CREATE TABLE student (    ID int primary key auto_increment,    class_name varchar (20 ) not NULL,    customer_id int unique,         #该字段一定要是唯一的    foreign Key (customer_id) references customer (ID) c10/> #外键的字段一定要保证unique on    delete cascade on    update Cascade    );    

UNIQUE KEY　

Set a UNIQUE constraint

Method One: After a certain field with the unique    create TABLE department1 (                ID int,                name varchar () unique,                comment varchar (100)                );     Method Two: Define the unique    CREATE TABLE department2 (                ID int,                name varchar (),                comment varchar (100) Separately after all the fields,                constraint uk_name Unique (name)                );   
For example:
mysql> INSERT INTO department1 values (1, ' IT ', ' technology ');
Query OK, 1 row Affected (0.00 sec)
mysql> INSERT INTO department1 values (1, ' IT ', ' technology ');
ERROR 1062 (23000): Duplicate entry ' IT ' for key ' name '

Union unique:

CREATE TABLE service (ID int primary key auto_increment,name varchar (), host varchar () not null,port int not null,unique (host,port) #联合唯一);mysql> INSERT into service values    --(1, ' nginx ', ' 192.168.0.10 ', '),    (2, ' Haproxy ', ' 192.168.0.20 ',----    (3, ' MySQL ', ' 192.168.0.30 ', 3306)    ; Query OK, 3 rows affected (0.01 sec) records:3  duplicates:0  warnings:0mysql> INSERT INTO service (NAME,HOST,PO RT) VALUES (' Nginx ', ' 192.168.0.10 ', 80); ERROR 1062 (23000): Duplicate entry ' 192.168.0.10-80 ' for key ' host '

Not NULL

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

Mysql> CREATE TABLE t2 (ID int not NULL); #设置字段id不为空mysql > Desc t2;+-------+---------+------+-----+---------+-------+| Field | Type    | Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+|  ID   | int (11) | NO   |     | NULL    |       | +-------+---------+------+-----+---------+-------+

Auto_increment　　

Constraint fields are autogrow, constrained fields must be constrained by key at the same time

1 Creating a self-increment field

CREATE TABLE student (        ID int primary key auto_increment,        name varchar,        sex enum (' Male ', ' female ') Default ' male '        );
# When the ID is not specified, it grows according to the self-increment ID, and when the ID is specified, it grows according to the specified ID.

2 delete a self-increment field

Truncate student;        # Please empty table
Note: For the self-increment field, after using delete, after inserting the value, the field continues to grow as it was before the deletion

3 Setting the Self-increment field

Set the starting value of the  auto_increment field
mysql> ALTER TABLE student auto_increment=3;mysql> show create table student;. Engine=innodb auto_increment=3 DEFAULT Charset=utf8

Set Step auto_increment_increment
#基于会话级别
Set Session auth_increment_increment=2 #修改会话级别的步长
#基于全局级别的
Set global auth_increment_increment=2 #修改全局级别的步长 (all sessions are in effect)
Starting value Auto_increment_offset
Attention:
If the value of Auto_increment_offset is greater than the value of auto_increment_increment, the value of Auto_increment_offset is ignored

DEFAULT

Default value, you can specify a default value when creating a column, and automatically add a default value when inserting data if it is not actively set

#设置id字段有默认值后, no matter whether the ID field is NULL or NOT NULL, you can insert NULL, insert NULL defaults to the default value specified by default
mysql> CREATE TABLE t3 (id int default 1);
mysql> ALTER TABLE t3 modify ID int not NULL default 1;

Outside of MySQL key