约束_sky 赤之约束

约束
用来维护数据结构完整性的一种手段
可以是表级 也可以是列级
通过查询视图 user_constraintsuser_cons_columns
约束的五种类型
非空notnull
唯一unique
检测check
主键primarykey
外键foreignkey

非空约束
列的值不允许null值
可以在建表时指明
可以建完表后添加
可以指定名字 不指定系统会给定随机名

创建表时在字段类型后添加 not null关键字

SQL> create table t1 (id number not null,namevarchar2(20) constraint t1_name_notnull not null);

Table created.

SQL> desc t1
NameNull?Type
---------------------------------------------------------- ----------------------------------
IDNOT NULL NUMBER
NAMENOT NULL VARCHAR2(20)

查看约束
SQL> select TABLE_NAME, CONSTRAINT_NAME,CONSTRAINT_TYPE, STATUS from user_constraints where table_name ='T1';

TABLE_NAMECONSTRAINT_NAMEC STATUS
------------------------------ ------------------------------ ---------
T1SYS_C005173CENABLED
T1T1_NAME_NOTNULLC ENABLED

SQL>
查看约束作用于t1表哪一列
SQL> col OWNER for a10
SQL> col column_name for a16
SQL> col table_name for a10
SQL> col CONSTRAINT_NAME for a20
SQL> select * from user_cons_columns wheretable_name = 'T1';

OWNERCONSTRAINT_NAMETABLE_NAMECOLUMN_NAMEPOSITION
---------- -------------------- ---------- --------------------------
SCOTTT1_NAME_NOTNULLT1NAME
SCOTTSYS_C005173T1 ID

SQL>
SQL> insert into t1 (id) values(1);
insert into t1 (id) values(1)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."T1"."NAME")

SQL> insert into t1 (name) values('SEKER');
insert into t1 (name) values('SEKER')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."T1"."ID")

SQL> insert into t1 values(1,'SEKER');

1 row created.

SQL>

对已经存在的表添加
SQL> alter table t1 add (mail varchar2(40));

Table altered.

SQL> desc t1
NameNull?Type
---------------------------------------------------- ------------------------------
IDNOT NULL NUMBER
NAME NOT NULL VARCHAR2(20)
MAILVARCHAR2(40)

SQL> alter table t1 modify(mail constraint ct notnull);

Table altered.

SQL> desc t1
NameNull?Type
---------------------------------------------------- ------------------------------
IDNOTNULL NUMBER
NAMENOT NULL VARCHAR2(20)
MAILNOT NULL VARCHAR2(40)

SQL>

唯一约束

列的数据只能是唯一的不可重复
可以是单列,可以是组合列(就叫表级)
所以唯一性约束它可以是列级别,可以是表级别
但unique规定的列只有一列时可以在列级别定义
如果unique规定的列包含多列时只能在表级别定义
创建唯一约束 即自动创建索引

SQL> create table t1(id number unique,
name varchar2(10),
mail varchar2(20),
constraints my_un unique(name,mail));

SQL> selectTABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE from user_constraintswhere table_name='T1';

TABLE_NAME CONSTRAINT_NAMEC
---------- -------------------- -
T1SYS_C005210U
T1MY_UNU

SQL>

SQL> selectTABLE_NAME,CONSTRAINT_NAME,COLUMN_NAME from user_cons_columns whereTABLE_NAME='T1';

TABLE_NAME CONSTRAINT_NAMECOLUMN_NAME
---------- -------------------- ----------------
T1MY_UNNAME
T1MY_UNMAIL
T1SYS_C005210ID

SQL>

SQL> select TABLE_NAME,INDEX_NAME,INDEX_TYPE fromuser_indexes where table_name='T1';

TABLE_NAMEINDEX_NAMEINDEX_TYPE
---------- ---------------------------------------------------------
T1SYS_C005210NORMAL
T1MY_UNNORMAL

SQL> 索引自动创建 和约束同名

SQL> insert into t1values(0,'ROOT','ROOT@UP.COM');

1 row created.

SQL> insert into t1values(0,'ROOT','ROOT@UP.COM');
insert into t1 values(0,'ROOT','ROOT@UP.COM')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C005210) violated

SQL> insert into t1values(1,'ROOT','ROOT@UP.COM');
insert into t1 values(1,'ROOT','ROOT@UP.COM')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.MY_UN) violated

SQL> insert into t1values(1,'SEKER','ROOT@UP.COM');

1 row created.

SQL> insert into t1values(2,'SEKER','SEKER@UP.COM');

1 row created.

SQL> 因为name和mail是联合唯一的只name列重复被忽略

SQL> insert into t1 values(null,null,null);

1 row created.

SQL> insert into t1 values(null,null,null);

1 row created.

SQL> insert into t1 values(null,null,null);

1 row created.

SQL>

null能多次插入,为什么重复的null可以呢?

主键约束
主键和唯一共同点:
都是唯一的可以单列也可以多列
都是自动创建索引

主键和唯一不同点:
unique可以为空
primary key 不可以为空

一般来说 每个表都应该有自己的主键列 而且最好是单列的

SQL> create table t2 (id number constraintt2_PK_id primary key,name varchar2(10));

Table created.

SQL> selectTABLE_NAME,CONSTRAINT_NAME,COLUMN_NAME from user_cons_columns whereTABLE_NAME='T2';

TABLE_NAME CONSTRAINT_NAMECOLUMN_NAME
---------- -------------------- ----------------
T2T2_PK_IDID

SQL> select TABLE_NAME,INDEX_NAME,INDEX_TYPE fromuser_indexes where table_name='T2';

TABLE_NAMEINDEX_NAMEINDEX_TYPE
---------- ---------------------------------------------------------
T2T2_PK_IDNORMAL

SQL> insert into t2 values(1,'SEKER');

1 row created.

SQL> insert into t2 values(1,'SEKER');
insert into t2 values(1,'SEKER')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.T2_PK_ID) violated

SQL> insert into t2 values(null,'SEKER');
insert into t2 values(null,'SEKER')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."T2"."ID")

SQL>

外键约束

例如 emp.deptno 被 dept.deptno值包含
如果你删除dept中deptno=10
从数据变更角度来讲没问题 但它的业务逻辑出现了错误 没有部门哪来的员工呢??
这就叫数据的完整性
这就是外键的作用 参考主键来维护数据完整性
参考主键的值 外键列中的值被主键中的值所包含(子集)
只能建立在表级别 不允许写在列的类型关键字后面
否则会报: ORA-02253: constraintspecification not allowed here
而且参考列一定是唯一或主键
否则会报: ORA-02270: no matchingunique or primary key for this column-list

创建外键约束

SQL> create table my_dept as select * from dept;

SQL> alter table my_dept modify(deptno numberprimary key);

SQL> create table my_emp (
empno number,
ename varchar2(20),
deptnonumber,
constraintmy_emp_fk_dno foreign key(deptno) references my_dept(deptno)
);

SQL> select TABLE_NAME,CONSTRAINT_NAME,COLUMN_NAMEfrom user_cons_columns where TABLE_NAME='MY_EMP';

TABLE_NAME CONSTRAINT_NAMECOLUMN_NAME
---------- -------------------- ----------------
MY_EMPMY_EMP_FK_DNODEPTNO

SQL> select TABLE_NAME,INDEX_NAME,INDEX_TYPE fromuser_indexes where table_name='MY_EMP';

no rows selected

SQL> 外键不会建立索引的 但一般来说 外键也是必须要有索引的 因为级联删除时会用到

违反约束
SQL> insert into my_emp values(2,'ROOT',50);
insert into my_emp values(2,'ROOT',50)
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.MY_EMP_FK_DNO) violated -parent key not found

SQL>

删除约束
SQL> alter table my_emp drop constraintMY_EMP_FK_DNO;

Table altered.

SQL>

外键的级联
可以设置主键在被删除时 外键的对应操作
操作包含:
设置null
级联删除

SQL> select * from my_emp;

EMPNOENAMEDEPTNO
---------- -------------------- ----------
1 ROOT10
2 ROOT20
2 ROOT40
3 ROOT30

SQL> commit;

Commit complete.

SQL>

SQL> alter table my_emp add constraint fk_dnoforeign key (deptno) references my_dept(deptno) on delete setnull;

Table altered.

SQL> delete my_dept where deptno=10;

1 row deleted.

SQL> select * from my_emp;

EMPNOENAMEDEPTNO
---------- -------------------- ----------
1 ROOT
2 ROOT20
2 ROOT40
3 ROOT30

SQL> 主键删除 外键被置空

SQL> roll
Rollback complete.
SQL> select TABLE_NAME,CONSTRAINT_NAME,COLUMN_NAMEfrom user_cons_columns where TABLE_NAME='MY_EMP';

TABLE_NAME CONSTRAINT_NAMECOLUMN_NAME
---------- -------------------- ----------------
MY_EMPFK_DNODEPTNO

SQL> alter table my_emp drop constraint fk_dno;

Table altered.

SQL> alter table my_emp add constraint fk_dnoforeign key(deptno) references my_dept(deptno) on deletecascade;

Table altered.

SQL> select * from my_emp;

EMPNOENAMEDEPTNO
---------- -------------------- ----------
1 ROOT10
2 ROOT20
2 ROOT40
3 ROOT30

SQL> delete my_dept where deptno=10;

1 row deleted.

SQL> select * from my_emp;

EMPNOENAMEDEPTNO
---------- -------------------- ----------
2 ROOT20
2 ROOT40
3 ROOT30

SQL> 主键删除 外键被级联删除

CHECK 约束
其实就是自定义约束
业务需要限制不符合规范的数据输入
例如 性别列只允许 男或女 不接受其他值
工资不能低于人均标准工资等等
接受空值

SQL> create table t3 (ename varchar2(10),
sex varchar2(5) check (sex='男' or sex='女'),
sal number check(sal >=1000)
);

Table created.

SQL> insert into t3 values('ROOT','f',1000);
insert into t3 values('ROOT','f',1000)
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.SYS_C005217) violated

SQL> insert into t3 values('PG','女',100);
insert into t3 values('PG','女',100)
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.SYS_C005218) violated

SQL> insert into t3 values('PG','女',1000);

1 row created.

SQL>
SQL> insert into t3 values('BLUES',null,1000);

1 row created.

SQL> 要屏蔽null需要加非空 sex is notnull

SQL> truncate table t3;

SQL> alter table t3 modify(sex varchar2(5) check((sex='女' or (sex='男')) and sex is not null));

Table altered.

SQL> insert into t3values('BLUES',null,1000);
insert into t3 values('BLUES',null,1000)
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.SYS_C005222) violated

SQL>

延迟约束
延迟到commit时检测约束
比如批量装载数据 数据量很大 里面又包含违反约束的行
这时启用延迟约束 先将数据全装载进表 再剔除违反约束的行 再提交
这就是延迟约束存在的价值
SQL> create table t4 as select * from emp where0=9;

Table created.

SQL> alter table t4 modify (deptno numberunique);

Table altered.

SQL> selectTABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS,DEFERRABLE,DEFERREDfrom user_constraints where table_name='T4';

TABLE_NAME CONSTRAINT_NAMECSTATUSDEFERRABLE DEFERRED
---------- -------------------- - -------- -----------------------
T4SYS_C005223UENABLED NOT DEFERRABLE IMMEDIATE

SQL> insert into t4 select * from emp;
insert into t4 select * from emp
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C005223) violated

SQL> 违反了约束 因为当前约束是enable的

SQL> alter table t4 modify (deptno number uniqueinitially deferred deferrable);
alter table t4 modify (deptno number unique initially deferreddeferrable)
*
ERROR at line 1:
ORA-02261: such unique or primary key already exists in thetable

不可以修改 因为同类型的约束 已经存在 初始条件限制只能先删再加,.
或者用SQL> set constraints all immediate;
SQL> set constraints all deferred;

SQL> alter table t4 drop constraint SYS_C005223;

Table altered.

SQL> alter table t4 modify (deptno number uniqueinitially deferred deferrable);

Table altered.
SQL> selectTABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS,DEFERRABLE,DEFERREDfrom user_constraints where table_name='T4';

TABLE_NAME CONSTRAINT_NAMECSTATUSDEFERRABLE DEFERRED
---------- -------------------- - -------- -----------------------
T4SYS_C005224UENABLEDDEFERRABLE DEFERRED

SQL>
SQL> insert into t4 select * from emp;

14 rows created.

SQL> commit;
commit

*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-00001: unique constraint (SCOTT.SYS_C005224) violated

SQL> select count(*) from t4;

COUNT(*)
----------
0

SQL>

约束的状态
SQL> drop table t5 purge;

Table dropped.

SQL> create table t5 as select * from emp where0=9;

Table created.

SQL> alter table t5 modify(deptno numberunique);

Table altered.

SQL> selectTABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS,VALIDATED,DEFERRABLE,DEFERREDfrom user_constraints where table_name='T5';

TABLE_NAME CONSTRAINT_NAMECSTATUSVALIDATEDDEFERRABLEDEFERRED
---------- -------------------- - -------- --------------------------- ---------
T5SYS_C005226UENABLED VALIDATED NOTDEFERRABLE IMMEDIATE

SQL> select table_name,index_name,index_type fromuser_indexes where table_name='T5';

TABLE_NAMEINDEX_NAMEINDEX_TYPE
---------- ---------------------------------------------------------
T5SYS_C005226NORMAL

SQL> alter table t5 disable constraintSYS_C005226;

Table altered.

SQL> selectTABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS,VALIDATED,DEFERRABLE,DEFERREDfrom user_constraints where table_name='T5';

TABLE_NAME CONSTRAINT_NAMECSTATUSVALIDATEDDEFERRABLEDEFERRED
---------- -------------------- - -------- --------------------------- ---------
T5SYS_C005226U DISABLED NOTVALIDATED NOT DEFERRABLE IMMEDIATE

SQL> select table_name,index_name,index_type fromuser_indexes where table_name='T5';

no rows selected

*注意　停掉约束后　约束自带的索引会被删除
SQL> alter table t5 enable constraintSYS_C005226;

Table altered.

SQL> selectTABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS,VALIDATED,DEFERRABLE,DEFERREDfrom user_constraints where table_name='T5';

TABLE_NAME CONSTRAINT_NAMECSTATUSVALIDATEDDEFERRABLEDEFERRED
---------- -------------------- - -------- --------------------------- ---------
T5SYS_C005226UENABLED VALIDATED NOTDEFERRABLE IMMEDIATE

SQL> select table_name,index_name,index_type fromuser_indexes where table_name='T5';

TABLE_NAMEINDEX_NAMEINDEX_TYPE
---------- ---------------------------------------------------------
T5SYS_C005226NORMAL

SQL> 　启用后　索引重新建立

约束容错方法

SQL> alter table t5 disable constraintSYS_C005226;

Table altered.

SQL> selectTABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS,VALIDATED,DEFERRABLE,DEFERREDfrom user_constraints where table_name='T5';

TABLE_NAME CONSTRAINT_NAMECSTATUSVALIDATEDDEFERRABLEDEFERRED
---------- -------------------- - -------- --------------------------- ---------
T5SYS_C005226U DISABLED NOTVALIDATED NOT DEFERRABLE IMMEDIATE

SQL>
SQL> insert into t5 select * from emp whereename='KING';

1 row created.

SQL> commit;

Commit complete.

SQL> alter table t5 enable constraintSYS_C005226;

Table altered.

SQL> 列不违反约束　所以没问题

SQL> alter table t5 disable constraintSYS_C005226;

Table altered.

SQL> select ename,deptno from emp wheredeptno=10;

ENAMEDEPTNO
---------- ----------
CLARK10
KING10
MILLER10

SQL> insert into t5 select * from emp whereename='MILLER';

1 row created.

SQL> commit;

SQL> alter table t5 enable constraintSYS_C005226;
alter table t5 enable constraint SYS_C005226
*
ERROR at line 1:
ORA-02299: cannot validate (SCOTT.SYS_C005226) - duplicate keysfound

SQL> 无法启用　数据违反了约束

让约束对已有数据容错　只对新数据检测　手动建立普通索引即可
SQL> create index i5 on t5(deptno);

Index created.

SQL> alter table t5 disable constraintSYS_C005226 using index i5;
alter table t5 disable constraint SYS_C005226 using index i5
*
ERROR at line 1:
ORA-00933: SQL command not properly ended

SQL> alter table t5 enable constraint SYS_C005226using index i5;
alter table t5 enable constraint SYS_C005226 using index i5
*
ERROR at line 1:
ORA-02299: cannot validate (SCOTT.SYS_C005226) - duplicate keysfound

SQL> alter table t5 enable novalidate constraintSYS_C005226 using index i5;

Table altered.

对已有数据容错了
SQL> select deptno from t5;

DEPTNO
----------
10
10

SQL> insert into t5 select * from emp whereename='CLARK';
insert into t5 select * from emp where ename='CLARK'
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C005226) violated

SQL> 但新数据被抵制

找出违反约束的列
为了批量装载数据　通常都是关闭约束　之后再开启
如果装载的数据量太大　又存在违反约束的情况　此时无法启用约束
又很难再批量数据中定位违反约束的行
此时oracle提供一种简单方法 利用约束启动时的检测过程 将违反的约束行存起来

SQL> @?/rdbms/admin/utlexpt1.sql

Table created.

SQL>
SQL> create table t9 as select * from emp ;

Table created.

SQL>

SQL> alter table t9 modify(empno number uniquedisable);

Table altered.

SQL>
SQL> update t9 set empno=7788 whereename='KING';

1 row updated.

SQL> commit;

Commit complete.

SQL> select empno,ename from t9 whereempno=7788;

EMPNO ENAME
---------- ----------
7788 SCOTT
7788 KING

SQL>
SQL> selectTABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS,VALIDATED,DEFERRABLE,DEFERREDfrom user_constraints where table_name='T9';

TABLE_NAME CONSTRAINT_NAMECSTATUSVALIDATEDDEFERRABLEDEFERRED
---------- -------------------- - -------- --------------------------- ---------
T9SYS_C005232U DISABLED NOTVALIDATED NOT DEFERRABLE IMMEDIATE

SQL>
SQL> alter table t9 enable constraint SYS_C005232EXCEPTIONS into EXCEPTIONS;
alter table t9 enable constraint SYS_C005232 EXCEPTIONS intoEXCEPTIONS
*
ERROR at line 1:
ORA-02299: cannot validate (SCOTT.SYS_C005232) - duplicate keysfound

SQL>
SQL> col row_id for a20
SQL> select * from exceptions wheretable_name='T9';

ROW_IDOWNERTABLE_NAME CONSTRAINT
-------------------- ---------- ----------------------------------------
AAAMnTAAEAAAAG0AAISCOTTT9SYS_C005232
AAAMnTAAEAAAAG0AAHSCOTTT9SYS_C005232

SQL> select rowid,empno,ename from T9 where rowidin ('AAAMnTAAEAAAAG0AAI','AAAMnTAAEAAAAG0AAH');

ROWIDEMPNOENAME
------------------ ---------- ----------
AAAMnTAAEAAAAG0AAH 7788 SCOTT
AAAMnTAAEAAAAG0AAI 7788 KING

SQL>

爱华网本文地址 » http://www.aihuau.com/a/25101017/335614.html