mysql-Table Completion constraints

An 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 vs. default

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

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
CREATE TABLE TB1 (
Nid int not null Defalut 2,
num int NOT NULL
)

Validation

Three unique

============ setting a UNIQUE constraint unique===============Method One: Create TABLE department1 (ID int,name varchar (20) unique,comment varchar (100); method Two: Create TABLE Department2 (ID int,name varchar (20), Comment varchar (100), constraint uk_name unique (name));mysql> insert into department1 values (1,'IT',' technology '); Query OK, 1 row Affected (0.00 sec) mysql> insert into department1 values (1,'IT',' technology ' c14>); ERROR 1062 (23000): Duplicate entry 'IT' for key 'name'    

chemical reactions of not null+unique Union Unique

Four primary key

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-column primary keymulti-column primary key

Five Auto_increment

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

View Codestep increment and start offset offset:auto_increment_increment,auto_increment_offset

Six foreign key

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

Demonstration

Auxiliary Understanding

#一对多或称为多对一三张表: Publishing house, author information, book one-to-many (or many to one): A publishing house can publish multiple books related ways: foreign key

View Code

Polygamy # wife Table of husband ID foreign key to husband table ID 

#多对多三张表: Publishing house, author information, book many-to-many: an author can write more than one book, a book can also have multiple authors, two-way to many, that is, many-to-many associations: foreign key+ a new table

View Code

Single sheet: User table + dating relationship table, equivalent to: User table + Blind Date Relationship table + user table multiple tables: User table + user and host relationship table + host table in the middle of the table that holds the relationship, the field can be associated with a single 

#一对一两张表: Student table and Customer table one-to: A student is a client, a client may become a school, that is, one-to-one relationship: foreign key+unique

View Code

Example one: A user has only one blog    user table:    ID  name   Egon   Alex   Wupeiqi    Blog table              fk+unique    ID URL name_id xxxx   1 yyyy   3 zzz    2 case two: One administrator only corresponds to a user    user table:    ID user  password Egon    xxxx Alex    yyyy    Administrator table:       fk+Unique ID user_id password 1 xxxxx 2 yyyyy   

Seven Exercises

Exercise 1: Account information table, user group, host table, main unit

View Code

Exercise 2:

mysql-Table Completion constraints