Oracle之完整性约束

一. 完整性约束简介

1. 完整性约束：

2. 维护完整性约束：

3. 主要约束分类：

二. 非空约束：NK

在正常情况下，NULL是每个属性的合法数据值。如果现在某个字段不能为空，且必须存在数据。那么就可依靠非空约束来进行控制

--DROP TABLE member PURGE;

CREATE TABLE member (
    mid    NUMBER,
    name   VARCHAR2(200)  NOT NULL
);

插入数据：

#--正确
INSERT INTO member(mid,name) VALUES(1,'悟空');

#--错误
INSERT INTO member(mid) VALUES(2);

#错误信息：
SQL 错误: ORA-01400: 无法将 NULL 插入 ("C##SCOTT"."MEMBER"."NAME")

对于此处的错误信息，可以发现由‘用户名’，‘表名称’，‘字段名称’ 三部分组成。已经准确的告诉了用户那里有问题

三. 唯一约束：UK

唯一约束：表示在表中的数据不允许出现重复的情况。

1. 创建UK

DROP TABLE member PURGE;

CREATE TABLE member (
     mid    NUMBER,
     name   VARCHAR2(100)  NOT NULL,
     email  VARCHAR2(50)   UNIQUE
);

2. 插入数据：

#---正确
INSERT INTO member(mid,name,email) VALUES(1,'悟空','wk@163.com');

#---错误
INSERT INTO member(mid,name,email) VALUES(2,'如来','wk@163.com');

#错误信息：
SQL 错误: ORA-00001: 违反唯一约束条件 (C##SCOTT.SYS_C0010064)

此时的错误信息与之前的非空约束的错误信息相比，不够详细。因为约束在数据库之中也是一个对象，所以为了方便维护，那么每一个约束都有一个自己的名字。如果用户没有指定名字，那么就将系统动态分配一个

3. 可以使用 CONSTRAINT 关键字来为约束定义名字

建议格式：约束简写_字段（约束简写_表_字段）

3.1 创建

DROP TABLE member PURGE;
CREATE TABLE member (
     mid    NUMBER,
     name   VARCHAR2(100)  NOT NULL,
     email  VARCHAR2(50),
     CONSTRAINT uk_email UNIQUE(email)
);

3.2 插入错误数据

#---错误信息（详细）
INSERT INTO member(mid,name,email) VALUES(2,'如来','wk@163.com')
错误报告:
SQL 错误: ORA-00001: 违反唯一约束条件 (C##SCOTT.UK_EMAIL)

注意：唯一约束本身不受 null类型控制的（email 可没有）。

四. 主键约束：PK

主键约束 = 非空约束 + 唯一约束。主键约束使用 PRIMARY KEY（简称PK），进行制定。

DROP TABLE member PURGE;

CREATE TABLE member (
     mid    NUMBER,
     name   VARCHAR2(100)  NOT NULL,
     email  VARCHAR2(50),

     CONSTRAINT pk_mid PRIMARY KEY(mid),    #PRIMART KEY
     CONSTRAINT uk_email UNIQUE(email)
);

五. 检查约束：CK

DROP TABLE member PURGE;

CREATE TABLE member (
     mid    NUMBER,
     name   VARCHAR2(100)  NOT NULL,
     email  VARCHAR2(50),
     age    NUMBER         CHECK(age BETWEEN 0 AND 200),    #CK
     sex    VARCHAR2(10),
     CONSTRAINT pk_mid PRIMARY KEY(mid),
     CONSTRAINT uk_email UNIQUE(email),
     CONSTRAINT ck_sex   CHECK(sex in('男','女'))                 #CK
);

六. 主-外键约束：FK

外键约束的产生分析：

1.创建

DROP TABLE member PURGE;
DROP TABLE advice PURGE;

CREATE TABLE member (
     mid    NUMBER,
     name   VARCHAR2(100)  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)
);

2. 插入数据

INSERT INTO member(mid,name) VALUES(1,'悟空');
INSERT INTO member(mid,name) VALUES(2,'哪吒');

INSERT INTO advice(adid,content,mid) VALUES(1,'紧箍咒，拿掉',1);
INSERT INTO advice(adid,content,mid) VALUES(2,'路上妖精，有的如此厉害',1);

INSERT INTO advice(adid,content,mid) VALUES(3,'宝塔，震慑',2);
INSERT INTO advice(adid,content,mid) VALUES(4,'路上妖精，有的如此厉害',99);#错误数据

3. 如果要删除父表数据，需删除对应的所有子表数据。若子表数据过多，费时间。

为了解决外键中的数据的操作问题，就出现了数据的级联操作。

3.1 级联删除：ON DELETE CASCADE,当主表数据被删除之后，对应的子表数据也同时删除

DROP TABLE advice PURGE;           #先删子表
DROP TABLE member PURGE;        #再删主表

CREATE TABLE member (
     mid    NUMBER,
     name   VARCHAR2(100)  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) ON DELETE CASCADE   #ON DELETE CASCADE
);

表创建

delete from member where mid=1;

3.2 级联更新，ON DELETE SET NULL

当主表数据被删除后，对应的子表数据的相应字段的内容，会设置为 null。

表创建：

DROP TABLE advice PURGE;
DROP TABLE member PURGE;

CREATE TABLE member (
     mid    NUMBER,
     name   VARCHAR2(100)  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) ON DELETE SET NULL      #此行
);

View Code

数据：

INSERT INTO member(mid,name) VALUES(1,'悟空');
INSERT INTO member(mid,name) VALUES(2,'哪吒');

INSERT INTO advice(adid,content,mid) VALUES(1,'紧箍咒，拿掉',1);
INSERT INTO advice(adid,content,mid) VALUES(2,'路上妖精，有的如此厉害',1);
INSERT INTO advice(adid,content,mid) VALUES(3,'宝塔，震慑',2);
INSERT INTO advice(adid,content,mid) VALUES(4,'路上',1);

View Code

删除主表member的 mid=1：

delete from member where mid=1;

子表advice的显示：

#总结：
1.如何选择级联操作，看实际需求。

2. 使用外键约束后删除表，应先删除子表再删除主表

3. 在进行外键设置的时候，对应的字段，在主表中必须是主键或者是唯一约束

七. 查看约束

约束是由数据库自己创建的对象，所有对象都会在数据字典之中进行保存，可以利用“user_constraints”数据字典或者

“user_cons_columns”数据字典查看。

1. 主键位置名称

CREATE TABLE member (
      mid   NUMBER PRIMARY KEY,
      name  VARCHAR2(20) NOT NULL
);

2. 插入数据

#-- 正确
INSERT INTO member(mid,name) VALUES(1,'悟空');

#--错误
INSERT INTO member(mid,name) VALUES(1,'如来');

此时的错误信息：SQL 错误: ORA-00001: 违反唯一约束条件 (C##SCOTT.SYS_C0010113)。SYS_C0010113，就是数据库对象（约束）的名称

3.查看约束名称：SYS_C0010113

select * from user_constraints;

4. 现在只是知道了约束名称，而不知道字段是哪一个

select * from user_cons_columns;

#从开发角度来讲，约束名称一定要有。

八. 修改约束

表建立之后，尽量不要去修改。对于约束也一样，约束跟表一起建立，那么建立之后不要修改了

posted @ 2018-06-26 22:41 shadow3 阅读(636) 评论(0) 收藏举报

刷新页面返回顶部

shadow3

Oracle之完整性约束

公告