700字范文 > mysql完整性约束

mysql完整性约束

时间：2023-10-10 20:14:13

相关推荐

mysql完整性约束

文章目录

一、完整性约束1.1 MySQL支持的完整性约束1.2 主键约束1.2.1 新创建表，添加主键1.2.2 给已有表添加主键1.2.3 删除主键约束1.3 唯一约束 unique1.3.1 列级添加唯一约束1.3.2 表级添加唯一约束1.3.3 给已有表添加唯一约束1.3.4 删除唯一约束1.4 非空 not null1.4.1 列级添加非空约束1.4.2 给已有表添加非空约束1.4.3 删除非空约束1.5 默认值约束(default)1.5.1 列级添加默认值约束1.5.2 给已有表添加默认值约束1.5.3 删除默认值约束1.6 自动增长约束(auto_increment)1.6.1 创建表时候创建自动增长约束1.6.2 给已有表添加自动增长约束1.6.3 删除自增长约束1.7 外键约束(foreign key)1.7.1 创建外键约束1.7.2 在已有表中添加外键约束1.7.3 删除外键约束1.7.4 有关系的表进行删除

一、完整性约束

1.1 MySQL支持的完整性约束

1.2 主键约束

特点：唯一且非空

主键可以由一个字段组成也可以由多个字段组成（复合主键）

如果主键可以由一个字段组成，则既可以添加到列级也可以添加到表级

如果由多个字段组成只能添加到表级

目的：为了保证每一行不重复

1.2.1 新创建表，添加主键

(1)在字段的列级添加约束

create table 表名(字段名字段类型 primary key)

例如：创建student1表(id name age sex)其中id为主键

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

(2)表级上添加主键约束

create table 表名(字段名1 字段类型1,字段名2 字段类型2,.....字段名n 字段类型n,[constraint 主键约束名] primary key(字段名))

例如：创建student2表(id name age sex)其中id为主键在表级添加主键约束

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)多个字段作为主键，只能添加到表级

create table 表名(字段名1 字段类型1,字段名2 字段类型2,.....字段名n 字段类型n,[constraint 主键约束名] primary key(字段名1,字段名2))

例如：创建student3表(school id name age sex)其中school 和id为主键在表级添加主键约束

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 给已有表添加主键

语法:alter table 表名 add [constraint 主键约束名] primary key(字段名)

例如：创建表student4 ,不加id主键约束，创建完后添加主键约束

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 删除主键约束

语法alter table 表名 drop primary key;

例如：删除表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

指：表中字段的值不能重复

1.3.1 列级添加唯一约束

语法:

create table 表名(字段名1 字段类型1 unique,字段名2 字段类型2,.....字段名n 字段类型n)

例如: 创建表student5 给name字段添加唯一约束

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 表级添加唯一约束

语法:

create table 表名(字段名1 字段类型1,字段名2 字段类型2,.....字段名n 字段类型n,[constraint 唯一约束名] unique(字段1,字段2...));

例如:创建表student6,给id 和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 给已有表添加唯一约束

alter table 表名 add [constraint 唯一约束名] unique(字段1,字段2...)

例如：创建表student7后给表中name添加唯一约束

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 删除唯一约束

alter table 表名 drop index 唯一约束名

注意：

如果单个字段没有指定唯一约束名，则默认的唯一约束名为字段名

如果是多个字段组合为唯一约束时候，默认的唯一约束名为第一个字段的名称

如果指定了约束名则删除的时候写约束名

例如:删除表student7中的唯一约束

alter table student7 drop index uk_student7_name

1.4 非空 not null

某张表中某字段的值不能为空

注意:

1.只能使用列级添加

2.空字符串"" 或者0 都不是null

1.4.1 列级添加非空约束

create table 表名(字段名字段类型 not null )

例如：创建student8表，给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 给已有表添加非空约束

alter table 表名 modify 字段名字段类型 not null

例如：创建student9后，给字段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 删除非空约束

alter table 表名 modify 字段名字段类型 [null]

例如:删除student9中name字段的非空约束

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)

指在没有对某字段插入具体值时候会去默认的值

注意:

1.只能使用列级约束

2.对于使用默认值约束，如果插入的数据为“null”,则不会使用默认值，只有没有插入数据时候，才会使用默认值

1.5.1 列级添加默认值约束

语法:

create table 表名(字段名字段类型 default value)

例如：创建表student10 ,其中对sex字段设置默认值为’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 给已有表添加默认值约束

alter table 表名 modify 字段名字段类型 default value;

例如:创建表student11后，给sex字段设置默认值为’男’

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

1.5.3 删除默认值约束

alter table 表名 modify 字段名字段类型;

例如:删除表student11 sex字段的默认值为’男’约束

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

1.6 自动增长约束(auto_increment)

指：表中某字段的值会自动增加

注意:

1.一张表中只能有一个自动增长的字段

2.配合主键一起使用并且只适用于整数类型

3.自动增长默认的初始值1，每增加一条记录，该字段的值会增加1

1.6.1 创建表时候创建自动增长约束

create table 表名(字段名字段类型 auto_increment)

例如:创建表student12 ，给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 给已有表添加自动增长约束

语法:alter table 表名 modify 字段名字段类型 auto_increment

例如:创建表student13后，给表中字段id添加auto_increment约束

alter table student13 modfiy id int auto_increment

1.6.3 删除自增长约束

语法:alter table 表名 modify 字段名字段类型

例如删除表student13中auto_increment 约束

alter table student13 modify id int unsigned not null primary key

注意，当某一个字段同时有 default 、auto_increment、 not null 约束时，如果单纯的想删除一个，需要把其他的约束重新写一边，否则会全部删除

例如：

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

如果只想删除name字段的default，则语法如下：

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

1.7 外键约束(foreign key)

外键:某一张表中某字段的值依赖于另一张表中某字段的值

主要实现了数据库中的参照完整性

将两张表紧密结合，对某张表修改或者删除时候，要保证数据的完整

例如：班级(t_class) 学生(t_student) 关系 1:n

1.7.1 创建外键约束

注意:虽然MySQL提供了列级添加外键约束，但添加完后不会生效，所以使用表级添加外键约束

语法:

create table 表名(字段名字段类型,...[constraint 外键约束名] foreign key(字段名) references 表(字段名) )

例如:班级(class) 学生(student)

# 创建班级表create table class(id int not null primary key auto_increment,name varchar(10) not null);# 创建学生表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 在已有表中添加外键约束

语法:

alter table 表名 add [constraint 外键约束名] foreign key(字段名) references 表(字段名)

例如:创建学生(tt_student)表，之后为其添加外键。

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)