约束的种类
1. 非空约束(不允许值为空)
2. 唯一约束(通过自动添加索引或利用现有索引来实现)
3. 主键约束(通过自动添加或利用现有索引和添加非空约束来实现)
4. 外键约束(也叫“引用完整性约束”,或简称“引用约束”)
5. 检查约束(为列给出写入值的条件,满足的才允许)
无约束的建表例子
create table test_t1 (
id number,
create_time timestamp,
name varchar2(20)
);
在建表时指定约束
1. 创建表在指定列的同时指定约束,不指定约束名(复合键约束无法用此法创建)
create table test_t1 (
id number primary key,
create_time timestamp,
name varchar2(20) not null);
2. 创建表在指定列的同时指定约束,指定约束名(复合键约束无法用此法创建)
create table test_t1 (
id number constraint t1_pk primary key,
create_time timestamp,
name varchar2(20) constraint t1_not_null not null);
3. 创建表时单独指定约束(除非空约束之外,其他种类约束均可用此法创建)
create table test_t1 (
id number,
create_time timestamp,
name varchar2(20),
constraint t1_pk primary key (id)
);
4. 在建表时指定复合键主键约束的例子
create table test_t1 (
id number,
create_time timestamp,
name varchar2(20),
constraint t1_pk primary key (id,create_time,name)
);
在现有表上添加约束的例子(以“无约束的建表例子”为例)
1. 添加主键约束
alter table test_t1 add constraint t1_pk primary key (id);
1.1 Oracle允许一个表有多个主键,比如:
CREATE TABLE employee ( emp_id NUMBER(10),
emp_name VARCHAR2(50),
emp_gender VARCHAR2(10),
CONSTRNT pk_employee_id PRIMARY KEY (emp_id),
CONSTRNT pk_employee_name PRIMARY KEY (emp_name),
CONSTRNT pk_employee_gender PRIMARY KEY (emp_gender)
);
2. 添加非空约束(添加非空约束有一点特殊)
2.1. 不指定约束名
alter table test_t1 modify name not null;
2.2. 指定约束名
alter table test_t1 modify name constraint t1_not_null not null;
3. 添加唯一约束
alter table test_t1 add constraint t1_uniq unique (id);
4. 添加检查约束
alter table test_t1 add constraint t1_chk check (name in ('A','B'));
5. 添加外键约束(另外创建两张表作为例子)
5.1 创建父表(必须创建主键约束,才能让子表创建外键约束)
create table parent_t1 (
department varchar2(10) constraint pt1_pk primary key);
5.2 创建子表
create table child_t1 (
depno varchar2(10),
name varchar2(20));
5.3 在子表的depno列上创建外键约束,引用父表parent_t1的department列
alter table child_t1 add constraint t1_fk foreign key (depno) references parent_t1(department);
删除约束
1. 当已知约束名时的删除
alter table test_t1 drop constraint t1_not_null;
2. 不知道约束名
2.1 查询指定表的约束名(如果约束在创建时没有指定名字,会有一个系统自建的名字)
select table_name,constraint_name,constraint_type from user_constraints where table_name='TEST_T1';
以下是一个查询系统自建约束名并删除的例子
SQL> create table test_t1 (
2 id number primary key,
3 create_time timestamp not null,
4 name varchar2(20),
5 constraint t1_chk check (name in ('A','B'))
6 );
Table created.
SQL> select table_name,constraint_name,constraint_type from user_constraints where table_name='TEST_T1';
TABLE_NAME CONSTRAINT_NAME C
------------------------------ ------------------------------ -
TEST_T1 SYS_C0014129 C
TEST_T1 T1_CHK C
TEST_T1 SYS_C0014131 P
SQL> alter table test_t1 drop constraint SYS_C0014129;
Table altered.
SQL> alter table test_t1 drop constraint SYS_C0014131;
Table altered.
SQL> select table_name,constraint_name,constraint_type from user_constraints where table_name='TEST_T1';
TABLE_NAME CONSTRAINT_NAME C
------------------------------ ------------------------------ -
TEST_T1 T1_CHK C
SQL>
关于USER_CONSTRAINTS.CONSTRAINT_TYPE的内容
这个列呈现约束的类型,以下是官方文档对该列中存在的可能值的说明
C - Check constraint on a table
P - Primary key
U - Unique key
R - Referential integrity
V - With check option, on a view
O - With read only, on a view
H - Hash expression
F - Constraint that involves a REF column
S - Supplemental logging
需要注意的是:C表示检查约束,但非空约束在这个视图中也显示为C
约束的四种状态
disable novalidate
disable validate
enable novalidate
enable validate
其中的disable/enable代表是否启用约束,validate/novalidate代表启用约束时是否对表中原有数据进行检查。
1. 禁用且不验证约束(默认,相当于只写disable)
alter table test_t1 disable novalidate constraint T1_CHK;
2. 禁用但验证约束(这会导致表无法insert/update/delete)
alter table test_t1 disable validate constraint T1_CHK;
2. 启用约束但不验证(表中原有数据不会被验证,但insert/update/delete要满足约束要求)
alter table test_t1 enable novalidate constraint T1_CHK;
3. 启用且验证约束(默认,相当于只写enable)
alter table test_t1 enable validate constraint T1_CHK;
约束的可延迟和非可延迟((deferrable和no deferrable),指是否允许在事务中用set constraint语句推迟约束验证
deferrable 允许在事务中推迟约束的验证
not deferrable 不允许在事务中推迟约束验证,这是默认设置
实验1:
在创建约束时指定deferrable属性,允许事务推迟约束验证
SQL> alter table test_t1 drop constraint t1_pk;
Table altered.
SQL> alter table test_t1 add constraint t1_pk primary key (id) deferrable;
Table altered.
不使用set constraint语句,直接插入数据(可以看到向主键插入重复值会出错)
SQL> insert into test_t1 values(1,sysdate,'A');
1 row created.
SQL> insert into test_t1 values(1,sysdate,'B');
insert into test_t1 values(1,sysdate,'B')
*
ERROR at line 1:
ORA-00001: unique constraint (YYJ.T1_PK) violated
使用set constraint改变约束延迟属性(可以看到允许主键插入重复值,但提交时出错)
SQL> set constraint t1_pk deferred;
Constraint set.
SQL> insert into test_t1 values(1,sysdate,'A');
1 row created.
SQL> insert into test_t1 values(1,sysdate,'B');
1 row created.
SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-00001: unique constraint (YYJ.T1_PK) violated
实验2:
在创建约束时指定not deferrable属性,不允许事务推迟约束验证
SQL> alter table test_t1 drop constraint t1_pk;
Table altered.
SQL> alter table test_t1 add constraint t1_pk primary key (id) not deferrable;
Table altered.
此时执行set constraint语句会出错,因为约束验证不可推迟
SQL> set constraint t1_pk deferred;
set constraint t1_pk deferred
*
ERROR at line 1:
ORA-02447: cannot defer a constraint that is not deferrable