MySQL integrity 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

11whether NULL is allowed, default NULL, can be set not NULL, field is not allowed to be empty, must be assigned22. The default value for the field is null, and if the record is inserted without assigning a value to the field, this field uses the default value3Sex enum ('male','female') notNull default'male'4Age Int. unsigned not NULL default 20must be positive (unsigned) not allowed to null default is53. is the key6 primary key Primary key7 foreign key foreign key8Index (Index,unique ...)

Introduction2, NOT NULL and default

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

================== notnull====================MySQL> CREATE table t1 (id int);#The ID field can be inserted by default in an emptyMysql>desc T1;+-------+---------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| ID | Int (11) |     YES | |       NULL | |+-------+---------+------+-----+---------+-------+MySQL> INSERT into T1 values ();#You can insert an emptyMySQL> CREATE TABLE t2 (ID int notNULL);#set field ID is not emptyMysql>desc T2;+-------+---------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| ID | Int (11) |     NO | |       NULL | |+-------+---------+------+-----+---------+-------+MySQL> INSERT into t2 values ();#cannot insert emptyERROR 1364 (HY000): Field'ID'doesn'T has a default value==================default====================#after setting the ID field with a default value, you can insert null if the ID field is NULL or NOT NULL, insert null defaults to fill default values specified by defaultmysql> CREATE TABLE t3 (ID int default 1); MySQL> ALTER TABLE t3 modify ID int notNull default 1;================== Comprehensive Practice ====================MySQL>CREATE TABLE Student (-Name varchar (20) notNULL,-Age int (3) unsigned notNull default 18,    -Sex enum ('male','female') Default'male',    -Hobby Set ('Play','study','Read','Music') Default'Play,music'-); MySQL>desc student;+-------+------------------------------------+------+-----+------------+-------+| Field | Type | Null | Key | Default | Extra |+-------+------------------------------------+------+-----+------------+-------+| name | varchar (20) |     NO | |       NULL | || Age | Int (3) unsigned |     NO | |       18 | || sex | Enum'male','female')              |     YES | |       Male | || Hobby | Set'Play','study','Read','Music') |     YES | |       Play,music | |+-------+------------------------------------+------+-----+------------+-------+MySQL> INSERT into Student (name) VALUES ('Egon'); MySQL> select * fromstudent;+------+-----+------+------------+| name | Age | sex | Hobby |+------+-----+------+------------+|  Egon | 18 | Male | Play,music |+------+-----+------+------------+Validation

View Code3. Unique

Role: Limit the value of a field to unique

# single-column unique CREATE TABLE t1 (id int nnique,name char ()); # Federated unique CREATE TABLE server (ID int nuique,ip char (), Port Int,unique (Ip,port));

4. Primary key

Primary key is viewed from a constrained point of view, which is equivalent to NOT NULL unique

Stressed

1, a table must have, and only one primary key
2, a table should have an ID field, and the ID field should be made into a primary key

1============ single-row key ===============2 #method One: not Null+unique3 CREATE TABLE Department1 (4ID int notNull unique,#PRIMARY Key5Name varchar (20) notnull unique,6Comment varchar (100)7 );8 9Mysql>desc department1;Ten+---------+--------------+------+-----+---------+-------+ One| Field | Type | Null | Key | Default | Extra | A+---------+--------------+------+-----+---------+-------+ -| ID | Int (11) | NO | PRI |       NULL | | -| name | varchar (20) | NO | UNI |       NULL | | the| Comment | varchar (100) |     YES | |       NULL | | -+---------+--------------+------+-----+---------+-------+ -RowsinchSet (0.01sec) -  + #method Two: Use primary key after a certain field - CREATE TABLE Department2 ( +ID int PRIMARY KEY,#PRIMARY Key AName varchar (20), atComment varchar (100) - ); -  -Mysql>desc Department2; -+---------+--------------+------+-----+---------+-------+ -| Field | Type | Null | Key | Default | Extra | in+---------+--------------+------+-----+---------+-------+ -| ID | Int (11) | NO | PRI |       NULL | | to| name | varchar (20) |     YES | |       NULL | | +| Comment | varchar (100) |     YES | |       NULL | | -+---------+--------------+------+-----+---------+-------+ theRowsinchSet (0.00sec) *  $ #method Three: Define primary key separately after all fieldsPanax Notoginseng CREATE TABLE Department3 ( - ID int, theName varchar (20), +Comment varchar (100), AConstraint Pk_name primary key (ID);#Create a primary key and name it Pk_name the  +Mysql>desc Department3; -+---------+--------------+------+-----+---------+-------+ $| Field | Type | Null | Key | Default | Extra | $+---------+--------------+------+-----+---------+-------+ -| ID | Int (11) | NO | PRI |       NULL | | -| name | varchar (20) |     YES | |       NULL | | the| Comment | varchar (100) |     YES | |       NULL | | -+---------+--------------+------+-----+---------+-------+WuyiRowsinchSet (0.01sec) the  -Single-Column primary key

single-column primary key

CREATE TABLE t19 (    IP char (+),    port int,    primary key (Ip,port));

Federated Primary Key5, Auto_increment

The bundle field is autogrow, and the constrained field must be constrained by key at the same time

CREATE TABLE T20 (    ID int primary key auto_increment,    name char (+)) engine=InnoDB; # auto_increment Note:1, usually with primary key, and usually to the ID field plus 2, auto_incremnt can only be defined as a key (unique key, Primary key) field plus

MySQL integrity constraints