文章目录
- 一:实体完整性
-
- (1)我们来看一下创建表时将某一个属性定义为主键——在对应列后面加`primary key`
- (2)创建表并将多个属性的组合定义为主键。———在表的属性列定义结束之后,写:primary key(属性名列表)
- (3)给约束起约束名。在约束定义前加“constraint 约束名”——格式:constraint 约束名 约束定义
- (4)添加主键——对已创建好的表添加主键,即对表进行修改。
- (5)删除约束
- 二、唯一约束:unique,实现属性值唯一
-
- (1)创建表时设置唯一约束
- (2)删除唯一约束
- 三、用户定义的完整性——check
-
- (1)创建表时添加check
- (2)给已有表添加check
- (3)删除check,和删除主键约束一样
- 三、参照完整性——外键(码)
-
- (1)给已有表添加外码:该属性不是该表的码,是另一个表的码
- (2)建表时创建外码约束
-
- a) 表级形式
- b)列级约束
一:实体完整性
首先我们来看一下实体完整性
首先我们执行下面sql 语句
CREATE TABLE test1(
n1 INT,
n2 INT,
n3 INT)
执行下面sql语句三遍
INSERT INTO test1
VALUES(1,2,3)
我们会发现,数据表中出现了三条一样的数据
但是,在大多数情况下,我们要在表的每一行有一个唯一标识,使得每一行中有一个或多个属性字段值不相同,所以我们引入主键(码) primary
key
,来实现实体完整性,将字段加上主键,那么这个字段必须是唯一而且非空的。
(1)我们来看一下创建表时将某一个属性定义为主键——在对应列后面加primary key
可以采用列级形式定义约束,只能访问一个属性,对于主键约束来说,只能将单个属性定义为主键。
下面我们来看一个例子:
例:
create table test2
( n1 int primary key,
n2 int,
n3 int
)
我们在来插入数据
INSERT INTO test2
VALUES(1,2,3);
我们发现在执行第一条的时候成功了,但是执行第二条的时候却失败了,提示
Duplicate entry '1' for key 'PRIMARY'
证明主键中已经有了一个相同的值,插入失败
(2)创建表并将多个属性的组合定义为主键。———在表的属性列定义结束之后,写:primary key(属性名列表)
在有些情况下,我们设置一个属性字段为主键实现不了相关功能,必须让多个属性字段合起来是唯一的,那么我们就需要设置多个属性字段为主键。
我们先来一个错误的示范
例2:错误写法。
create table test3
( n1 int primary key,
n2 int primary key,
n3 int
)
这样的做法是错误的,我们会发现当我们执行这条sql语句时,报错:Multiple primary key defined
,提示我们定义了多个主键
正确方法: 采用约束的表级形式,可以访问多个属性,对于主键约束来说,可以定义单个属性或多个属性的组合为主键。
下面我们来看下正确的例子:
格式:在表的属性列定义结束之后,写:primary key(属性名列表)
例3:
create table test3
( n1 int,
n2 int,
n3 int,
primary key(n1,n2)
);
insert into test3
values(1,2,3);
当我们执行第二次插入的时候,我们发现插入失败Duplicate entry '1-2' for key 'PRIMARY'
出错,主键约束起作用。
insert into test3
values(1,1,3);
我们发现插入成功了。
这里我们要注意:n1不是主键,(n1,n2)是主键。
(3)给约束起约束名。在约束定义前加“constraint 约束名”——格式:constraint 约束名 约束定义
create table test4
( n1 int,
n2 int,
n3 int,
constraint pk_test4 primary key(n1)
)
约束的列级形式也可以定义约束的名字,方法和表级约束形式一样,写在对应列定义之后。
以上定义主键的形式是在创建表时创建。
下面我们来看一下当我们创建表以后,但是我们却没有在表中加入主键时该怎么办。
(4)添加主键——对已创建好的表添加主键,即对表进行修改。
格式:
alter table 表名
add constraint 约束名 —— 约束的表级形式
例4:
alter table test1
add constraint pk_test1 primary key(n1)
我们在执行后发现报错。
原因: 设置主键的属性不是非空属性,需要先设置属性非空,再添加主键约束。
继续执行,执行后仍错,
原因: 是表中已有多个元组在n1上的值均为1,n1的值不唯一。删除表中元组再添加主键。
注意: 在已有表上添加主键约束时:
a)该属性须为非空属性
b)表中已有数据在该属性上的取值和主键约束不冲突。
(5)删除约束
格式:
alter table 表名
drop constraint 约束名
二、唯一约束:unique,实现属性值唯一
MYSQL中null值也是不可以重复的。
唯一约束的用法与主键相同,只需将primary key
换成unique
例5:
(1)创建表时设置唯一约束
设置单个属性字段为唯一约束
create table test5
( n1 int unique,
n2 int,
n3 int );
设置多个属性字段为唯一约束
create table test5
( n1 int,
n2 int,
n3 int,
constraint uk_test5 unique(n1)
)
(2)删除唯一约束
删除唯一约束,与删除主键方法一样。
格式:
alter table 表名
drop constraint 约束名
三、用户定义的完整性——check
CHECK 约束用于限制列中的值的范围。
- 如果对单个列定义 CHECK 约束,那么该列只允许特定的值。
- 如果对一个表定义 CHECK 约束,那么此约束会在特定的列中对值进行限制。
引入:我们来创建一个 stu 表
create table stu
( id int,
name nvarchar(10),
sex nvarchar(2)
);
insert into stu(id,name,sex)
values(1,'张三','二');
使得属性的取值满足某个条件,用check。
格式: check(条件表达式)
条件表达式的写法和查询语句中写法一致。
(1)创建表时添加check
例6:
create table stu
( id int,
name nvarchar(10),
sex nvarchar(2),
constraint ck_stu check(sex='男' or sex='女')
);
(2)给已有表添加check
例7:
限制性别只能取男或女两个值
alter table stu
add constraint ck_stu check(sex='男' or sex='女');
注意: 添加约束时,表中如果已有数据且和约束条件冲突,需要先删除冲突的元组。
(3)删除check,和删除主键约束一样
格式:
alter table 表名
drop constraint 约束名
三、参照完整性——外键(码)
(1)给已有表添加外码:该属性不是该表的码,是另一个表的码
格式:
alter table 表名
add constraint fk_表名foreign key(列名) references 表名(列名);
说明: 约束名中的表名是参照表的表名,第2个表名是被参照表的表名
例8:
alter table emp
add constraint fk_foreign key(dept_id) references dept(id);
但是我们会发现出错了。
原因: dept表中的id不是主键
。给dept添加主键、唯一约束或唯一索引。
alter table dept
add constraint pk_dept primary key(id)
再添加外码
(2)建表时创建外码约束
a) 表级形式
create table e
( id int,
name varchar(50),
dept_id smallint,
manager_id int,
title varchar(50),
salary numeric(11,2),
constraint fk_e foreign key(dept_id) references dept(id)
)
b)列级约束
create table e
( id int,
name varchar(50),
dept_id smallint constraint fk_e foreign key(dept_id) references dept(id),
manager_id int,
title varchar(50),
salary numeric(11,2),
)
删除被参照表中的数据时:
insert into e
select id,name,dept_id,managerid,title,salary
from emp
delete
from dept
where id=1
出错:不能删除。
原因: 已经有员工参照1号部门
修改被参照表中的数据时:
update dept
set id=20
where id=1
出错。
添加被参照表中的数据时:
insert into dept
values(13,'aa',3)
可以。对参照表无影响。
被参照表使用时受限,被参照表上数据的修改、删除受限,添加不受限制。被参照表删除也是受限的。
delete
from dept
where id=13
注意是受限不是禁止。当不影响参照表时是可以修改和删除数据的。
删除被参照表:不可以。定义的有外键。
删除、修改参照表中的数据时:
delete
from e
where id=24
update e
set dept_id=20
where dept_id=1
出错: 在dept表中没有20号部门。
insert into e
values(30,James,20,5,sales,2000)
被参照表使用时受限,参照表上数据的修改、添加受限,删除不受限。
解决方法:
级联删除、级联修改、级联置空:
- 删除被参照表中的数据时,将参照表中对应的数据也删除,称为级联删除。
或者将参照表中对应数据在该属性上的取值置为空值,如果该属性可以取空值的话。 - 当修改被参照表中的数据时,将参照表中参照对象的该属性值也跟着修改,称为级联修改。
或者将参照表中对应数据在该属性上的取值置为空值,如果该属性可以取空值的话。
声明外键时,注明:
create table e
( id int,
name varchar(50),
dept_id smallint,
manager_id int,
title varchar(50),
salary numeric(11,2),
constraint fk_e foreign key(dept_id) references dept(id)
on delete cascade | set null
on update cascade | set null
)
后两句之间无关系,可写一个,也可分别设置不同的处理。
本文地址:https://blog.csdn.net/Black_Customer/article/details/110672425