Oracle约束(Constraint)详解
概述
约束是数据库用来确保数据满足业务规则的手段,不过在真正的企业开发中,除了主键约束这类具有强需求的约束,像外键约束,检查约束更多时候仅仅出现在数据库设计阶段,真实环境却很少应用,更多是放到程序逻辑中去进行处理。这也比较容易理解,约束会一定程度上较低数据库性能,有些规则直接在程序逻辑中处理就可以了,同时,也有可能在面对业务变更或是系统扩展时,数据库约束会使得处理不够方便。不过在我看来,数据库约束是保证数据准确性的最后一道防线,对于设计合理的系统,处于性能考虑数据库约束自然可有可无;不过若是面对关联关系较为复杂的系统,且对系统而言,数据的准确性完整性要高于性能要求,那么这些约束还是有必要的(否则,就会出现各种相对业务规则来说莫名其妙的脏数据)。总之,对于约束的选择无所谓合不合理,需要根据业务系统对于准确性和性能要求的侧重度来决定。
约束按照创建方式分为:表级约束和列级约束
表级约束:指创建表的时候,定义完全部列后。在最后指定约束对应的列
列级约束:指创建表的时候,定义为列立即定义该列对应的约束
某些特殊情况只能使用表级约束,列级约束无法完成创建。例如创建复合索引就必须使用表级索引,才能创建成功。
但例如创建非空约束,则必须在列级进行定义。不能在表级创建约束
具体创建方式,下面测试
- 列级约束
SQL> create table dc(
2 id number(10)
3 constraint dc_id_pk primary key,
4 name varchar2(10));
- 表级约束
SQL> create table edward(
2 id number(10),name varchar2(10),
3 constraint ed_id_pk primary key(id));
数据库约束有五种:
- 主键约束(PRIMARY KEY)
- 唯一性约束(UNIQUE)
- 非空约束(NOT NULL)
- 外键约束(FOREIGN KEY)
- 检查约束(CHECK)
下面我们就分别来看下这五类约束:
主键约束(PRIMARY KEY)
主键是定位表中单个行的方式,可唯一确定表中的某一行,关系型数据库要求所有表都应该有主键,不过Oracle没有遵循此范例要求,Oracle中的表可以没有主键(这种情况不多见)。关于主键有几个需要注意的点:
- 键列必须必须具有唯一性,且不能为空,其实主键约束 相当于 UNIQUE+NOT NULL
- 一个表只允许有一个主键(可以指定一列或多列)
- 主键所在列必须具有索引(主键的唯一约束通过索引来实现),如果不存在,将会在索引添加的时候自动创建
例如:
create table person
(
p_id int primary key, --定义该字段为主键约束
p_name varchar2(20),
p_age int
);
SQL> select table_name,constraint_name, ---未命名索引Oracle会按照SYS_Cn的格式自动创建约束名
2 constraint_type from
3 dba_constraints
4 where table_name='PERSON';
TABLE_NAME CONSTRAINT_NAME CONSTRAINT_TYPE
-------------------- -------------------------------- -------------------------------
PERSON SYS_C0011265 P
- 命名主键约束
create table person_1
(
p_id int constraint p1_pk primary key, --定义该字段为主键约束,并指定约束名字p_pk
p_name varchar2(20),
p_age int
);
- 复合主键索引
create table person_2
(
p_id int,
p_name varchar2(20),
p_age int,
constraint p2_pk primary key (p_id,p_name)--定义复合主键,并指定名字
--primary key(p_id,p_name)
);
SQL>select
c.constraint_name as "约束名",
c.table_name as "表名",
c.column_name as "列名",
c.position as "列位置",
d.constraint_type as "约束类型"
from
dba_constraints d
join dba_cons_columns c
on
(c.constraint_name=d.constraint_name)
where
c.table_name in ('PERSON_1','PERSON_2');
约束名 表名 列名 列位置 约束类型
------------ --------------- --------------- ---------- ---------------
P2_PK PERSON_2 P_NAME 2 P
P2_PK PERSON_2 P_ID 1 P
P1_PK PERSON_1 P_ID 1 P
- 添加主键(约束的添加可在建表时创建,也可如下所示在建表后添加,一般推荐建表后添加,灵活度更高一些,建表时添加某些约束会有限制)
SQL
>
alter
table
table_name
add
constraint
constraint_name
primary
key
(column_name);
- 删除主键约束
SQL>
alter table
table_name
drop
constraint
constraint_name;
或
alter table
table_name
drop
primary key
;
唯一性约束(UNIQUE)
唯一性约束的特点:
- 可作用在单列或多列上,因此,唯一约束既可以定义在列级,也可以定义在表级。对于这些列或列组合,唯一性约束保证每一行的唯一性(但不排除null)。
UNIQUE需要注意:
- 对于唯一约束来讲,索引是必须的。如果不存在,Oracle就自动创建一个(UNIQUE的唯一性本质上是通过索引来保证的)
- UNIQUE允许null值,UNIQUE约束的列可存在多个null。这是因为,唯一性是通过btree索引来实现,而btree索引中不包含null。当然,这也造成了在where语句中用null值进行过滤会造成全表扫描。
- 可以对列,同时定义非空和唯一约束。
例:
SQL> create table person_u
(p_id int,
p_name varchar2(20) constraint pu_un unique not null, -----同时创建唯一和非空约束,由于非空约束只能定义在列级,所以如果同时定义必须在列级进行定义。
p_age int
);
select
c.constraint_name as "约束名",
c.table_name as "表名",
c.column_name as "列名",
c.position as "列位置",
d.constraint_type as "约束类型"
from
dba_constraints d
join dba_cons_columns c
on
(c.constraint_name=d.constraint_name)
where
c.table_name='PERSON_U';
约束名 表名 列名 列位置 约束类型
--------------------- ------------------------ -------------- --------------- -------------
PU_UN PERSON_U P_NAME 1 U
SYS_C0011271 PERSON_U P_NAME 1 C
- 添加唯一约束
SQL
>
alter
table
table_name
add
constraint
constraint_name
unique
(column_name);
- 删除唯一约束
SQL>
alter table
table_name
drop
constraint
constraint_name;
非空约束(NOT NULL)
非空约束作用的列也叫强制列。顾名思义,强制键列中必须有值,当然建表时候若使用default关键字指定了默认值,则可不输入。
非空约束的特点:
定义了非空约束的字段中不能包含null值。
只能在列级定义非空约束。
在同一个表中可以定义多个非空约束。
SQL> create table person_n
(
p_id int,
p_name varchar2(20) constraint pn_c not null,
p_age int
);
SQL> desc person_n;
Name Null? Type
--------------- ----------------- ------------------
P_ID NUMBER(38)
P_NAME NOT NULL VARCHAR2(20)
P_AGE NUMBER(38)
- 添加非空约束,语法较特别(列在前面,约束名、类型在后面)
alter
table
table_name modify column_name
constraint
constraint_name
not null
;
例:
SQL> alter table person_n modify p_id constraint pn_id_c not null;
- 删除非空约束
alter table
table_name
modify
column_name
constraint
constraint_name
null;
例:
SQL>
alter table person_n modify p_id constraint pn_c null;
外键约束(FOREIGN KEY)
外键约束定义在具有父子关系的子表中,外键约束使得该列子表的值,必须对应父表的值,用以维护数据库的完整性。不过出于性能和后期的业务系统的扩展的考虑,很多时候,外键约束仅出现在数据库的设计中,实际会放在业务程序中进行处理。外键约束注意以下几点:
- 外键约束的子表中的列和对应父表中的列数据类型必须相同,列名可以不同
- 对应的父表列必须存在主键约束(PRIMARY KEY)或唯一约束(UNIQUE)
- 外键约束列允许NULL值,对应的行就成了孤行
例:创建一张表,将p_id列,定义为主键,然后参照p_id的主键将w_id列定义为外键,这种方法称为自引用
SQL> create table person_f
(
p_id int,
p_name varchar2(20),
p_age int,
w_id int,
constraint p_id_pk primary key (p_id),
constraint w_fk foreign key (w_id) references person_f (p_id)
);
SQL> select
c.constraint_name as "约束名",
c.table_name as "表名",
c.column_name as "列名",
c.position as "列位置",
d.constraint_type as "约束类型",
d.r_constraint_name as "外键参照内容"
from
dba_constraints d
join dba_cons_columns c
on
(c.constraint_name=d.constraint_name)
where
c.table_name='PERSON_F';
约束名 表名 列名 列位置 约束类型 外键参照内容
--------------------- ------------------------ -------------- --------------- ------------- ---------------------
W_FK PERSON_F W_ID 1 R P_ID_PK
P_ID_PK PERSON_F P_ID 1 P
其实很多时候不使用外键,很多人认为会让删除操作比较麻烦,比如要删除父表中的某条数据,但某个子表中又有对该条数据的引用,这时就会导致删除失败。
可以使用下面两种方式来优化这种场景
添加外键约束
- 第一种方式简单粗暴,删除的时候,级联删除掉子表中的所有匹配行,在创建外键时,通过 on delete cascade 子句指定该外键列可级联删除:
SQL
>
alter
table
外键表名
add
constraint
constraint_name
foreign
key
(外键表列)
references
参照表 (参照表列)
on
delete
cascade
;
- 第二种方式,删除父表中的对应行,会将对应子表中的所有匹配行的外键约束列置为NULL,通过 on delete set null 子句实施:
SQL
>
alter
table
外键表名
add
constraint
constraint_name
foreign
key
(外键表列)
references
参照表 (参照表列)
on
delete
set
null
;
- 删除外键约束
SQL>
alter table
table_name
drop constraint
constraint_name;
检查约束(CHECK)
检查约束可用来实施一些简单的规则,比如列值必须在某个范围内。检查的规则必须是一个结果为true或false 的表达式,
1)作为数据存入的最后一次检查,通过CHECK约束,确保列内存入的值在check所给的范围里面。
2)既可以定义为列级别,也可以定义为表级别
列级别:column_name data_type check(值1,值2)
表级别:constraint c_k check(条件)--自定义约束名
或者check(条件)--系统自动分配约束名
例:创建一张表,为p_id列定义约束 (p_id>100 and p_id <> p_age)
SQL> create table person_ck
(
p_id int,
p_name varchar2(20),
p_age int,
constraint p_ck check (p_id>100 and p_id <> p_age)
);
SQL> select
c.constraint_name as "约束名",
c.table_name as "表名",
c.column_name as "列名",
c.position as "列位置",
d.constraint_type as "约束类型",
d.search_condition as "检查内容"
from
dba_constraints d
join dba_cons_columns c
on
(c.constraint_name=d.constraint_name)
where
c.table_name='PERSON_CK';
约束名 表名 列名 列位置 约束类型 检查内容
--------------------- ------------------------ -------------- --------------- ------------- ---------------------------------------
P_ID_CK PERSON_CK P_ID C p_id>100 and p_id <> p_age
P_ID_CK PERSON_CK P_AGE C p_id>100 and p_id <> p_age
SQL> insert into person_ck (p_age) values (10);
1 row created.
SQL> insert into person_ck (p_id) values (10);
insert into person_ck (p_id) values (10)
*
ERROR at line 1:
ORA-02290: 违反检查约束条件 (DACHEN.P_CK)
SQL> insert into person_ck (p_id) values (99);
insert into person_ck (p_id) values (99)
*
ERROR at line 1:
ORA-02290: 违反检查约束条件 (DACHEN.P_CK)
SQL> insert into person_ck (p_id) values (101);
1 row created.
添加约束
SQL
>
alter
table
table_name
add
constraint
constraint_name
check
(具体约束内容);
删除约束
SQL>
alter table
table_name
drop constraint
constraint_name;
创建表定义默认值
column_name
defailt
默认值
例:
SQL> create table person_d
(
p_id int,
p_name varchar2(20),
p_age int default 100
);
查看是否定义默认值
SQL> select
column_name as "字段",
nullable as "是否允许为空",
data_default as "默认值"
from dba_tab_cols
where table_name='PERSON_D';
字段 是否允许为空 默认值
---------- -------------------- --------------
P_ID Y
P_NAME Y
P_AGE Y 100
添加、修改默认值
alter table table_name
modify
column_name
default
具体内容;
删除默认值
alter table table_name
modify
column_name
default
null;
约束状态
很多时候由于业务需要,比如我们有大量的历史数据,需要和现有数据合并,当前表存在数据库约束(如非空约束),而这些历史数据又包含违背非空约束的数据行,为了避免导入时由于违反约束而导入失败,我们通过调整约束状态来达到目的。
数据库约束有两类状态
启用/禁用(enable/disable)
:是否对新变更的数据启用约束验证
验证/非验证 (validate/novalidate)
:是否对表中已存在的数据进行约束验证
这两类四种状态从语法角度讲可以随意组合,默认是 enable validate
下面我们来看着四类组合会分别出现什么样的效果:
enable validate
: 默认的约束组合状态,无法添加违反约束的数据行,数据表中也不能存在违反约束的数据行;
enable novalidate
: 无法添加违反约束的数据行,但对已存在的违反约束的数据行不做验证;
disable validate
: 可以添加违反约束的数据行,但对已存在的违反约束的数据行会做约束验证(从描述中可以看出来,这本来就是一种相互矛盾的约束组合,只不过是语法上支持这种组合罢了,造成的结果就是会导致DML失败)
disable novalidate
: 可以添加违法约束的数据行,对已存在的违反约束的数据行也不做验证。
拿上面的例子来说,我们需要上传大量违反非空约束的历史数据(从业务角度讲这些数据不会造成系统功能异常),可以临时将约束状态转为 disable novalidate,以保证这些不合要求的数据导入表中
SQL
>
alter
table
table_name modify
constraint
constraint_name disable novalidate;
在数据导入完成之后,我们再将约束状态转为enable novalidate 以确保之后添加的数据不会再违反约束
SQL
>
alter
table
table_name modify
constraint
constraint_name enable novalidate;
重命名约束
alter table
table_name
rename constraint
原约束名 to 新约束名
约束延迟(在事务提交时再验证,默认为不延迟)
deferrable ---延迟生效
not deferrable ----不延迟生效(默认)
单独deferrable的含义就是是否允许约束检查延后进行。但单独设置deferrable之后,约束仍然不会延迟生效,要配合deferred参数,如果该参数是immediate,那么约束还是在DML的时候立即进行应用。如果deferred参数设置为deferred,约束就是在事务commit提交的时候应用,出现错误就连带回滚rollback整个事务
系统级别设置
set constraints 约束名 immediate; 关闭约束延迟
set constraints 约束名 deferred; 开启约束延迟
session级别设置
alter session set constraints=immediate;
alter session set constraints=deferred;
创建约束时,可指定开启延迟
create table tt
(
id int primary key deferrable initially deferred
);
约束所有相关内容全部在DBA_CONSTRAINTS和DBA_CONS_COLUMNS两个数据字典视图内通过自然连接可以方便查看。
CONSTRAINT_TYPE(约束类型) 的相关解释如下:
C - CHECK(检查)
P - Primary key(主键)
U - Unique key(唯一键)
R - Referential integrity(外键)
V - With check option, on a view(带有CHECK性质,视图)
O - With read only, on a view(只读性质,视图)
H - Hash expression(散列表达式)
F - Constraint that involves a REF column(涉及引用列约束)
S - Supplemental logging(开启补充日志)
如果是
check约束
,那么在search_condition列能看到具体的内容。
Oracle(修改表结构和约束)
- 添加、删除(列、约束、默认值)
- 修改列属性(数据类型、约束、默认值)
- 增加、删除约束
- 使某个约束生效、失效
- 删除、重命名、截断(表、列、约束、序列、同义词)
- 为表、列添加注释
- 增加列(约束、默认值)
语法:
alter table tb_name add column datatype [default val] constraint .....
- 如果添加not null(primary key约束要求值也不能为null)约束,需要保证当前表中没有数据存在。
- 新添加的列,相当于表定义中最后一个定义的列。
例如:
alter table test add name varchar2(10) default 'test' not null ;
alter table s_stu add (sname varchar2(20),sage number);
alter table husband add sage number constraint husband_sage_check check(sage<=100);
- 删除列:
语法:alter table tableName drop column column_name;
例如:alter table test drop column name;
- 修改列属性:(数据类型、约束、默认值)
语法:ALTER TABLE table MODIFY (column datatype [DEFAULT expr][NOT NULL] [, column datatype]...);
修改列的规则:
- 可以增加字段的宽度或者精度
- 如果列的值为null或者表中没有数据,可以降低宽度和精度
- 给当前列,后续添加的数据指定默认值。
- 当前列中没有null值时,可以重定义当前列为not null。
- 当前列中的全部为null值时,可以修改列的数据类型
- 如果需要给某个存在null值列添加not null约束,需要清除null值或使用modify并enable novalidate忽略旧数据。
例如:
alter table test modify id number constraint test_pk_id primary key;
alter table test modify id char(20);
- 增加约束
语法:alter table tb_name add 约束的完整定义
- 只能增加能够使用表级的约束
- 不能修改约束
例如:
alter table test add constraint test_pk_id primary key(id);
alter table test add check(gender in ('F','M'));
- 删除约束
语法:alter table tb_name drop 约束名。
例如:
alter table test drop constraint test_pk_id;
删除组件约束时,同时删除依赖它的外键约束
alter table test drop constraint test_pk_id cascade;
- 使某个约束失效:
语法:alter table tb_name disable constraint constraint_name [cascade];
注意:添加cascade表明要让该表所有的外键约束都级联失效。
- 使某个约束生效:
语法:alter table tb_name enable constraint constraint_name;
- 当启用unique和primary key约束时,会自动创建索引。
例如:alter table test enable constraint test_id_pk;
- 删除表:drop
drop table tb_name [cascade constraint];
- 删除表中所有数据
- 所有的索引同时被删除
- 加入cascade constraint,级联删除所有依赖该表的外键约束
例如:
drop table test cascade constraint;
- 重命名:rename
重命名表:
rename old_tb_name to new_tb_name;
重命名列:
alter table tb_name rename column old_col_name to new_col_name;
- 重命名可以用来修改table,view,sequence,synonym
- 只有是这个对象的拥有者,才能重命名。
例如:
rename emp to emp2; 将表名重n名为emp2
alter table emp rename column id to eid;
- 截断表:truncate
语法:truncate table tb_name
- 清空表中所有数据
- 降低HWM,只保留表结构
- 是一个DDL命令。
- 一旦删除,事务不能回滚。
例如:truncate table emp;
- delete和truncate的比较
delete:可以指定删除某些列,也可以清空表,但是不降低HWM,在事务没有提交之前可以回滚。
truncate:只能清空表数据,降低HWM,不能回滚。
- 给表、列加注释:comments
语法:
COMMENT ON TABLE
talbe_name
IS '注释内容'
COMMENT ON COLUMN
table_name.column_name
IS '注释内容';
- 添加的注释可以在如下数据字典中查看
ALL_COL_COMMENTS
USER_COL_COMMENTS
ALL_TAB_COMMENTS
USER_TAB_COMMENTS
例如:
SQL> comment on table p is '测试表';
SQL> comment on column p.id is '测试';
SQL> select * from user_col_comments where table_name='P';
TABLE_NAME COLUMN_NAME COMMENTS
--------------- --------------- --------------------
P ID 测试
SQL> select * from user_tab_comments where table_name='P';
TABLE_NAME TABLE_TYPE COMMENTS
--------------- ----------- --------------------
P TABLE 测试表
测试
一、创建一张表,里面包含列还有约束
create table person_dc
(
p_id int,
p_name varchar2(20),
p_mgr int,
p_age int not null,
p_sal int not null,
p_comm int,
p_date date default sysdate,
constraint p_dc_pk primary key (p_id),
constraint p_dc_un unique (p_name),
constraint p_dc_fk foreign key (p_mgr)
references person_dc(p_id) on delete set null,
constraint p_dc_ck check (p_age>(1+p_id)),
constraint p_dc_s_ck check (p_sal >0),
constraint p_dc_c_ck check(p_comm>=((p_sal/100)*10))
);
select
c.constraint_name as "约束名",
c.table_name as "表名",
c.column_name as "列名",
c.position as "列位置",
d.constraint_type as "约束类型",
d.r_constraint_name as "外键参照内容",
d.search_condition as "检查内容"
from
dba_constraints d
join dba_cons_columns c
on
(c.constraint_name=d.constraint_name)
where
c.table_name='PERSON_DC';
约束名 表名 列名 列位置 STATUS 约束类型 外键参照内容 检查内容
------------------------ -------------- ---------- ---------- ------------- ------------ ------------------------- ----------------------
P_DC_FK PERSON_DC P_MGR 3 ENABLED R P_DC_PK
SYS_C0011326 PERSON_DC P_SAL ENABLED C "P_SAL" IS NOT NULL
P_DC_CK PERSON_DC P_ID ENABLED C p_age>(1+p_id)
P_DC_CK PERSON_DC P_AGE ENABLED C p_age>(1+p_id)
P_DC_S_CK PERSON_DC P_SAL ENABLED C p_sal >0
P_DC_C_CK PERSON_DC P_SAL ENABLED C p_comm>=(sal+((p_sal/100)*10))
P_DC_C_CK PERSON_DC P_COMM ENABLED C p_comm>=(sal+((p_sal/100)*10))
SYS_C0011325 PERSON_DC P_AGE ENABLED C "P_AGE" IS NOT NULL
P_DC_PK PERSON_DC P_ID 1 ENABLED P
P_DC_UN PERSON_DC P_NAME 2 ENABLED U
插入一行数据,commit
SQL> insert into person_dc (p_id,p_name,p_mgr,p_age,p_sal,p_comm,p_date) values (1,'大辰',1,10,100,101,'12-4月-18');
现在修改p_comm列约束为p_comm必须大于等于p_sal的1.1倍,由于不能直接修改约束,现在需要重建一个约束然后将原来的约束删除。
SQL> alter table person_dc add constraint P_DC_C_eck check(p_comm>=(p_sal+(p_sal/100)*10));
ERROR at line 1:
ORA-02293: 无法验证 (DACHEN.P_DC_C_ECK) - 违反检查约束条件
由于之前的数据违反了,新创建的约束。需要忽略旧数据或删除之前的数据,这里使用
enable novalidate不对旧数据做检查。
再次执行,添加成功
SQL> alter table person_dc add constraint P_DC_C_eck check(p_comm>=(p_sal+(p_sal/100)*10)) enable novalidate;
Table altered.
现在删除旧的约束
SQL> alter table person_dc drop constraint p_dc_c_ck;
Table altered.
再次插入一行数据,可以看出新的约束已经生效
SQL> insert into person_dc (p_id,p_name,p_mgr,p_age,p_sal,p_comm,p_date) values (2,'小明',2,20,200,201,'12-4月-18');
insert into person_dc (p_id,p_name,p_mgr,p_age,p_sal,p_comm,p_date) values (2,'小明',2,20,200,201,'12-4月-18')
*
ERROR at line 1:
ORA-02290: 违反检查约束条件 (DACHEN.P_DC_C_ECK)
添加一个列,并且添加一个not null约束。新添加的列为该表最后一个列,not null和主键约束都是不能在已有数据的表中直接添加,必须为空表或者使用MODIFY修改列属性并且忽略旧数据。
SQL> alter table person_dc add p_tx int;
Table altered.
SQL> alter table person_dc modify p_tx int constraint p_tx_n not null enable novalidate;
Table altered.