sql完整性约束有哪些
完整性约束是保证用户所做的修改不会破坏数据的一致性,是保护数据正确性和相容性的一种手段。
常见的5种约束:
NOT NULL:非空约束C,指定的列不允许为空值
UNIQUE:唯一约束U,指定的列中没有重复值,或该表中每一个值或者每一组值都将是唯一的
PRIMARY KEY:主键约束P,唯一的标识出表的每一行,且不允许空值,一个表只能有一个主键约束
FOREIGN KEY:外键约束R,一个表中的列引用了其它表中的列,使得存在依赖关系,可以指向引用自身的列
CHECK:条件约束C,指定该列是否满足某个条件
非空约束 NKcreate 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") 唯一约束 UKdrop 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)检查约束 CKcreate 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)主外键约束FKcreate 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 cascadecreate 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 nullcreate 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 CONTENTMID---------- ---------- ---------- test test2查看约束SQL>select constraint_name,constraint_type,table_namefrom user_constraints; CONSTRAINT_NAMEC TABLE_NAME------------------------------ - ------------------------------FK_DEPTNOR EMPPK_EMPP EMPPK_DEPTP DEPTR外键约束,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完整性约束有哪些的详细内容,更多请关注小潘博客其它相关文章!