sql完整性约束有哪些

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

create table member(     mid number,         name varchar2(200) not null     );

插入空报错：

SQL> insert into member(mid,name) values(1,null); * 第 1 行出现错误: ORA-01400: 无法将 NULL 插入 ("SCOTT"."MEMBER"."NAME")

唯一约束 UK

drop table member purge;create table member(     mid number,     name varchar2(200) not null,     email varchar2(50) unique     );

插入报错

SQL> insert into member(mid,name,email) values (1,'scott','scott@163.com');

已创建 1 行。

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)

给约束指定名字

这错误并没有像之前的非空约束那样，准确告诉用户哪个字段出现了问题，我们可以给这个约束指定一个名字

create table member(     mid number,     name varchar2(200) not null,     email varchar2(50),         constraint uk_email unique(email)     );

主键约束 PK

主键约束=非空约束+唯一约束

create table member(     mid number,     name varchar2(200) not null,     email varchar2(50),         constraint pk_number primary key(mid),         constraint uk_email unique(email)     );

插入错误的数据报错：

SQL> insert into member(mid,name,email) values (1,'jack','scott@163.com');

已创建 1 行

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

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('男','女'))        );

插入错误数据报错：

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

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在附表中已经存在

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数据

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) - 未找到父项关键字

采用主外键约束会在表的删除和数据删除方面存在新的问题

删除父表得先删除子表

SQL> drop table member;drop table member            * 第 1 行出现错误: ORA-02449: 表中的唯一/主键被外键引用

强制性删除父表，这样就不能使用purge选项了，并且字表中的主外键约束也将不复存在

SQL> drop table member cascade constraint; 表已删除。 SQL> insert into advice(adid,content,mid) values (3,'test',3)；

已创建 1 行

删除父表的数据的先删除字表中的数据

SQL> delete from member where mid=1; delete from member where mid=1 * 第 1 行出现错误: ORA-02292: 违反完整约束条件 (SCOTT.FK_MID) - 已找到子记录

除了这种方法外还可以设置级联操作子句

on delete cascade

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

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        );

这样删除父表中的数据，子表中的对应的关联字段将被设置为空

SQL> delete from member where mid=1;  已删除 1 行。  SQL> select * from advice;        ADID CONTENT         MID ---------- ---------- ---------- 　　　 test 　　　　test            2

查看约束

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

修改约束

增加约束

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的方式

禁用/启用约束

非外键

alter table member disable constraint ck_age; alter table member enable constraint ck_age;

外键

alter table member disable constraint pk_mid cascade;

删除约束

alter table member drop constraint pk_mid cascade; alter table member drop constraint ck_age;

以上就是sql完整性约束有哪些的详细内容，更多请关注名铺123其它相关文章！