Oracle数据库——约束-15

最新推荐文章于 2023-05-11 12:55:26 发布

a584898

最新推荐文章于 2023-05-11 12:55:26 发布

阅读量268

点赞数

分类专栏： Oracle数据库

本文链接：https://blog.csdn.net/a584898/article/details/83118558

版权

Oracle数据库专栏收录该内容

14 篇文章 1 订阅

订阅专栏

约束（核心）

数据表创建完成之后，对于数据表内保存的数据往往需要做一些过滤，例如：人员的编号应该唯一，性别
应该只有男或女，年龄的范围也应该有。
在数据库之中一共存在有6种约束；
有一种约束是数据类型的约束，例如：存放日期的不能随意存放字符串；
五种人为约束：非空约束，主键约束，检查约束，外键约束。

非空约束（NOT NULL,NK）

非空约束主要是在表建立的时候使用，直接在字段后增加“NOT NULL”即可。

范例：定义非空约束
--删除对象
DROP TABLE member PURGE;
--创建对象
CREATE TABLE member (
mid NUMBER,
name VARCHAR2(20) NOT NULL
);

范例：增加错误的数据
--测试数据
INSERT INTO member(mid,name) VALUES(1,null);
INSERT INTO member(mid) VALUES(2);
ORA-01400: 无法将 NULL 插入 ("SCOTT"."MEMBER"."NAME")
非空约束一旦违反，会自动的告诉用户在那个用户的那张表中的那个字段里面出现了违反约束的情况

-唯一约束（UNIQUE,NK）

如果现在希望数据表的某一列数据不重复，则可以使用此约束，例如：每个成员有自己的email地址，地址不可能
重复，所以就需要在email这个字段上使用唯一约束
范例：定义唯一约束
--删除对象
DROP TABLE member PURGE;
--创建对象
CREATE TABLE member (
   mid    NUMBER,
   name    VARCHAR2(20) NOT NULL,
   email   VARCHAR2(50) UNIQUE
);

范例：增加正确的数据
--测试数据
INSERT INTO member(mid,name,email) VALUES(1,'张三','jianjian@162.com');
INSERT INTO member(mid,name,email) VALUES(2,'战士',null);
INSERT INTO member(mid,name,email) VALUES(3,'王八',null);
在定义唯一约束之后，发现uull这类数据是不受约束控制的

范例：增加错误的数据——email重复
--测试数据
INSERT INTO member(mid,name,email) VALUES(10,'你妹','jianjian@162.com');

ORA-00001: 违反唯一约束条件 (SCOTT.SYS_C0011256)
此时依然会出现有一个错误的代号，同时会告诉用户违反了唯一约束，但是显示的信息是“(SCOTT.SYS_C0011256)
”对于约束而言实际上本身也属于数据库的一个对象，只要是对象（一定保存在数据字典之中），就必须存在一个名称作为标记
那么此时由于某一设置对象的名字，所以会自动的由系统进行名称分配
如果要想确定此时的约束名称的含义以及作用的字段，则可以使用“user_cons_columns”数据字典
范例：查询数据字典
SELECT * FROM user_cons_columns;
这个数据字典可以发现每一个约束在那个列上起作用，虽然这个时候就可以查询出某个约束与具体的字段
联系，但是毕竟不是很方便，所以一般都建议定义约束的名字，而约束名字一般采用“约束简写_字段”
约束的简写是“UK”，约束在email字段上，所以约束名称设置为“UK_EMAIL”。如果设置约束名称，则必须在
数据库创建脚本之中编写“CONSTRAINT”语句。

范例：设置唯一约束的名字
--删除对象
DROP TABLE member PURGE;
--创建对象
CREATE TABLE member (
   mid    NUMBER,
   name    VARCHAR2(20) NOT NULL,
   email   VARCHAR2(50)
   CONSTRAINT uk_email UNIQUE (email)
);

此时再次使用违反约束，错误提示“ORA-00001”;违反唯一约束条件（SCOTT UK_EMAIL）,此时在进行错误
排查的时候就非常方便了

主键约束（PRIMARY KEY,PK）

主键约束=非空约束+唯一约束。表示某一列上的数据不允许为null，而且不能重复，一般像雇员的编号，
人员的身份证号都需要设置主键约束
范例：定义主键约束
--删除对象
DROP TABLE member PURGE;
--创建对象
CREATE TABLE member (
   mid    NUMBER,
   name    VARCHAR2(20) NOT NULL,
   CONSTRAINT uk_mid PRIMARY KEY (mid)
);

范例：设置null数据
INSERT INTO member(mid,name) VALUES(null,'张三');
ORA-01400: 无法将 NULL 插入 ("SCOTT"."MEMBER"."MID")

范例:设置重复的数据
INSERT INTO member(mid,name) VALUES(1,'张三');
INSERT INTO member(mid,name) VALUES(1,'张三');
ORA-00001: 违反唯一约束条件 (SCOTT.UK_MID)

一般开发来讲一张数据表里面只会存在一个或零个主键，那么在SQL之中也允许用户为一张表设置多个主键
，此类操作称为复合主键

范例：定义复合主键
--删除对象
DROP TABLE member PURGE;
--创建对象
CREATE TABLE member (
   mid    NUMBER,
   name    VARCHAR2(20) NOT NULL,
   CONSTRAINT pk_mid_name PRIMARY KEY (mid,name)
);

这个时候由于mid和name都属于主键字段，所以只有当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,'李地');
ORA-00001: 违反唯一约束条件 (SCOTT.PK_MID_NAME)
一般正常人都不会

检查约束（CHECK,CK）

所谓的检查约束指的是在字段设置上配置若干个田间，满足过滤条件的数据允许保存，否则会报错
范例：定义检查约束
--删除对象
DROP TABLE member PURGE;
--创建对象
CREATE TABLE member (
   mid    NUMBER,
   name    VARCHAR2(20) NOT NULL,
   sex    VARCHAR2(10),
   age   NUMBER(3),
   CONSTRAINT pk_mid PRIMARY KEY (mid)
   CONSTRAINT ck_sex CHECK(sex IN('男','女','中')),
   CONSTRAINT ck_age CHECK (age BETWEEN 0 AND 250)
);

范例：增加错误的性别
INSERT INTO member(mid,name,sex,age) VALUES(1,'大雨','不女',20);

范例：增加错误的年龄
INSERT INTO member(mid,name,sex,age) VALUES(1,'小雨','女',290);

使用了检查约束之后，所有数据应该都是安全的了，但是不是好的解决方案。
实际上所谓的约束就是对数据进行层层过滤，如果过滤的太多了，数据量更新大，性能上就差了。
以上四种约束用得最多：主键，非空

外键约束（FOREIGN 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) ,
   price    NUMBER,
   mid   NUMBER,
   CONSTRAINT pk_bid PRIMARY KEY (bid)
);

这个时候的book表中的mid表示此书属于的成员那一位（因为成员依靠mid作为唯一标记），
范例：增加正确的数据
INSERT INTO member(mid,name) VALUES(1,'张三');
INSERT INTO member(mid,name) VALUES(2,'李四');
INSERT INTO book(bid,title,price,mid) VALUES(10001,'Java',89,1);
INSERT INTO book(bid,title,price,mid) VALUES(10002,'Oracle',99,1);
INSERT INTO book(bid,title,price,mid) VALUES(10007,'Android',58,2);
INSERT INTO book(bid,title,price,mid) VALUES(10008,'Oracle-C',57,2);
INSERT INTO book(bid,title,price,mid) VALUES(10009,'Phone',59,2);
这个时候book表一共存在5个数据，而且这5个数据都有对应的成员，为了确定每个成员都有查询

范例：统计出每个成员的编号，姓名，拥有图书的数量，花费的总额
确定所需的数据表
member表：编号，姓名
book表：统计信息
确定已知的关联字段
成员和图书：member.mid=book.mid;
暂时考虑性能问题,直接使用多表关联后进行多字段分组
SELECT m.mid,m.name,COUNT(b.bid),SUM(b.price)
FROM member m,book b
WHERE m.mid=b.mid
GROUP BY m.mid,m.name;

这个时候的术后之所以正确是因为人为为其增加了正确的内容，但是数据库本身并不具备检测错误的数据

范例：错误的数据
INSERT INTO book(bid,title,price,mid) VALUES(30008,'儿童数学',19,22);
既然book.mid应该由member.mid字段作为范围参考，但是此时member表中并没有mid=22的数据，而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) ,
   price    NUMBER,
   mid   NUMBER,
   CONSTRAINT pk_bid PRIMARY KEY (bid),
   CONSTRAINT fk_mid FOREIGN KEY (mid)
   REFERENCES member(mid)
);

本程序最为关键的部分在于子表上设置的外键约束
CONSTRAINT fk_mid FOREIGN KEY (mid) REFERENCES member(mid)
CONSTRAINT 约束名字 FOREIGN KEY (子表字段) REFERENCES 父表(父表字段)
此时设置了外键约束之后如果是正确的数据则可以正常的进行插入，而一旦出现了错误数据（使用了主表
不存在的数据），就会出现错误提示”(SCOTT.FK_MID)“，未找到父项关键字
对于外键操作现在会存在有如下的几个限制

限制一：主表中如果作为子表外键的字段必须设置主键约束或者唯一约束，否则无法设置外键约束
--创建对象
CREATE TABLE member (
   mid    NUMBER,
   name    VARCHAR2(20) NOT NULL
);
CREATE TABLE book (
   bid    NUMBER,
   title    VARCHAR2(20) ,
   price    NUMBER,
   mid   NUMBER,
   CONSTRAINT pk_bid PRIMARY KEY (bid),
   CONSTRAINT fk_mid FOREIGN KEY (mid)
   REFERENCES member(mid)
);

限制二：由于子表要关联父表，所以在删除父表之前必须首先保证子表被删除
范例：删除操作，先删子表再删父表
DROP TABLE book PURGE;
DROP TABLE member PURGE;

这个时候实际上我们自己清楚外键关系，但是在一些由于数据库你不熟悉，但是又要来快速删除表的时候，如果要顺序来删除就很
麻烦了。为了解决这个问题，在Oracle提供了一个强制删除主表
范例：强制删除member表（此时book子表依然存在）
DROP TABLE member CASCADE CONSTRAINT;
以上的操作方式一般不要采用

限制三：在删除父表数据时必须保证对应的子表数据已经全部被删除，否则无法删除

范例：数据库脚本
--删除对象
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) ,
   price    NUMBER,
   mid   NUMBER,
   CONSTRAINT pk_bid PRIMARY KEY (bid),
   CONSTRAINT fk_mid FOREIGN KEY (mid)
   REFERENCES member(mid)
);
--测试数据
INSERT INTO member(mid,name) VALUES(1,'张三');
INSERT INTO member(mid,name) VALUES(2,'李四');
INSERT INTO book(bid,title,price,mid) VALUES(10001,'Java',89,1);
INSERT INTO book(bid,title,price,mid) VALUES(10002,'Oracle',99,1);
INSERT INTO book(bid,title,price,mid) VALUES(10007,'Android',58,2);
INSERT INTO book(bid,title,price,mid) VALUES(10008,'Oracle-C',57,2);
INSERT INTO book(bid,title,price,mid) VALUES(10009,'Phone',59,2);
--提交事务
COMMIT;
范例：现在删除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;
但是这样操作也有问题，因为很多时候是无法意义取确认子表数据是否存在的，为此提供了数据的级联删除
A，数据的级联删除
--删除对象
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) ,
   price    NUMBER,
   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,price,mid) VALUES(10001,'Java',89,1);
INSERT INTO book(bid,title,price,mid) VALUES(10002,'Oracle',99,1);
INSERT INTO book(bid,title,price,mid) VALUES(10007,'Android',58,2);
INSERT INTO book(bid,title,price,mid) VALUES(10008,'Oracle-C',57,2);
INSERT INTO book(bid,title,price,mid) VALUES(10009,'Phone',59,2);
--提交事务
COMMIT;

这个时候一旦删除了父表数据，那么对应的子表数据同时也会删除掉。
B。级联更新：
再删除父表数据时，对应的子表数据设置为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) ,
   price    NUMBER,
   mid   NUMBER,
   CONSTRAINT pk_bid PRIMARY KEY (bid),
   CONSTRAINT fk_mid FOREIGN KEY (mid)
   REFERENCES member(mid) ON DELETE SET NULL
);
是否配置数据的级联操作完全根据业务来决定的。
修改约束（了解）
约束本身就是数据库对象，所以约束也进行修改，例如，现在又如下一张表

范例：定义数据表
--删除对象
DROP TABLE member PURGE;
--创建对象
CREATE TABLE member (
mid NUMBER,
name VARCHAR2(20)
);
INSERT INTO member(mid,name) VALUES(1,null);
INSERT INTO member(mid,name) VALUES(1,'张三');
这个时候member表之中存在又错误的数据，主键重复了，姓名为null了
1.为表中增加数据，语法
ALTER TABLE 表名称 ADD CONSTRAINT 约束名称约束类型（字段）[选项];
范例：为表中增加主键约束
ALTER TABLE member ADD CONSTRAINT pk_mid PRIMARY KEY(mid);
这个时候如果表之中存在的数据已经有违反约束的地方了，那么约束无法添加
DELETE FROM member WHERE name='张三';
但是使用ALTER语句可以进行主键，唯一，检查，外键四种约束，却无法处理非空约束，如果要增加非空
约束，只能够采用修改表结构的方式完成
范例：增加非空约束
错误：ALTER TABLE member ADD CONSTRAINY nk_name NOT NULL(name);
正确：ALTER TABLE member MODIFY(name VARCHAR2(20) NOT NULL);

删除表中的约束：语法
ALTER TABLE 表名称 DROP CONSTRAINY 约束名称;
范例：删除表中的主键约束
ALTER TABLE member DROP CONSTRAINY pk_mid;
虽然现在给出了修改约束与修改表结构的语法，但是从所有的开发来讲，修改表不要使用，而且所有的约束修改
不要用，在表建立的同时就设置完整，千万不要在后期增加。