mysql integrity constraints

1.1 integrity constraints supported by MySQL

1.2 primary key constraints

Features: unique and non empty
A primary key can consist of one field or multiple fields (composite primary key)
If a primary key can consist of one field, it can be added to either the column level or the table level
If it consists of multiple fields, it can only be added to the table level
Purpose: to ensure that each line is not repeated

1.2.1 create a new table and add a primary key

(1) Add constraints at the column level of the field

create table Table name( Field name field type primary key )

For example, create the student1 table (id name age sex), where id is the primary key

create table student1 ( id int unsigned not null auto_increment primary key, age tinyint unsigned, sex enum('male','female') default 'male );

(2) Adding primary key constraints at the table level

create table Table name( Field name 1 field type 1, Field name 2 field type 2, ..... Field name n Field type n, [constraint Primary key constraint name] primary key(Field name) )

For example, create a student2 table (id name age sex), where id is the primary key, and add a primary key constraint at the table level

create table student2 ( id int unsigned not null auto_increment, age tinyint unsigned, sex enum('male','female') default 'male', constraint pk_student2_id primary key(id) ); mysql> desc student2; +-------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | age | tinyint(3) unsigned | YES | | NULL | | | sex | enum('male','female') | YES | | male | | +-------+-----------------------+------+-----+---------+----------------+

(3) Multiple fields as primary keys can only be added to the table level

create table Table name( Field name 1 field type 1, Field name 2 field type 2, ..... Field name n Field type n, [constraint Primary key constraint name] primary key(Field name 1,Field name 2) )

For example, create a student3 table (school id name age sex), where school and id are primary keys, and add primary key constraints at the table level

create table student3 ( id int unsigned not null auto_increment, school varchar(10), name varchar(20), age tinyint unsigned, sex enum('male','female') default 'male', constraint pk_student3_id_school primary key(id,school) ); mysql> desc student3; +--------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-----------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | school | varchar(10) | NO | PRI | NULL | | | name | varchar(20) | YES | | NULL | | | age | tinyint(3) unsigned | YES | | NULL | | | sex | enum('male','female') | YES | | male | | +--------+-----------------------+------+-----+---------+----------------+

1.2.2 adding primary keys to existing tables

Syntax: alter table name add [constraint primary key constraint name] primary key (field name)
For example, create table student4 without id primary key constraint, and add primary key constraint after creation

create table student4 ( id int unsigned not null, school varchar(10), name varchar(20), age tinyint unsigned, sex enum('male','female') default 'male' ); mysql> desc student4; +--------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-----------------------+------+-----+---------+-------+ | id | int(10) unsigned | NO | | NULL | | | school | varchar(10) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | age | tinyint(3) unsigned | YES | | NULL | | | sex | enum('male','female') | YES | | male | | +--------+-----------------------+------+-----+---------+-------+ alter table student4 add constraint pk_student4_id primary key(id) mysql> desc student4; +--------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-----------------------+------+-----+---------+-------+ | id | int(10) unsigned | NO | PRI | NULL | | | school | varchar(10) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | age | tinyint(3) unsigned | YES | | NULL | | | sex | enum('male','female') | YES | | male | | +--------+-----------------------+------+-----+---------+-------+

1.2.3 delete primary key constraint

Syntax: alter table name drop primary key;
For example, delete the primary key constraint in table student4

alter table student4 drop primary key; mysql> desc student4; +--------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-----------------------+------+-----+---------+-------+ | id | int(10) unsigned | NO | | NULL | | | school | varchar(10) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | age | tinyint(3) unsigned | YES | | NULL | | | sex | enum('male','female') | YES | | male | | +--------+-----------------------+------+-----+---------+-------+

1.3 unique constraint

Means: the values of fields in the table cannot be duplicate

1.3.1 adding unique constraints at the column level

Syntax:

create table Table name( Field name 1 field type 1 unique, Field name 2 field type 2, ..... Field name n Field type n)

For example, create the table student5 and add a unique constraint to the name field

create table student5 ( id int unsigned not null primary key auto_increment, school varchar(10), name varchar(20) unique, age tinyint unsigned, sex enum('male','female') default 'male' ); mysql> desc student5; +--------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-----------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | school | varchar(10) | YES | | NULL | | | name | varchar(20) | YES | UNI | NULL | | | age | tinyint(3) unsigned | YES | | NULL | | | sex | enum('male','female') | YES | | male | | +--------+-----------------------+------+-----+---------+----------------+

1.3.2 adding unique constraints at the table level

Syntax:

create table Table name( Field name 1 field type 1, Field name 2 field type 2, ..... Field name n Field type n, [constraint Unique constraint name] unique(Field 1,Field 2...) );

For example, create table student6 and add unique constraints to id and name

create table student6 ( id int unsigned not null primary key auto_increment, school varchar(10), name varchar(20), age tinyint unsigned, sex enum('male','female') default 'male', constraint uk_student6_id_name unique(id,name) );

1.3.3 add unique constraints to existing tables

alter table name add [constraint unique constraint name] unique (field 1, field 2...)

For example, after creating the table student7, add a unique constraint to the name in the table

create table student7 ( id int unsigned not null primary key auto_increment, school varchar(10), name varchar(20), age tinyint unsigned, sex enum('male','female') default 'male' ); alter table student7 add constraint uk_student7_name unique(name);

1.3.4 delete unique constraint

alter table name drop index unique constraint name
be careful:
If a single field does not specify a unique constraint name, the default unique constraint name is the field name
If multiple fields are combined as unique constraints, the default unique constraint name is the name of the first field
If a constraint name is specified, the constraint name will be written when deleting
For example, delete the unique constraint in table student7

alter table student7 drop index uk_student7_name

1.4 not null

The value of a field in a table cannot be empty
be careful:
1. Only column level can be used to add
2. The empty string '' or 0 is not null

1.4.1 adding non empty constraints at the column level

create table Table name( Field name field type not null )

For example, create the student8 table and add a non empty constraint to name

create table student8 ( id int unsigned not null primary key auto_increment, school varchar(10), name varchar(20), age tinyint unsigned, sex enum('male','female') default 'male' );

1.4.2 adding non empty constraints to existing tables

alter table table name modify field name field type not null

For example, after creating student9, add a non empty constraint to the field name

create table student9 ( id int unsigned not null primary key auto_increment, school varchar(10), name varchar(20), age tinyint unsigned, sex enum('male','female') default 'male' ); alter table student9 modify name varchar(20) not null; mysql> desc student9; +--------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-----------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | school | varchar(10) | YES | | NULL | | | name | varchar(20) | NO | | NULL | | | age | tinyint(3) unsigned | YES | | NULL | | | sex | enum('male','female') | YES | | male | | +--------+-----------------------+------+-----+---------+----------------+

1.4.3 delete non empty constraints

alter table table name modify field name field type [null]
For example, delete the non empty constraint of the name field in student9

alter table student9 modify name varchar(20) null; mysql> desc student9; +--------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-----------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | school | varchar(10) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | age | tinyint(3) unsigned | YES | | NULL | | | sex | enum('male','female') | YES | | male | | +--------+-----------------------+------+-----+---------+----------------+

1.5 default value constraint (default)

It refers to the default value when no specific value is inserted into a field
be careful:
1. Only column level constraints can be used
2. For the constraint using default value, if the inserted data is "null", the default value will not be used. The default value will be used only when there is no inserted data

1.5.1 add default value constraint at column level

Syntax:

create table Table name( Field name field type default value )

For example, create the table student10, where the default value for the sex field is' male '

create table student10 ( id int unsigned not null primary key auto_increment, school varchar(10), name varchar(20), age tinyint unsigned, sex enum('male','female') default 'male' );

1.5.2 adding default value constraints to existing tables

alter table name modify field name field type default value;
For example, after creating table student11, set the default value of sex field to male

alter table student11 modify sex enum('male','female') default 'male'

1.5.3 delete default value constraint

alter table name modify field name field type;
For example, delete the constraint that the default value of student11 sex field in the table is male

alter table student11 modify sex enum('male','female')

1.6 auto_increment

Indicates that the value of a field in the table will increase automatically
be careful:
1. There can only be one automatically growing field in a table
2. It is used together with the primary key and is only applicable to integer types
3. Automatically increase the default initial value of 1. Each time a record is added, the value of this field will increase by 1

1.6.1 create an automatic growth constraint when creating a table

create table Table name( Field name field type auto_increment )

For example, create the table student12, add the primary key and automatic growth constraint to the id

create table student12 ( id int unsigned not null primary key auto_increment, school varchar(10), name varchar(20), age tinyint unsigned, sex enum('male','female') default 'male' );

1.6.2 add automatic growth constraints to existing tables

Syntax: alter table table name modify field name field type auto_increment
For example, after creating the table student13, add Auto to the field id in the table_ Increment constraint

alter table student13 modfiy id int auto_increment

1.6.3 delete self growth constraint

Syntax: alter table table name modify field name field type
For example, delete Auto in table student13_ Increment constraint

alter table student13 modify id int unsigned not null primary key

For example:

create table student15 ( id int not null primary key auto_increment, name varchar(20) default 'bertwu' not null )

If you only want to delete the default of the name field, the syntax is as follows:

alter table student15 modify name varchar(20) not null;

1.7 foreign key constraint

Foreign key: the value of a field in one table depends on the value of a field in another table
It mainly realizes the referential integrity in the database
Closely combine the two tables. When modifying or deleting a table, ensure the integrity of the data

For example: t_class student relationship 1:n

1.7.1 creating foreign key constraints

Note: Although MySQL provides column level foreign key constraints, they will not take effect after adding, so table level foreign key constraints are used
Syntax:

create table Table name( Field name field type, ... [constraint Foreign key constraint name] foreign key(Field name) references surface(Field name) )

For example: class student

# Create class table create table class( id int not null primary key auto_increment, name varchar(10) not null ); # Create student table create table student( id int not null primary key auto_increment, name varchar(10) not null, age tinyint, class_id int(2), constraint fk_class_student foreign key(class_id) references class(id) ) mysql> desc class; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(10) | NO | | NULL | | +-------+-------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec) mysql> desc student; +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(10) | NO | | NULL | | | age | tinyint(4) | YES | | NULL | | | class_id | int(2) | YES | MUL | NULL | | +----------+-------------+------+-----+---------+----------------+

1.7.2 add foreign key constraints to existing tables

Syntax:
alter table table name add [constraint foreign key constraint name] foreign key (field name) references table (field name)
For example, create a student (tt_student) table and add foreign keys to it.

create table tt_student( id int not null primary key auto_increment, name varchar(10) not null, age tinyint, class_id int(2) ) alter table tt_student add constraint fk_class_tt_student foreign key(class_id) references class(id)

1.7.3 deleting foreign key constraints

alter table name drop foreign key constraint name

For example, table TT_ Delete foreign key constraint name in student

alter table tt_student drop foreign key fk_class_tt_student

1.7.4 delete related tables

Method 1. Delete the table with foreign key constraints first, and then delete the main table
For example: delete TT first_ Delete class from student table

drop table tt_student drop table class

Method 2. Delete the foreign key constraint first and then delete the table

alter table tt_student drop foreign key fk_class_tt_student drop table class

Add new comment

0 comments