[Oracle]约束(constraint)
(一)约束的概念
在Oracle中,可以通过设置约束来防止无效数据进入表中。Oracle一共有5种约束:
- 主键约束(primary key)
- 外键约束(foreign key)
- 唯一性约束(unique)
- 非空约束(not null)
- 检查约束(check)
(1)主键约束
--主键约束可以定义在一列或多列上,值具有唯一性、非空性;
--在一个表上只能定义一个主键约束;
--Oracle会自定在主键约束的列上创建唯一性索引,可以指定唯一性索引的位置及存储参数。
(2)外键约束
--外键约束列的取值来源于参照表(父表)的参照列的值,或者空值;
--定义外键约束的列只能参照父表的主键约束列和唯一性约束列;
--父表与子表必须在同一个数据库中。
(3)唯一性约束
--可定义在一列或多列上,列的取值必须唯一;
--如果在某些列上定义了唯一性约束,而没有定义非空约束,那么在这些列上可以出现多个空值;
--Oracle会自定在主键约束的列上创建唯一性索引,可以指定唯一性索引的位置及存储参数。
(4)非空约束
--只能定义在列上;
--在同一个表中可定义多个非空约束。
(5)检查约束
--检查约束用来限制列的取值范围,其表达式必须引用相应列,表达式的计算结果是一个布尔值;
--一个列可以定义多个检查约束
(二)列级约束与表级约束
列级约束是对某一个特定列的约束,包含在列的定义中,直接跟在其它定义之后,不需要指出列名。其语法为:
column_definition [CONSTRAINT constraint_name] constraint_type ...
表级约束的定义与列的定义相互独立,通常用于多列组合的约束,定义表级约束要指出约束的名称。其语法为:
[CONSTRAINT constraint_name] constraint_type(column1,[column2...]);
需要注意的是,除了非空约束只能定义列级约束外,其他的均可定义列级/表级约束。
(三)约束的状态
根据在数据插入、更新时是否对数据进行约束检查,约束状态分为激活状态(ENABLE)和禁用状态(DISABLE);
根据是否对表中已有数据进行约束检查,约束状态分为验证状态(VALIDATE)和非验证状态(NOVALIDATE);
组合之后,一共有四种约束状态:
约束状态 |
定义 |
特点 |
激活验证状态 (ENABLE VALIDATE) |
激活约束且约束检查已有数据 | 对表中已有数据进行检查,对后续插入、更新的数据也会进行检查 |
激活非验证状态 (ENABLE NOVALIDATE) |
激活约束但是不检查表中已存在的数据 | 不检查目前表中已经存在的数据,但会检查后续插入、更新的数据 |
禁用验证状态 (DISABLE VALIDATE) |
不激活约束但是会去检查表中已经存在的数据 | Oracle不允许进行任何DML(insert/update/delete)操作,因为操作无法得到约束的检查。表处于只读模式 |
禁用非验证状态 (DISABLE NOVALIDATE) |
不激活验证且不检查表中已有的数据 | 不会对表中数据进行约束检查,也不会对新插入的数据进行约束检查 |
(四)创建约束
(1)创建列级约束
创建列级约束的语法为:
INITIALLYCONSTRAINT constraint_name --约束名 [ --选择5中约束类型 [NOT] NULL | [UNIQUE] | [PRIMARY KEY] | [CHECK (condition)] | [REFERENCES [schema.]object[(column)] [ON DELETE CASCADE | SET NULL]] ] [ --是否延迟约束检查 [NOT DEFERRABLE] | [DEFERRABLE [INITIALLY IMMEDIATE | DEFERRED]] ] [ENABLE | DISABLE] --定义约束检查的类型 [VALIDATE | NOVALIDATE ] [USING INDEX index_clause] --约束上索引的定义(主键约束与唯一性约束) [EXCEPTIONS INTO [schema.]table]
--违反约束的数据的存储方式
参数说明:
--cinstraint_name:约束名,如果没有为约束命名,oracle将自动为约束命名,命名样式为SYS_Cn,n是数据库对象的唯一编号;
--ON DELETE CASCADE:定义级联删除的外键约束;
--ON DELETE SET NULL:定义置空删除的晚间约束;
--NOT DEFERRABLE:约束不可延迟;
--DEFERRABLE:约束可以延迟;
--INITIALLY IMMEDIATE:可延迟约束的立即检查;
--DEFERRED:可延迟约束的延迟检查;
--USING INDEX index_clause:创建 唯一性索引的参数设置(存在与主键约束、唯一性约束);
--EXCEPTIONS INTO:将违反约束的记录保存到表中。
(2)定义表级约束
列级约束是定义在列上的,如果需要在多个列上定义约束,则需采用表级约束,表级约束语法如下:
.INITIALLYCONSTRAINT constraint_name --约束名 [ --选择4种约束类型,非空约束只能定义在列上 [UNIQUE(column1[,column2…])] | [PRIMARY KEY(column1[,column2…])] | [CHECK (condition)] | [FOREIGN KEY (column1[,column2…]) --外键约束需要指出要约束的列,与列级约束有较大区别
REFERENCES [schema.]object[(column1[,column2…])] [ON DELETE CASCADE | SET NULL]] ] [ --是否延迟约束检查 [NOT DEFERRABLE] | [DEFERRABLE [INITIALLY IMMEDIATE | DEFERRED]] ] [ENABLE | DISABLE] --定义约束检查的类型 [VALIDATE | NOVALIDATE ] [USING INDEX index_clause] --约束上索引的定义(主键约束与唯一性约束) [EXCEPTIONS INTO [schema.]table] --违反约束的数据的存储方式
(五)添加、修改、重命名、删除约束
(1)添加约束
语法为:
ALTER TABLE table_name ADD [CONSTRAINT constraint_name] constraint_type(column1[,column2...]) [constraint_parameters];
例子1.添加约束
--创建表books create table books ( id number(6), title varchar2(20), isbn number(20), auther varchar(10), price number(6,2), discribe varchar2(1000), pid number ); Table created --创建表publish(用来辅助books创建外键约束) create table publish ( pid number primary key, name varchar2(20) ); Table created --添加主键约束 SQL> alter table books add constraint books_pk primary key (id); Table altered --添加外键约束 --需要注意,参照的父表的列需要是主键约束列或唯一性约束列
--主要注意,外键约束的references带‘s’ SQL> alter table books add constraint books_fk foreign key (pid) references publish(pid); Table altered --创建唯一性约束 SQL> alter table books add constraint books_unique unique (title); Table altered --创建检查约束 SQL> alter table books add constraint books_check check (price > 0); Table altered --需要特别注意,非空约束只能使用modify来创建/删除 ,不能使用add方式 --1.创建非空约束 SQL> alter table books modify auther not null; Table altered --2.删除非空约束 SQL> alter table books modify auther null; Table altered
(2)修改约束
可以使用ALTER TABLE … MODIFY修改约束参数,语法为:
ALTER TABLE table_name MODIFY [CONSTRAINT constraint_name] | [PRIMARY KEY] | [UNIQUE(column1[,column2,...])
] --主键约束和唯一性约束可以通过约束类型来修改 [constraint_parameters]
例子2.修改约束
--修改检查约束,将其置为不检查表中已有数据 SQL> alter table books modify constraint books_check novalidate; Table altered --使主键约束不可用 SQL> alter table books modify primary key disable; Table altered
(3)重命名约束
可以使用ALTER TABLE … RENAME…来重命名约束,语法为:
ALTER TABLE table_name RENAME CONSTRAINT old_constrain_name TO new_constraint_name;
(4)删除约束
可以使用ALTER TABLE … DROP …来删除约束,语法为:
ALTER TABLE table_name DROP [CONSTRAINT constraint_name] | [PRIMARY KEY] | [UNIQUE (column1 [,column2,...])] [CASCADE] [KEEP | DROP INDEX]
对于删除约束,需要注意:
1.可以通过约束名称来删除约束,对于主键约束和唯一性约束,也可以指定约束的类型来删除约束;
2.如果要删除的主键约束或唯一性约束已经被子表中的外键约束引用,需要加CASCADE关键字,删除子表中的外键约束;
3.在删除主键约束/唯一性约束时,如果要保留主键约束/唯一性约束创建时生成的唯一性索引,则需要使用KEEP关键字。
图、books与publish的关系
例子3.删除主键约束
--根据上图,我们发现books的外键引用了publish的主键,如果直接删除,会报错,只有加了cascade后才能删除主键约束
--删除主键约束后才去看books表,对应的外键约束也被删除了
SQL> alter table publish drop primary key; alter table publish drop primary key ORA-02273: 此唯一/主键已被某些外键引用 SQL> alter table publish drop primary key cascade; Table altered
(六)关于外键约束的数据删除方式
假如两个表之间存在外键约束的关系,在父表进行数据删除时,子表的关联数据如何处理?Oracle提供了3种方式。
(1)受限删除【默认】,如果子表中有与父表关联的记录存在,则不能删除数据;
例子4.受限删除无法删除已经关联的数据
create table table_a --父表 ( id number primary key, name varchar(20) ); create table table_b1 --子表 ( id number references table_a(id), --默认是受限删除 country varchar2(30) ) SQL> insert into table_a values(1,'lijiaman'); 1 row inserted SQL> insert into table_b1 values(1,'test'); 1 row inserted SQL> commit; Commit complete SQL> delete table_a where id = 1; --子表有关联数据,导致父表数据也无法删除 delete table_a where id = 1 ORA-02292: 违反完整约束条件 (LIJIAMAN.SYS_C0013519) - 已找到子记录 SQL> insert into table_a values(2,'fg'); 1 row inserted SQL> delete table_a where id = 2; --子表无关联数据,父表数据可以删除 1 row deleted SQL> commmit;
(2)级联删除,删除父表数据的同时也会将外键关联的子表的记录删除;
例子5.级联删除外键约束会删除子表关联数据
SQL> drop table table_b1; --删除table_b1 Table dropped create table table_b2 --子表 ( id number references table_a(id) on delete cascade, --级联删除外键约束 country varchar2(30) ); SQL> insert into table_b2 values(1,'ch'); 1 row inserted SQL> select * from table_a ; --两个表各有1行关联数据 ID NAME ---------- -------------------- 1 lijiaman SQL> select * from table_b2; ID COUNTRY ---------- ------------------------------ 1 ch SQL> delete table_a where id = 1; --删除父表数据,子表关联数据也随之删除 1 row deleted SQL> commit; Commit complete SQL> select * from table_a; ID NAME ---------- -------------------- SQL> select * from table_b2; ID COUNTRY ---------- ------------------------------
(3)置空删除,父表数据删除后,子表中关联记录的外键约束的列值置为空;
例子6.置空删除外键约束会将子表关联记录的外键列置为空值
create table table_b3 --子表 ( id number references table_a(id) on delete set null, --置空删除外键约束 country varchar2(30) ); SQL> insert into table_a values(1,'adrci'); --往两个表插入1行关联数据 1 row inserted SQL> insert into table_b3 values(1,'srvctl'); 1 row inserted SQL> commit; Commit complete SQL> select * from table_a; ID NAME ---------- -------------------- 1 adrci SQL> select * from table_b3; ID COUNTRY ---------- ------------------------------ 1 srvctl SQL> delete table_a where id = 1; --删除id=1的数据,父表整条记录被删除,子表仅置空了关联数据的外键列值 1 row deleted SQL> select * from table_a; ID NAME ---------- -------------------- SQL> select * from table_b3; ID COUNTRY ---------- ------------------------------ srvctl
(七)与约束相关的数据字典
视图名称 | 说明 |
DBA_CONSTRAINTS ALL_CONSTRAINTS USER_CONSTRAINTS |
包含数据库中所有约束的定义信息; 包含当前用户可以看到的所有约束的定义信息; 包含当前用户拥有的约束的定义信息; |
DBA_CONS_COLUMNS ALL_CONS_COLUMNS USER_CONS_COLUMNS |
记录了约束列信息,DBA_ 、ALL_ 、USER_的用法与前面相同 |