Integrity constraints for MySQL database field parameters

I. Classification

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 identifies the field cannot be empty unique key (UK) that identifies the field The value is unique auto_increment the value of the field is automatically growing (the integer type, and the primary key) default for the field is set to unsigned unsigned zerofill using 0 padding#Description1whether NULL is allowed, default NULL, can be set not NULL, field is not allowed to be empty, must be assigned2The 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 value of the Sex enum ('male','female') notNull default'male'Age Int. unsigned not NULL default20must be positive (unsigned) not allowed to null default is3whether it is a key primary key primary key foreign key foreign key index (Index,unique ...)

Two. Not NULL and default use

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

Three. Uniqle

　Two ways to set a unique constraint

============ 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.00sec) MySQL> INSERT INTO DEPARTMENT1 values (1,'IT','Technology'); ERROR1062 (23000): Duplicate entry'IT'  forKey'name'

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-row key ===============#method One: not Null+uniqueCREATE TABLE department1 (ID int notNull unique,#PRIMARY KeyName varchar (20) notnull 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 | |+---------+--------------+------+-----+---------+-------+rowsinchSet (0.01sec)#method Two: Use primary key after a certain fieldCREATE TABLE Department2 (ID int primary KEY,#PRIMARY KeyName 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 | |+---------+--------------+------+-----+---------+-------+rowsinchSet (0.00sec)#method Three: Define primary key separately after all fieldsCREATE TABLE Department3 (ID int,name varchar (20), Comment varchar (100), constraint Pk_name primary key (ID); #Create a primary key and name it Pk_nameMySQL>desc Department3;+---------+--------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+---------+--------------+------+-----+---------+-------+| ID | Int (11) | NO | PRI |       NULL | || name | varchar (20) |     YES | |       NULL | || Comment | varchar (100) |     YES | |       NULL | |+---------+--------------+------+-----+---------+-------+rowsinchSet (0.01sec)

================== multi-row key ================CREATE TABLE service (IP varchar (15), Port char (5), service_name varchar (10) notnull,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 | |+--------------+-------------+------+-----+---------+-------+rowsinchSet (0.00sec) MySQL>INSERT into service values('172.16.45.10','3306','mysqld'),    ('172.16.45.11','3306','mariadb')    - ; Query OK,2 Rows Affected (0.00sec) Records:2duplicates:0 Warnings:0mysql> INSERT into service values ('172.16.45.10','3306','Nginx'); ERROR1062 (23000): Duplicate entry'172.16.45.10-3306'  forKey'PRIMARY'

Integrity constraints for MySQL database field parameters