Oracle--13、约束

约束是保证数据表中数据完整性的一种有效检测手段。在之前建立完数据表之后，可以发现里面能够随意的增加数据，例如：设置性别的时候，可以设置“不男不女”，在设置编号的时候，编号也可以重复，这些都是因为数据表缺少约束所导致的，那么在数据库之中约束严格来讲一共有6种约束：数据类型（可忽略）、非空约束、唯一约束、主键约束、检查约束、外键约束。



1、非空约束（NOT NULL，NK）

非空约束可以说是整个约束之中最特殊的一个，而且设置方式也与其他的四种约束不太一样，只需要在表创建的时候字段后面编写“NOT NULL”即可实现。

范例：非空约束

DROP TABLE member ;

CREATE TABLE member(

Mid  NUMBER,

Name VARCHAR2(20) NOT NULL

) ;

此时表示的是member表中的name字段的内容不允许设置为null.

范例：增加正确的数据

INSERT INTO member(mid,name) VALUES (1,’王小二’) ;

范例：增加错误的数据

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

INSERT INTO member(mid) VALUES (1) ;

此时，数据库会提示如下的错误信息：“ORA-01400  无法将NULL插入（’SCOTT’.’MEMBER’ ‘NAME’）”,此时会明确的显示出哪个字段上违反了非空约束。

 

 

2、唯一约束（UNIQUE，UK）

唯一约束指的是表中某一个列的数据不允许重复，例如：在一张用户表之中，会存在有email字段，那么每个人的email字段是不可能重复的，所以就要使用UNIQUE来表示。

范例：建立唯一约束

DROP TABLE member ;

CREATE TABLE member(

Mid  NUMBER,

Name VARCHAR2(20) NOT NULL,

Email VARCHAR2(20) UNIQUE

) ;

范例：增加正确的数据

INSERT INTO member (mid,name) VALUES (1,’王二小’) ;

INSERT INTO member (mid,name,email) VALUES (2,’王三小’,’san@sina.com’) ;

在使用唯一约束的时候一定要记住，null不属于重复的统计范畴。

范例：增加错误的数据

INSERT INTO member (mid,name,email) VALUES (2,’王四小’,’san@sina.com’) ;

此时出现了错误的提示信息：“ORA-00001 :违反唯一约束条件（SCOTT.SYS_COO111O1）”。但是与之前的非空约束相比，此时的提示信息非常不明确。

实际上在Oracle里面会将所有的约束也作为一个个对象保存，那么这些对象信息一定会在数据字典之中进行记录。所以此时出现的错误提示，只是数据字典之中的信息名称。使用“user_cons_columns”数据字典查看。

范例：查看数据字典

SELECT * FROM user_cons_columns ;

COL name FRO A15 ;//单独为name这一列格式化



发现，如果以后在开发之中真的出现了违反唯一约束的问题，那么还需要通过数据字典去查询信息，基本上要疯掉的感觉。所以在进行约束定义的时候，用户可以使用CONSTRAINT来手工设置约束的名称，而且约束的名称建议采用的方式“约束简写_字段名称”，例如，唯一约束是UNIQUE,这种唯一约束的简写为“UK”,那么在email字段上设置 的约束名称可以为：“UK_email” ；

范例：修改数据库创建脚本

DROP TABLE member PURGE;

CREATE TABLE member(

Mid  NUMBER,

Name VARCHAR2(20) NOT NULL,

Email VARCHAR2(20) ,

CONSTRAINT uk_email UNIQUE(email)

) ;

此时，如果保存的数据出现了错误，则提示信息“ORA-00001 违反唯一约束条件（SCOTT UK_EMAIL）”。

除了非空约束之外，所有的约束都一定要按照以上的方式进行设置，因为只有一个正常的名称，才方便系统进行约束的维护。

 


3、主键约束（PRIMARY KEY，PK）

主键约束 = 非空约束 + 唯一约束 ；例如，每一个公民的身份证号，一定是唯一的，而且一定不能是null。那么主键约束使用PRIMARY KEY表示。

范例：定义主键约束

DROP TABLE member PURGE;

CREATE TABLE member(

Mid  NUMBER,

Name VARCHAR2(20) NOT NULL,

CONSTRAINT pk_mid PRIMARY KEY(mid)

) ;

范例：添加正确的数据

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

范例：添加错误的数据——主键重复

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

错误提示：“ORA-00001 : 违反唯一约束条件（SCOTT PK_MID）”

范例：添加错误的数据——主键为空

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

错误提示：“ORA-01400 :无法将NULL插入（SCOTT MEMBER MID）”

 

提示：一般正常人来讲，在进行数据表设计的时候都会为每一张数据表至少设置一个主键或不设置，但是从数据表本身的设计角度出发，一张数据表也可以设置多个主键，多个主键就成为复合主键。只要多个主键不同时重复，就可以正确添加进表。

范例：观察符合主键

DROP TABLE member PURGE;

CREATE TABLE member(

Mid  NUMBER,

Name VARCHAR2(20),

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 (2,’李四’) ;

正常人都不用复合主键，知道就行，千万别用。

 



4、检查约束（CHECK，CK）

检查约束算是比较有意思的一种约束,检查约束是由用户自己来设置检查的过滤条件,例如:在保存年龄的的时候,年龄的范围:0~150;设置性别的时候,可选值:男、女。这样的方式都需要用户自己来指定。

范例：设置检查约束

DROP TABLE member PURGE;

CREATE TABLE member(

Mid  NUMBER,

Name VARCHAR2(20) NOT NULL,

Sex  VARCHAR2(4),

Age  NUMBER(3),

CONSTRAINT pk_mid PRIMARY KEY(mid),

CONSTRAINT ck_sex CHECK(sex IN (‘男’,’女’)),

CONSTRAINT ck_age CHECK(age BETWEEN 0 AND 150)

) ;

范例：增加错误数据

INSERT INTO member (mid,name,sex,age) VALUES (1,’王五’,’雌雄’,300) ;

即使设置了多个检查约束，那么也是一个一个约束进行过滤的。

但是在这里有一个提示：一般而言，设置的约束越多，在进行数据更新操作的时候性能就越低，所以很多时候一些数据的检查都会交给程序完成。

 



5、主外键约束（FROEIGN KEY,FK）

在解释主外键约束之前，首先来完成一个简单的程序，要求设计数据表，可以表示出：“一个人有多本书”的关系。编写数据库创建脚本，建立两张数据表。

DROP TABLE member PURGE ;

DROP TABLE book PURGE ;

CREATE TABLE member(

Mid  NUMBER,

Name VARCHAR2(20) NOT NULL,

CONSTRAINT pk_mid PRIMARY KEY(mid)

) ;

CREATE TABLE book(

bid  NUMBER,

title VARCHAR2(20) NOT NULL,

mid  NUMBER,

CONSTRAINT pk_bid PRIMARY KEY(bid)

) ;

范例：向表之中添加正确的数据

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

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

INSERT INTO book(bid,title,mid)VALUES(1001,’格林童话’,1) ;

INSERT INTO book(bid,title,mid)VALUES(1002,’一千零一夜’,1) ;

INSERT INTO book(bid,title,mid)VALUES(1003,’大豆子’,2) ;

INSERT INTO book(bid,title,mid)VALUES(1004,’买女孩的小火柴’,2) ;

之所以说这些是正确的数据，是因为在book表之中mid保存的数据，member表里面都有。或者下面进行查询，统计每个人的图书数量。

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

FROM member m,book b

WHERE m.mid = b.mid

GROUP BY m.mid,m.name ;

以上都是正常的数据，但是由于此时缺少了数据验证的支持，那么下面的数据也可以保存。

范例：错误的数据

INSERT INTO book(bid,title,mid)VALUES(2001,’冒险王’,9) ;

INSERT INTO book(bid,title,mid)VALUES(2002,’机器猫’,9) ;

现在member表里面根本就没有9号成员，所以此数据一定是非法数据。而现在的问题也就清楚了，在两张数据表之中，子表（book）的某个字段（book.mid）应该与父表（member）的主键或唯一约束的字段（mid）有关联。

范例：建立外键关联

DROP TABLE member PURGE ;

DROP TABLE book PURGE ;

CREATE TABLE member(

Mid  NUMBER,

Name VARCHAR2(20) NOT NULL,

CONSTRAINT pk_mid PRIMARY KEY(mid)

) ;

CREATE TABLE book(

bid  NUMBER,

title VARCHAR2(20) NOT NULL,

mid  NUMBER,

CONSTRAINT pk_bid PRIMARY KEY(bid),

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

) ;

那么此时，如果再增加错误的数据，就会出现错误提示：“ORA-02291 ： 违反完整约束条件（SCOTT.FK_MID） - 未找到父项关键字” 。由于member表里面没有指定的数据，所以就出现了错误。

清楚了外键的关联操作之后，那么下面还有几个相应的限制：

限制一：在删除主表之前，一定要保证删除子表。所以在删除数据表的时候应该先删除子表  后在删除父表。

DROP TABLE book PURGE ;

DROP TABLE member PURGE ;

有些时候如果接触到的是一个不熟悉的数据库，那么又需要快速删除一张表的话，则可以使用强制删除。

范例：强制删除

DROP TABLE member CASCADE CONSTRAINT ;

但是必须注意的事，强制删除是无法彻底删除的，所删除的数据表会保存在回收站之中。但是不建议使用强制删除的操作，还是建议按照先删除子表再删除父表的方式进行。

 

限制二：作为外键的字段在主表之中必须具备主键或者是唯一约束。

限制三：数据的级联问题。

·在删除主表数据的时候，如果存在有相应的子表数据，那么主表数据无法被删除。

DELETE FROM member WHERE mid=2 ;

ORA-02292: 违反完整约束条件（SCOTT.FK_MID）- 已找到子记录

因为现在book标存在mid=2的外键数据，所以此时无法删除。

·为了方便删除数据，提供有一个级联删除操作，在主表数据删除的时候对应的子表数据会同时删除掉，在建立外键的时候使用ON DELETE CASCADE来设置。

范例：设置级联删除

DROP TABLE member PURGE ;

DROP TABLE book PURGE ;

CREATE TABLE member(

Mid  NUMBER,

Name VARCHAR2(20) NOT NULL,

CONSTRAINT pk_mid PRIMARY KEY(mid)

) ;

CREATE TABLE book(

bid  NUMBER,

title VARCHAR2(20) NOT NULL,

mid  NUMBER,

CONSTRAINT pk_bid PRIMARY KEY(bid),

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

) ;

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

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

INSERT INTO book(bid,title,mid)VALUES(1001,’格林童话’,1) ;

INSERT INTO book(bid,title,mid)VALUES(1002,’一千零一夜’,1) ;

INSERT INTO book(bid,title,mid)VALUES(1003,’大豆子’,2) ;

INSERT INTO book(bid,title,mid)VALUES(1004,’买女孩的小火柴’,2) ;

DELETE FROM member WHERE mid=1 ; 对应子表中数据也删除了

· 如果在删除主表数据的时候不希望子表数据被一起删除掉，则可以使用级联更新操作，使用ON DELETE SET NULL来设置。

范例：设置级联更新

DROP TABLE member PURGE ;

DROP TABLE book PURGE ;

CREATE TABLE member(

Mid  NUMBER,

Name VARCHAR2(20) NOT NULL,

CONSTRAINT pk_mid PRIMARY KEY(mid)

) ;

CREATE TABLE book(

bid  NUMBER,

title VARCHAR2(20) 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(1001,’格林童话’,1) ;

INSERT INTO book(bid,title,mid)VALUES(1002,’一千零一夜’,1) ;

INSERT INTO book(bid,title,mid)VALUES(1003,’大豆子’,2) ;

INSERT INTO book(bid,title,mid)VALUES(1004,’买女孩的小火柴’,2) ;

DELETE FROM member WHERE mid=1 ; 对应子表外键为null

但是对于数据的级联关系，没有一个明确的使用原则，根据需要来决定。

 



6、修改约束

首先明确：所有的约束一定要在表建立的同时就已经设置完成，不可能说表先去使用之后再回头设置约束，所以以下的这些操作没有任何的实际意义，只能够作为知识点了解以下。

本次操作使用如下数据库脚本。

CREATE TABLE member(

Mid  NUMBER,

Name VARCHAR2(20) NOT NULL,

) ;

INSERT INTO member(mid,name)VALUES(1,NULL) ;

INSERT INTO member(mid,name)VALUES(2,’张三’) ;

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

此时数据表之中没有任何的约束，而且保存的数据也都违反了约束。

 

1、为表增加约束

ALTER TABLE 表名称 ADD CONSTRAINT 约束名称 约束类型（约束字段）[选项]  ;

范例：为表添加约束

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

如果此时member表中的mid字段里面包含有违反约束的数据，那么约束无法添加。删除数据后添加约束。

但是需要提醒的是，此类的语法适合于：唯一、主键、检查、外键约束的添加，而无法添加非空约束。

ALTER TABLE member ADD CONSTRAINT nk_name NOT NULL(name) ;//会报错

ALTER TABLE member MODIFY(name VARCHAR2(20) NOT NULL) ;

 

 

2、删除约束

ALTER TABLE 表名称 DROP CONSTRAINT 约束名称 ;

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

ALTER TABLE member DROP CONSTRAINT pk_mid ;

所有修改表的操作你都可以忽略，都可以不会。





oracle

2020.11.18 20:50

https://www.meihaocloud.com.com/233.html ， 欢迎转载，请在文章页标出原文连接 ！

Copyright © 2020 千夕网 联系站长

粤公网安备 44030302001408号 粤ICP备19099833号-1