Oracle约束

表虽然建好了，但是表中的数据是否合法并不能有所检查，如果想针对表中的数据做一些过滤的话，则可以通过约束完成，以保证数据的合法性。

一个有五种：非空约束、唯一约束、主键约束、检查约束、外键约束。

非空约束 ：NK

列名 数据类型 [DEFAULT ]  NOT  NULL ;

DROP TABLE mumber PURGE;

CREATE TABLE mumber (

mid NUMBER,

name VARCHAR2(50) NOT NULL

);

INSERT INTO mumber (mid,name) VALUES(1,'中国');

INSERT INTO mumber (mid,name) VALUES(2,'美国');

INSERT INTO mumber (mid,name) VALUES(3,'日本');

INSERT INTO mumber (mid,name) VALUES(4,null);

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

 唯一约束： （UNIQUE）UK

email VARCHAR2(50) UNIQUE;

错误信息少，因为没有为约束指定一个名字。可以使用一下CONSTRAINT完成

CONSTRAINT UK_email UNIQUE(email);

DROP TABLE mumber PURGE;

CREATE TABLE mumber (

mid NUMBER,

name VARCHAR2(50) NOT NULL,

email VARCHAR2(30) UNIQUE

);

INSERT INTO mumber (mid,name,email) VALUES(1,'中国','[e-mail]');

INSERT INTO mumber (mid,name,email) VALUES(2,'中国','[e-mail]');

错误：ORA-00001: 违反唯一约束条件 (SCOTT.SYS_C005284)

DROP TABLE mumber PURGE;

CREATE TABLE mumber (

mid NUMBER,

name VARCHAR2(50) NOT NULL,

email VARCHAR2(30),

CONSTRAINT UK_email UNIQUE(email)

);

再次插入数据

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

主键约束（Primary Key）：PK

主键约束 = 非空约束 + 唯一约束。作为数据的唯一标识，如人员的ID

一般一个表只设置一个主键。两个主键称为复合主键。

DROP TABLE mumber PURGE;

CREATE TABLE mumber (

mid NUMBER PRIMARY KEY,

name VARCHAR2(50) NOT NULL

);

INSERT INTO mumber (mid,name) VALUES(1,'中国');

INSERT INTO mumber (mid,name) VALUES(1,'美国');

错误：ORA-00001: 违反唯一约束条件 (SCOTT.SYS_C005288)

设置为：

DROP TABLE mumber PURGE;

CREATE TABLE mumber (

mid NUMBER,

name VARCHAR2(50) NOT NULL,

CONSTRAINT UK_mid PRIMARY KEY(mid)

);

插入报错： ORA-00001: 违反唯一约束条件 (SCOTT.UK_MID)

复合主键设置: 只有多个主键都重复才叫重复数据

CREATE TABLE mumber (

mid NUMBER,

name VARCHAR2(50) NOT NULL,

CONSTRAINT UK_mid_name PRIMARY KEY(mid,name)

);

检查约束：（Check）CK

检查为表中的数据增加一些过滤条件，

例如：设置年龄范围 0-250，性别只能是男女

DROP TABLE mumber PURGE;

CREATE TABLE mumber (

mid NUMBER,

name VARCHAR2(50) NOT NULL,

sex VARCHAR2(50),

age NUMBER,

CONSTRAINT UK_mid PRIMARY KEY(mid),

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

CONSTRAINT CK_age CHECK(age BETWEEN 0 AND 250)

INSERT INTO mumber (mid,name,sex,age) VALUES(1,'凤姐','人妖',200);

ORA-02290: 违反检查约束条件 (SCOTT.CK_SEX)

    );

主-外键约束

在多表中进行的关联，比如一个人（主表）又多本书（父表），书只能属于一个人，也就是说字表中的字段取值只能由父表决定。

首先建立两张表：

DROP TABLE mumber PURGE;

DROP TABLE book PURGE;

CREATE TABLE mumber (

mid NUMBER,

name VARCHAR2(50) NOT NULL,

CONSTRAINT PK_mid PRIMARY KEY(mid)

);

CREATE TABLE book(

bid NUMBER,

name VARCHAR2(50) NOT NULL,

mid NUMBER,

CONSTRAINT PK_bid PRIMARY KEY(bid)

) ;

添加数据

INSERT INTO mumber (mid,name) VALUES(1,'aaa');

INSERT INTO book(bid,name,mid) VALUES(31,'java',2);

此时book表中的mid可以取任意值，不符合规择，在book表中添加外键约束，使book.mid 只能是mumber表中的mid

创建book表时在后面添加：

CONSTRAINT FK_mid FOREIGN KEY(mid) REFERENCES mumber(mid);

再次在book表中添加父表没有的字段 报错：

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

引入外键约束后同时也带来几个问题：

1.删除父表数据的时候，如果有字表与之对应，则不能删除

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

DELETE FROM mumber WHERE mid = 1;

此时只能先删除字表中的数据。

不方便，因为删除记录的时候还有顺清表关系。

解决方法：

(1)删除父表字段的时候把字表对应的内容也删除

在建立字表book的时候使用：

CONSTRAINT FK_mid FOREIGN KEY(mid) REFERENCES mumber(mid) ON DELETE CASCADE;

DROP TABLE book PURGE;

DROP TABLE mumber PURGE;

CREATE TABLE mumber (

mid NUMBER,

name VARCHAR2(50) NOT NULL,

CONSTRAINT PK_mid PRIMARY KEY(mid)

);

CREATE TABLE book(

bid NUMBER,

name VARCHAR2(50) NOT NULL,

mid NUMBER,

CONSTRAINT PK_bid PRIMARY KEY(bid),

CONSTRAINT FK_mid FOREIGN KEY(mid) REFERENCES mumber(mid) ON DELETE CASCADE

) ;

此时删除父表中的字段的时候子表中的字段也会删除。

(2)删除父表字段的时候把字表对应字段设为null

CONSTRAINT FK_mid FOREIGN KEY(mid) REFERENCES mumber(mid) ON DELETE SET NULL

2.删除表时要删除字表，否则无法删除。

DROP TABLE mumber PURGE;

第 1 行出现错误:

ORA-02449: 表中的唯一/主键被外键引用

解决办法：在删除语句后面加上CASCADE CONSTRAINT

DROP TABLE mumber CASCADE CONSTRAINT PURGE;

此时不关心字表是不是存在，直接强制性删除父表，但此时字表中的数据还存在。

合理做法：先删除字表，再删除父表。

修改约束（了解）

约束也属于数据库对象，也是用ALTER指定

为表添加约束：

ALTER TABLE 表名称 ADD CONSTRAINT 约束名称，约束类型（字段）

如果表中已经存在非法数据，则无法添加约束。

删除约束：

ALTER TABLE 表名称 DROP CONSTRAINT 约束名称

非空约束作为一个特殊的约束无法操作。

查询约束：

如果想知道有哪些约束，可以直接查询user_constraints 数据字典。

SELECT * FROM user_constraints;

此时的约束只有一个名字，没有告诉是哪个字段上的约束。查看另一张数据字典表：

user_cons_columns;