本文摘自PHP中文网，作者清浅，侵删。
sql完整性约束有：1、“NOT NULL”非空约束，指定的列不允许为空值；2、“UNIQUE”唯一约束，指定列中没有重复值；3、“PRIMARY KEY”主键约束，唯一的标识出表的每一行；4、“FOREIGN KEY”外键约束；5、“CHECK”条件约束。
完整性约束是保证用户所做的修改不会破坏数据的一致性，是保护数据正确性和相容性的一种手段。
常见的5种约束：
NOT NULL：非空约束C，指定的列不允许为空值
UNIQUE：唯一约束U，指定的列中没有重复值，或该表中每一个值或者每一组值都将是唯一的
PRIMARY KEY：主键约束P，唯一的标识出表的每一行,且不允许空值,一个表只能有一个主键约束
FOREIGN KEY：外键约束R，一个表中的列引用了其它表中的列，使得存在依赖关系，可以指向引用自身的列
CHECK：条件约束C，指定该列是否满足某个条件
非空约束 NK
1 2 3 4 | create table member(
mid number,
name varchar2(200) not null
);
|
插入空报错：
1 2 3 4 | SQL> insert into member(mid,name) values(1,null);
*
第 1 行出现错误:
ORA-01400: 无法将 NULL 插入 ("SCOTT"."MEMBER"."NAME")
|
唯一约束 UK
1 2 3 4 5 | drop table member purge;create table member(
mid number,
name varchar2(200) not null,
email varchar2(50) unique
);
|
插入报错
1 | SQL> insert into member(mid,name,email) values (1,'scott','scott@163.com');
|
已创建 1 行。
1 2 3 4 5 | SQL> insert into member(mid,name,email) values (2,'jack','scott@163.com');
insert into member(mid,name,email) values (2,'jack','scott@163.com')
*
第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SCOTT.SYS_C0010891)
|
给约束指定名字
这错误并没有像之前的非空约束那样，准确告诉用户哪个字段出现了问题，我们可以给这个约束指定一个名字
1 2 3 4 5 6 | create table member(
mid number,
name varchar2(200) not null,
email varchar2(50),
constraint uk_email unique(email)
);
|
主键约束 PK
主键约束=非空约束+唯一约束
1 2 3 4 5 6 7 | create table member(
mid number,
name varchar2(200) not null,
email varchar2(50),
constraint pk_number primary key(mid),
constraint uk_email unique(email)
);
|
插入错误的数据报错：
1 | SQL> insert into member(mid,name,email) values (1,'jack','scott@163.com');
|
已创建 1 行
1 2 3 4 5 | SQL> insert into member(mid,name,email) values (1,'jack','jack@163.com');
insert into member(mid,name,email) values (1,'jack','jack@163.com')
*
第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SCOTT.PK_NUMBER)
|
检查约束 CK
1 2 3 4 5 6 7 8 9 10 | create table member(
mid number,
name varchar2(200) not null,
age number check(age between 0 and 200),
sex varchar2(10),
email varchar2(50),
constraint pk_number primary key(mid),
constraint uk_email unique(email),
constraint ck_sex check(sex in('男','女'))
);
|
插入错误数据报错：
1 2 3 4 5 | SQL> insert into member(mid,name,age,sex,email) values (1,'Jack','300','无','jack@163.com');
insert into member(mid,name,age,sex,email) values (1,'Jack','300','无','jack@163.com')
*
第 1 行出现错误:
ORA-02290: 违反检查约束条件 (SCOTT.CK_SEX)
|
主外键约束 FK
1 2 3 4 5 6 7 8 9 10 11 12 | create table member(
mid number,
name varchar2(200) not null,
constraint pk_mid primary key(mid)
);
create table advice(
adid number,
content clob not null,
mid number,
constraint pk_adid primary key(adid),
constraint fk_mid foreign key(mid) references member(mid)
);
|
正确插入数据，mid在附表中已经存在
1 2 3 4 5 | insert into member(mid,name) values (1,'Scott');
insert into member(mid,name) values (2,'Jack');
insert into advice(adid,content,mid) values (1,'test',1);
insert into advice(adid,content,mid) values (2,'test',2);
commit;
|
插入一条父表中没有的mid数据
1 2 3 4 5 | SQL> insert into advice(adid,content,mid) values (3,'test',3);
insert into advice(adid,content,mid) values (3,'test',3)
*
第 1 行出现错误:
ORA-02291: 违反完整约束条件 (SCOTT.FK_MID) - 未找到父项关键字
|
采用主外键约束会在表的删除和数据删除方面存在新的问题
删除父表得先删除子表
1 2 3 4 | SQL> drop table member;drop table member
*
第 1 行出现错误:
ORA-02449: 表中的唯一/主键被外键引用
|
强制性删除父表，这样就不能使用purge选项了，并且字表中的主外键约束也将不复存在
1 2 3 | SQL> drop table member cascade constraint;
表已删除。
SQL> insert into advice(adid,content,mid) values (3,'test',3)；
|
已创建 1 行
删除父表的数据的先删除字表中的数据
1 2 3 4 5 | SQL> delete from member where mid=1;
delete from member where mid=1
*
第 1 行出现错误:
ORA-02292: 违反完整约束条件 (SCOTT.FK_MID) - 已找到子记录
|
除了这种方法外还可以设置级联操作子句
on delete cascade
1 2 3 4 5 6 7 | create table advice(
adid number,
content clob not null,
mid number,
constraint pk_adid primary key(adid),
constraint fk_mid foreign key(mid) references member(mid) on delete cascade
);
|
这样的话，删除附表数据，会把字表中的数据也给删除。
on delete set null
1 2 3 4 5 6 7 | create table advice(
adid number,
content clob not null,
mid number,
constraint pk_adid primary key(adid),
constraint fk_mid foreign key(mid) references member(mid) on delete set null
);
|
这样删除父表中的数据，子表中的对应的关联字段将被设置为空
1 2 3 4 5 6 7 8 9 10 | SQL> delete from member where mid=1;
已删除 1 行。
SQL> select * from advice;
ADID CONTENT MID
---------- ---------- ----------
test
test 2
|
查看约束
1 2 3 4 5 6 7 | SQL> select constraint_name,constraint_type,table_name from user_constraints;
CONSTRAINT_NAME C TABLE_NAME
------------------------------ - ------------------------------
FK_DEPTNO R EMP
PK_EMP P EMP
PK_DEPT P DEPT
|
R外键约束，C检查约束，Q唯一约束，P主键约束
修改约束
增加约束
1 2 3 | alter table member add constraint pk_mid primary key(mid);
alter table member add constraint ck_age check(age between 0 and 200);
alter table member modify (name varchar2(50) not null);
|
需要注意的是非空约束使用的是modify的方式
禁用/启用约束
非外键
1 2 | alter table member disable constraint ck_age;
alter table member enable constraint ck_age;
|
外键
1 | alter table member disable constraint pk_mid cascade;
|
删除约束
1 2 | alter table member drop constraint pk_mid cascade;
alter table member drop constraint ck_age;
|
以上就是sql完整性约束有哪些的详细内容，更多文章请关注木庄网络博客！
相关阅读 >>
sql完整性约束有哪些
更多相关阅读请进入《sql完整性约束》频道 >>
机械工业出版社
本书主要讲述了数据模型、基于对象的数据库和XML、数据存储和查询、事务管理、体系结构等方面的内容。
转载请注明出处：木庄网络博客 » sql完整性约束有哪些
