Oracle基础知识笔记（10） 约束

时间：2023-03-09 00:22:59
Oracle基础知识笔记（10） 约束

表尽管建立完毕了，可是表中的数据是否合法并不能有所检查，而假设要想针对于表中的数据做一些过滤的话，则能够通过约束完毕，约束的主要功能是保证表中的数据合法性，依照约束的分类，一共同拥有五种约束：非空约束、唯一约束、主键约束、检查约束、外键约束。

一、非空约束（NOT NULL）：NK

当数据表中的某个字段上的内容不希望设置为null的话，则能够使用NOT NULL进行指定。

范例：定义一张数据表

DROP TABLE member PURGE;

CREATE TABLE member(

    mid NUMBER,

    name VARCHAR2(50) NOT NULL

);

由于此时存在了“NOT NULL”约束，所以以下插入两组数据。

范例：正确的数据

INSERT INTO member(mid,name) VALUES(1,'张三');

INSERT INTO member(mid,name) VALUES(null,'李四');

INSERT INTO member(name) VALUES('王五');

范例：插入错误的数据

INSERT INTO member(mid,name) VALUES(9,null);

INSERT INTO member(mid) VALUES(10);

此时了出现的错误提示：

ORA-01400: 无法将 NULL 插入 ("SCOTT"."MEMBER"."NAME")

本程序之中，直接表示出了“用户”.“表名称”.“字段”出现了错误。

二、唯一约束（UNIQUE）：UK

唯一约束指的是每一列上的数据是不同意反复的，比如：email地址每一个用户肯定是不反复的，那么就使用唯一约束完毕。

DROP TABLE member PURGE;

CREATE TABLE member(

    mid NUMBER,

    name VARCHAR2(50) NOT NULL,

    email VARCHAR2(50) UNIQUE

);

范例：插入正确的数据

INSERT INTO member(mid,name,email) VALUES(1,'张三','mchina_tang@qq.com');

INSERT INTO member(mid,name,email) VALUES(2,'李四',null);

范例：插入错误的数据 —— 反复数据

INSERT INTO member(mid,name,email) VALUES(3,'王五','mchina_tang@qq.com');

此时会出现例如以下的错误提示：

ORA-00001: 违反唯一约束条件 (SCOTT.SYS_C005272)

但是这个时候的错误提示与之前的非空约束相比并不完好，由于如今仅仅是给出了一个代号而已，这是由于在定义约束的时候没有为约束指定一个名字，所以由系统默认分配了，并且约束的名字建议的格式“约束类型_字段”，比如：“UK_email”，指定约束名称使用CONSTRAINT完毕。

Oracle基础知识笔记（10） 约束
DROP TABLE member PURGE;

CREATE TABLE member(

    mid NUMBER,

    name VARCHAR2(50) NOT NULL,

    email VARCHAR2(50),

    CONSTRAINT UK_email UNIQUE(email)

);
Oracle基础知识笔记（10） 约束

以后再次添加错误数据时，提示信息例如以下：

ORA-00001: 违反唯一约束条件 (SCOTT.UK_EMAIL)

已经能够非常明白的提示用户错误的位置。

三、主键约束（Primary Key）：PK

主键约束 = 非空约束 + 唯一约束，在之前设置唯一的约束的时候发现能够设置为null，而假设如今使用了主键约束之后则不能为空，并且主键一般作为数据的唯一的一个标记出现，比如：人员的ID。

范例：建立主键约束

DROP TABLE member PURGE;

CREATE TABLE member(

    mid NUMBER PRIMARY KEY,

    name VARCHAR2(50) NOT NULL

);

范例：添加正确的数据

INSERT INTO member(mid,name) VALUES(1,'张三');

范例：错误的数据 —— 主键设置为null

INSERT INTO member(mid,name) VALUES(null,'张三');

错误信息，与之前的非空约束的错误信息提示是一样的；

ORA-01400: 无法将 NULL 插入 ("SCOTT"."MEMBER"."MID")

范例：错误的数据 —— 主键反复

INSERT INTO member(mid,name) VALUES(1,'张三');

错误信息，这个错误信息就是唯一约束的错误信息，可是信息不明白，由于没起名字。

ORA-00001: 违反唯一约束条件 (SCOTT.SYS_C005276)

所以为了约束的使用方便，以下为主键约束起一个名字。

DROP TABLE member PURGE;

CREATE TABLE member(

    mid NUMBER,

    name VARCHAR2(50) NOT NULL,

    CONSTRAINT pk_mid PRIMARY KEY(mid)

);

此时，反复插入数据，则错误信息例如以下：

ORA-00001: 违反唯一约束条件 (SCOTT.PK_MID)

从正常的开发角度而言，一张表一般都仅仅设置一个主键，可是从SQL语法的规定而言，一张表却能够设置多个主键，而此种做法称为复合主键，比如：參考例如以下代码：

DROP TABLE member PURGE;

CREATE TABLE member(

    mid NUMBER,

    name VARCHAR2(50) NOT NULL,

    CONSTRAINT pk_mid PRIMARY KEY(mid,name)

);

在复合主键的使用之中，仅仅有两个字段的内容都一样的情况下，才被称为反复数据。

范例：插入正确的数据

INSERT INTO member(mid,name) VALUES(1,'张三');

INSERT INTO member(mid,name) VALUES(1,'李四');

INSERT INTO member(mid,name) VALUES(2,'李四');

范例：插入错误的数据

INSERT INTO member(mid,name) VALUES(1,'张三');

错误信息：

ORA-00001: 违反唯一约束条件 (SCOTT.PK_MID)

可是从开发的实际角度而言，一般都不使用复合主键，所以这个知识仅仅是作为其相关的内容做一个介绍。仅仅要是数据表，永远都仅仅设置一个主键。

四、检查约束（Check）：CK

检查约束指的是为表中的数据添加一些过滤条件，比如：

  • 设置年龄的时候范围是：0~200；
  • 设置性别的时候应该是：男、女；

范例：设置检查约束

Oracle基础知识笔记（10） 约束
DROP TABLE member PURGE;

CREATE TABLE member(

    mid NUMBER,

    name VARCHAR2(50) NOT NULL,

    sex VARCHAR2(10) NOT NULL,

    age NUMBER(3),

    CONSTRAINT pk_mid PRIMARY KEY(mid),

    CONSTRAINT ck_sex CHECK(sex IN('男','女')),

    CONSTRAINT ck_age CHECK(age BETWEEN 0 AND 200)

);
Oracle基础知识笔记（10） 约束

范例：添加正确的数据

INSERT INTO member(mid,name,sex,age) VALUES(1,'张三','男','26');

范例：添加错误的性别 —— ORA-02290: 违反检查约束条件 (SCOTT.CK_SEX)

INSERT INTO member(mid,name,sex,age) VALUES(2,'李四','非','26');

范例：添加错误的年龄 —— ORA-02290: 违反检查约束条件 (SCOTT.CK_AGE)

INSERT INTO member(mid,name,sex,age) VALUES(2,'李四','女','260');

检查的操作就是对输入的数据进行一个过滤。

五、主-外键约束

之前的四种约束都是在单张表中进行的，而主-外键约束是在两张表中进行的，这两张表是存在父子关系的，即：子表中某个字段的取值范围由父表所决定。

比如，如今要求表示出一种关系，每个人有多本书，应该定义两张数据表：member（主）、book（子）；

Oracle基础知识笔记（10） 约束
DROP TABLE member PURGE;

DROP TABLE book PURGE;

CREATE TABLE member(

    mid NUMBER,

    name VARCHAR2(50) NOT NULL,

    CONSTRAINT pk_mid PRIMARY KEY(mid)

);

CREATE TABLE book(

    bid NUMBER,

    title VARCHAR2(50) NOT NULL,

    mid NUMBER,

    CONSTRAINT pk_bid PRIMARY KEY(bid)

);
Oracle基础知识笔记（10） 约束

此时仅仅是依据要求建立了两张独立的数据表，那么以下插入几条数据：

Oracle基础知识笔记（10） 约束
INSERT INTO member(mid,name) VALUES(1,'张三');

INSERT INTO member(mid,name) VALUES(2,'李四');

INSERT INTO book(bid,title,mid) VALUES(101,'Java开发',1);

INSERT INTO book(bid,title,mid) VALUES(102,'Java Web开发',2);

INSERT INTO book(bid,title,mid) VALUES(103,'EJB开发',2);

INSERT INTO book(bid,title,mid) VALUES(105,'Android开发',1);

INSERT INTO book(bid,title,mid) VALUES(107,'AJAX开发',1);
Oracle基础知识笔记（10） 约束

要想验证这个数据是否有意义，最简单的做法，就是写两个查询。

范例：统计每一个人员拥有书的数量

SELECT m.mid,m.name,COUNT(b.bid)

FROM member m,book b

WHERE m.mid=b.mid

GROUP BY m.mid,m.name;

范例：查询出每一个人员的编号，姓名，拥有书的名称

SELECT m.mid,m.name,b.title

FROM member m,book b

WHERE m.mid=b.mid;

即，如今的book.mid字段应该是与member.mid字段相关联的，可是因为本程序没有设置约束，所以，如今下面的数据也是能够添加的：

INSERT INTO book(bid,title,mid) VALUES(108,'PhotoShop使用手冊',3);

INSERT INTO book(bid,title,mid) VALUES(109,'FLEX开发手冊',8);

如今添加了两条新的记录，并且记录能够保存在数据表之中，可是这两条记录没有意义，由于member.mid字段的内容没有3和8，而要想解决问题就必须依靠外键约束来解决。

让book.mid的字段的取值由member.mid所决定，假设member.mid的数据真实存在，则表示能够更新。

Oracle基础知识笔记（10） 约束
DROP TABLE member PURGE;

DROP TABLE book PURGE;

CREATE TABLE member(

    mid NUMBER,

    name VARCHAR2(50) NOT NULL,

    CONSTRAINT pk_mid PRIMARY KEY(mid)

);

CREATE TABLE book(

    bid NUMBER,

    title VARCHAR2(50) NOT NULL,

    mid NUMBER,

    CONSTRAINT pk_bid PRIMARY KEY(bid),

    CONSTRAINT fk_mid FOREIGN KEY(mid) REFERENCES member(mid)

);
Oracle基础知识笔记（10） 约束

此时，仅仅是添加了一个约束，这样一来假设输入的数据有错误，则会出现例如以下的提示：

ORA-02291: 违反完整约束条件 (SCOTT.FK_MID) - 未找到父项keyword

由于member.mid没有指定的数据，所以book.mid假设数据有错误，则无法运行更新操作。

使用外键的最大优点是控制了子表中某些数据的取值范围，可是相同带来了不少的问题；

1、  删除数据的时候，假设主表中的数据有相应的子表数据，则无法删除；

范例：删除member表中mid为1的数据

DELETE FROM member WHERE mid=1;

错误提示信息：“ORA-02292: 违反完整约束条件 (SCOTT.FK_MID) - 已找到子记录”。

此时，仅仅能先删除子表记录，之后再删除父表记录：

DELETE FROM book WHERE mid=1;

DELETE FROM member WHERE mid=1;

可是这样的操作明显不方便，假设说如今希望主表数据删除之后，子表中相应的数据也能够删除的话，则能够在建立外键约束的时候指定一个级联删除的功能，改动数据库创建脚本：

Oracle基础知识笔记（10） 约束
DROP TABLE member PURGE;

DROP TABLE book PURGE;

CREATE TABLE member(

    mid NUMBER,

    name VARCHAR2(50) NOT NULL,

    CONSTRAINT pk_mid PRIMARY KEY(mid)

);

CREATE TABLE book(

    bid NUMBER,

    title VARCHAR2(50) NOT NULL,

    mid NUMBER,

    CONSTRAINT pk_bid PRIMARY KEY(bid),

    CONSTRAINT fk_mid FOREIGN KEY(mid) REFERENCES member(mid) ON DELETE CASCADE

);
Oracle基础知识笔记（10） 约束

此时因为存在级联删除的操作，所以主表中的数据删除之后，相应的子表中的数据也都会被同一时候删除。

2、  删除数据的时候，让子表中相应的数据设置为null

当主表中的数据删除之后，相应的子表中的数据相关项也希望将其设置为null，而不是删除，此时，能够继续改动数据表的创建脚本：

Oracle基础知识笔记（10） 约束
DROP TABLE member PURGE;

DROP TABLE book PURGE;

CREATE TABLE member(

    mid NUMBER,

    name VARCHAR2(50) NOT NULL,

    CONSTRAINT pk_mid PRIMARY KEY(mid)

);

CREATE TABLE book(

    bid NUMBER,

    title VARCHAR2(50) NOT NULL,

    mid NUMBER,

    CONSTRAINT pk_bid PRIMARY KEY(bid),

    CONSTRAINT fk_mid FOREIGN KEY(mid) REFERENCES member(mid) ON DELETE SET NULL

);

INSERT INTO member(mid,name) VALUES(1,'张三');

INSERT INTO member(mid,name) VALUES(2,'李四');

INSERT INTO book(bid,title,mid) VALUES(101,'Java开发',1);

INSERT INTO book(bid,title,mid) VALUES(102,'Java Web开发',2);

INSERT INTO book(bid,title,mid) VALUES(103,'EJB开发',2);

INSERT INTO book(bid,title,mid) VALUES(105,'Android开发',1);

INSERT INTO book(bid,title,mid) VALUES(107,'AJAX开发',1);
Oracle基础知识笔记（10） 约束

3、  删除父表之前必须首先先删除相应的子表，否则无法删除

DROP TABLE book PURGE;

DROP TABLE member PURGE;

可是这样做明显非常麻烦，由于对于一个未知的数据库，假设要依照此类方式进行，则必须首先知道其父子关系，所以在Oracle之中专门提供了一个强制性删除表的操作，即：不再关心约束，在删除的时候写上一句“CASCADE CONSTRAINT”。

DROP TABLE member CASCADE CONSTRAINT PURGE;

DROP TABLE book CASCADE CONSTRAINT PURGE;

此时，不关心子表是否存在，直接强制性的删除父表。

合理做法：在以后进行数据表删除的时候，最好是先删除子表，之后再删除父表。

六、改动约束

约束本身也属于数据库对象，那么也肯定能够进行改动操作，并且仅仅要是改动都使用ALTER指令，约束的改动主要指的是下面两种操作：

  • 为表添加约束：
ALTER TABLE 表名称 ADD CONSTRAINT 约束名称 约束类型(字段);
  • 删除表中的约束：
ALTER TABLE 表名称 DROP CONSTRAINT 约束名称;

能够发现，假设要维护约束，肯定须要一个正确的名字才干够，但是在这五种约束之中，非空约束作为一个特殊的约束无法操作，如今有例如以下一张数据表：

DROP TABLE member CASCADE CONSTRAINT PURGE;

CREATE TABLE member(

    mid NUMBER,

    name VARCHAR2(50) NOT NULL,

    age NUMBER(3)

);

范例：为表中添加主键约束

ALTER TABLE member ADD CONSTRAINT pk_mid PRIMARY KEY(mid);

添加数据：

INSERT INTO member(mid,name,age) VALUES(1,'张三',30);

INSERT INTO member(mid,name,age) VALUES(2,'李四',300);

如今在member表中已经存在了年龄上的非法数据，所以以下为member表添加检查约束：

ALTER TABLE member ADD CONSTRAINT ck_age CHECK(age BETWEEN 0 AND 250);

这个时候在表中已经存在了违反约束的数据，所以肯定无法添加。

范例：删除member表中的mid上的主键约束

ALTER TABLE member DROP CONSTRAINT pk_mid;

但是，跟表结构一样，约束最好也不要改动，并且记住，表建立的同一时候一定要将约束定义好，以后的使用之中建议就不要去改变了。

七、查询约束

在Oracle之中全部的对象都会在数据字典之中保存，而约束也是一样的，所以假设要想知道有哪些约束，能够直接查询“user_constraints”数据字典：

SELECT owner,constraint_name,table_name FROM user_constraints;

可是这个查询出来的约束仅仅是告诉了你名字，而并没有告诉在哪个字段上有此约束，所以此时能够查看另外一张数据字典表“user_cons_columns”；

COL owner FOR A15;

COL constraint_name FOR A15;

COL table_name FOR A15;

COL column_name FOR A15;

SELECT owner,constraint_name,table_name,column_name FROM user_cons_columns;

这些维护工作大部分由专门的DBA负责。

相关文章