mysql约束的操作_MySql--约束和级联操作

约束(constraint)和级联操作

一、什么是约束及为什么要使用约束

约束实际上就是表中数据的限制条件

表在设计的时候加上约束的目的是为了保证表中的记录完整和有效

二、约束的种类

非空约束 not null

唯一性约束 unique

主键约束 primary key(简称PK)

外键约束 foreign key(简称FK)

检查约束【目前mysql不支持，oracle支持】

2.1 非空约束

not null约束的字段，不能为null，必须给定具体的数据

创建学生表，给字段添加非空约束：用户名不能为空

create table t_user(

id int(10),

name varchar(32) not null,

email varchar(128)

);

2.2 唯一性约束

unique约束的字段具有唯一性，不可重复

创建用户表，保证邮箱地址唯一

列级约束

create table t_user(

id int(10),

name varchar(32) not null,

email varchar(128) unique

);

insert into t_user(id,name) values(2,'abc');//成功

insert into t_user(id,name) values(3,'def');//成功

"unique约束"约束的字段不能重复，但是可以为null

表级约束：

create table t_user(

id int(10),

name varchar(32) not null,

email varchar(128),

unique(email)

);

使用表级约束给多个字段联合添加约束

以下程序表示name和email两个字段联合唯一

create table t_user(

id int(10),

name varchar(32) not null,

email varchar(128),

unique(name,email)

);

表级约束可以给约束起名字：以后可以通过这个名字来删除这个约束：

create table t_user(

id int(10),

name varchar(32) not null,

email varchar(128),

constraint t_user_email_unique unique(email)

);

not null和unique可以联合使用，被not null unique约束的字段，级不能为空，也不能重复：

create table t_user(

id int(),

name varchar(32) not null unique

);

insert into t_user(id,name) values(1,'jack');//成功

insert into t_user(id,name) values(2,'jack');//失败

insert into t_user(id) values(3);//失败

2.3 主键约束(primary key 简称PK)

涉及到的术语：

主键约束

主键字段

主键值

关系：

表中的某个字段添加主键约束之后，该字段被称为主键字段，

主键字段中出现的每一个数据都被称为主键值

作用：

给某个字段添加主键约束primary key之后，该字段不能重复，

并且也不能为空。效果和"not null unique"约束相同，

但本质不同，主键约束除了可做到"not null unique"之外，

主键字段还会默认添加"索引-index"

一张表应该有主键字段，若没有，表示这张表是无效的。

"主键值"是当前行数据的唯一标识。

即使表中的两行记录相关的数据是相同的，但是由于主键值不同，

我们认为这是两行完全不同的数据。

分类：

根据字段分：

单一主键：给一个字段添加主键约束

复合主键：给多个字段联合添加一个主键约束

无论是单一主键还是复合主键，一张表主键约束只能有一个。

根据性质分：

自然主键：主键是一个自然数，这个自然数和当前表的业务没有任何关系

业务主键：主键值和当前表中业务紧密相关，当业务数据发生改变时，

主键值通常会受到影响，所以不建议使用。

例：

单一主键：

列级约束：

create table t_user(

id int(10) primary key,

name varchar(32)

);

表级约束：(起名)

create table t_user(

id int(10),

name varchar(32),

constraint t_user_id_pk primary key(id)

);

复合主键：(只能使用表级约束)

create table t_user(

id int(10),

name varchar(32),

email varchar(128),

primary key(id,name)

);

也可以起名：

create table t_user(

id int(10),

name varchar(32),

email varchar(128),

constraint t_user_id_name_pk primary key(id,name)

);

在mysql数据库管理系统中提供了一个自增的数字，专门用来自动生成主键值。主键值不需要用户维护和提供，自动生成。这个自增的数字默认从1开始，以1递增：1,2,3,4,5,6,7,8....

例：

create table t_user(

id int(10) primary key auto_increment,

name varchar(32)

);

2.4 外键约束(foreign key 简称FK)

涉及到的术语：

外键约束

外键字段

外键值

关系：

某个字段添加外键约束之后，该字段称为外键字段，

外键字段中的每一个数据都是外键值。

分类：

单一外键：给一个字段添加外键约束

复合外键：给多个字段联合添加一个外键约束

一张表中可以有多个外键字段

例：

设计数据库表用来存储学生和班级信息：

学生信息和班级信息之间的关系，一个班级对应多个学生(一对多关系)

方案一：将学生信息和班级信息存储到一张表中。(缺点：数据冗余)

学生信息表t_student

学生信息.PNG

方案二：将学生信息和班级信息分开两张表存储：学生表+班级表

学生表 t_student

学生表.PNG

班级表 t_class

班级表.PNG

结论：为了保证t_student表中的classno字段中的数据必须来自于t_class表中cno字段中的数据，有必要给t_student表中的classno字段添加外键约束，classno字段被称为外键字段，该字段中的100 200 300被称为外键值。classno这里是一个单一外键字段

注意：

1.外键值可以为null

2.外键字段去引用一张表的某个字段的时候，被引用的字段必须具有unique约束。

3.有了外键引用之后，表分为父表和子表

以上父表是：班级表

字表是：学生表

创建表：先创建父表，再创建子表；

删除数据：先删除子表中的数据，再删除父表中的数据；

插入数据：先插入父表中的数据，再插入子表中的数据。

SQL文：

drop table if exists t_student;

drop table if exists t_class;

create table t_class(

cno int(3) primary key,

cname varchar(128) not null unique

);

create table t_student(

sno int(3) primary key,

sname varchar(32) not null,

classno int(3),

constraint t_student_classno_fk foreign key(classno) references t_class(cno)

);

insert into t_class(cno,cname) values(100,'高三1班');

insert into t_class(cno,cname) values(200,'高三2班');

insert into t_class(cno,cname) values(300,'高三3班');

insert into t_student(sno,sname,classno) values(1,'jack',100);

insert into t_student(sno,sname,classno) values(2,'lucy',100);

insert into t_student(sno,sname,classno) values(3,'zhangsan',100);

insert into t_student(sno,sname,classno) values(4,'ford',200);

insert into t_student(sno,sname,classno) values(5,'king',200);

insert into t_student(sno,sname,classno) values(6,'allen',300);

insert into t_student(sno,sname,classno) values(7,'lisi',300);

找出每个学生的班级名称

select s.*,c.* from t_student s join t_class c on s.classno=c.cno;

注意：多对一关系，是在多的一方加外键

三、级联更新与删除

在删除父表中的数据的时候，级联删除子表中的数据on delete cascade;

在更新父表中的数据时候，级联更新子表中的数据on update cascade;

级联操作在外键约束后面添加

级联操作要谨慎使用，因为级联操作会将数据改变或者删除

例：

alter table t_student add

constraint t_student_classno_fk

foreign key(classno) references t_class(cno) on delete cascade;

alter table t_student add

constraint t_student_classno_fk

foreign key(classno) references t_class(cno) on update cascade;