mysql表添加完整性约束_SQL语句——完整性约束

1.MySQL支持的完整性约束

a.主键约束

特点：唯一且非空

主键可以由一个字段组成，也可以由多个字段组成

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

如果主键由多个字段组成，则只能添加到表级。

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

create table 表名(

字段名 字段类型 primary key

)

create table student_1(

id varchar(10) primary key,

name varchar(15),

age int(2),

sex varchar(1)

)

desc student_1

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

creat tabel 表名 (

字段名1，字段类型1，

字段名2，字段类型2，

……

字段名n, 字段类型n，

[constraint 主键约束名] primary key(字段名)

)

create table student_2(

id int(8),

name varchar(20),

age int(2),

sex varchar(1),

constraint pk_student_2 primary key(id)

)

-- 查看表结构

desc student_2

(3)多个字段作为组件，只能添加到表级

creat table 表名(

字段名1，字段类型1，

字段名2，字段类型2，

……

字段名n,字段类型n，

[constraint 主键约束名] primary key(字段名1，字段名2…)

)

create table student_3(

id int(8),

name varchar(20),

school varchar(10),

age int(2),

sex varchar(1),

constraint pk_student_3 primary key(school,id)

)

-- 查看表结构

desc student_3

(4)给已有表添加主键

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

create table student_4(

id int(8),

name varchar(20),

school varchar(10),

age int(2),

sex varchar(1)

)

alter table student_4 add constraint pk_student_4 primary key(id)

-- 查看表结构

desc student_4

(5)删除主键约束

语法：alter table 表名 drop primary key;

alter table student_4 drop primary key

b.唯一约束 unique

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

(1)列级添加唯一约束，语法：

creat tabel 表名 (

字段名1，字段类型1 unique，

字段名2，字段类型2，

……

字段名n, 字段类型n

)

create table student_5(

id int(8),

name varchar(20) unique,

school varchar(10),

age int(2),

sex varchar(1)

)

-- 查看表结构

desc student_5

(2)表级添加唯一约束:

creat table 表名(

字段名1，字段类型1，

字段名2，字段类型2，

……

字段名n,字段类型n，

[constraint 唯一约束名] unique (字段名1，字段名2…)

)

create table student_6(

id int(8),

name varchar(20),

age int(2),

sex varchar(1),

constraint uk_student_6_id_name unique(id,name)

)

(3)给已有表添加唯一约束

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

create table student_7(

id int(8),

name varchar(20),

age int(2),

sex varchar(1)

)

alter table student_7 add constraint uk_student_7_name unique(name)

-- 查看表结构

desc student_7

(4)删除唯一约束

语法：

alter table 表名 drop index 唯一约束名

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

create table student_7(

id int(8),

name varchar(20),

age int(2),

sex varchar(1)

)

alter table student_7 add unique(name)

alter table student_7 drop index name

-- 查看表结构

desc student_7

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

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

create table student_7(

id int(8),

name varchar(20),

age int(2),

sex varchar(1)

)

alter table student_7 add constraint uk_student_7_name unique(name)

alter table student_7 drop index uk_student_7_name

c.非空约束

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

注意：1.只能使用列级添加

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

(1)列级添加非空约束

creat table 表名(

字段名 字段类型 not null

)

create table student_8(

id int(8) not null,

name varchar(20),

age int(2),

sex varchar(1)

)

-- 查看表结构

desc student_8

(2)给已有表添加非空约束

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

create table student_9(

id int(8),

name varchar(20),

age int(2),

sex varchar(1)

)

alter table student_9 modify name varchar(20) not null

-- 查看表结构

desc student_9

(3)删除非空约束

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

alter table student_9 modify name varchar(20)

[ ]内的内容可写可不写

d.默认值约束(default)

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

注意：1.只能使用列级约束

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

(1)列级添加默认追约束

语法：creat table 表名 (

字段名 字段类型 default value

)

create table student_10(

id int(8),

name varchar(20),

age int(2),

sex varchar(1) default '男'

)

-- 查看表结构

desc student_10

(2)给已有表添加默认值约束

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

create table student_11(

id int(8),

name varchar(20),

age int(2),

sex varchar(1)

)

alter table student_11 modify sex varchar(1) default '男'

-- 查看表结构

desc student_11

(3)删除默认值约束

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

alter table student_11 modify sex varchar(1)

e.自动增长约束(auto_increment)

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

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

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

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

(1)创建表的时候创建自动增长约束

create table 表名(

字段名 字段类型 auto_increment

)

create table student_12(

id int(8) PRIMARY KEY auto_increment,

name varchar(20) ,

age int(2),

sex varchar(1)

)

(2)给已有表添加自动增长约束

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

create table student_13(

id int(8)PRIMARY KEY,

name varchar(20) ,

age int(2),

sex varchar(1)

)

alter table student_13 modify id int(8) auto_increment

-- 查看表结构

desc student_13

(3)删除自动增长约束

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

alter table student_13 modify

f.外键约束(foreign key)

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

如果一个字段X在一张表(表一)中是主关键字，而在另外一张表(表二)中不是主关键字，则字段X称为表二的外键；换句话说如果关系模式R1中的某属性集不是自己的主键，而是关系模式R2的主键，则该属性集称为是关系模式R1的外键。

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

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

例如：班级(t_class)学生(t_student)关系：1-n (一对多)

(1)创建外键约束

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

语法：create table 表名(

字段名，字段类型

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

)

create TABLE t_class(

cno INT(8) PRIMARY KEY auto_increment,

cname VARCHAR(20)

)

create TABLE t_student(

son INT(8) PRIMARY KEY auto_increment,

sname VARCHAR(20),

age INT(2),

sex VARCHAR(2),

stu_con INT(8),

CONSTRAINT fk_t_student_stu_con FOREIGN KEY(stu_con) REFERENCES t_class(cno)

)

(2)在已有表中添加外键约束

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

例如：班级(tt_class)学生(tt_student)

--创建班级表(tt_class)

create TABLE tt_class(

cno INT(8) PRIMARY KEY auto_increment,

cname VARCHAR(20)

)

--创建学生表(tt_student)

create TABLE tt_student(

son INT(8) PRIMARY KEY auto_increment,

sname VARCHAR(20),

stu_cno INT(8)

)

--给tt_student表添加外键约束

alter table tt_student add CONSTRAINT fk_tt_student_stu_cno FOREIGN KEY (stu_cno) REFERENCES tt_class(cno)

desc tt_class

desc tt_student

(3)删除外键约束

alter table 表名 drop foreign key 外键约束名

--删除外键约束名

alter table tt_student drop foreign key fk_tt_student_stu_cno

(4)有关系的表进行删除

(1)以公共关键字作主键的表为主键表(父表，主表)

(2)以公共关键字作外键的表为外键表(从表，外表)

a.先删除有外键约束的表(从表)，再删除主表

会报错：

t_student t_class

drop table t_class;

[SQL] drop table t_class;

[Err] 3730 - Cannot drop table 't_class' referenced by a foreign key constraint 'fk_t_student_stu_con' on table 't_student'.

应该：

drop table t_student;

drop table t_class;

b.先删除外键约束，再删除表

alter table t_student drop FOREIGN KEY fk_t_student_stu_con

drop table t_class

drop table t_student