修改表的约束条件及默认索引的处理

http://blog.csdn.net/zgmzyr/article/details/6659919

 

转载于

drop index时出现如下错误：

SQL> drop index oos_index;

drop index oos_index

*

ERROR at line 1:

ORA-02429: cannot drop index used for enforcement of unique/primary key

我们知道当创建Primary key和unique约束时，如果在该key上不存在索引，则Oracle会自动创建对应的unique索引，而当你要删除该索引时，必须先Disable或Drop该约束。看下面的例子：

SQL>CREATE TABLE employees
2 (
3 empno NUMBER(6) PRIMARY KEY,
4 name VARCHAR2(30),
5 dept_no NUMBER(2)
6 );

Table created.

SQL> select index_name,owner,table_NAME from all_indexes where owner=’SFA’ AND table_name=’EMPLOYEES’;

INDEX_NAME OWNER TABLE_NAME
———————- ——————- —————–
SYS_C007594 SFA EMPLOYEES

SQL> SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,INDEX_NAME FROM ALL_CONSTRAINTS WHERE TABLE_NAME=’EMPLOYEES’;

CONSTRAINT_NAME C TABLE_NAME INDEX_NAME
—————————— — ————————- ——————
SYS_C007594 P EMPLOYEES SYS_C007594

SQL> DROP INDEX SYS_C007594;
DROP INDEX SYS_C007594
*
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key

SQL> ALTER TABLE employees
2 MODIFY PRIMARY KEY DISABLE;

Table altered.

SQL> select index_name,owner,table_NAME from all_indexes where owner=’SFA’ AND table_name=’EMPLOYEES’;

no rows selected

SQL> SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,INDEX_NAME FROM ALL_CONSTRAINTS WHERE TABLE_NAME=’EMPLOYEES’;

CONSTRAINT_NAME C TABLE_NAME INDEX_NAME
—————————— — ————————- ——————
SYS_C007594 P EMPLOYEES SYS_C007594

SQL> ALTER TABLE employees
2 MODIFY PRIMARY KEY ENABLE;

Table altered.

SQL> select index_name,owner,table_NAME from all_indexes where owner=’SFA’ AND table_name=’EMPLOYEES’;

INDEX_NAME OWNER TABLE_NAME
——————- —————– ————-
SYS_C007594 SFA EMPLOYEES

从上面可以看出，如果创建了Primary Key约束，则Oracle会自动帮你创建相应的unique索引。当把Primary Key约束Disable时会自动删除对应的Unique索引，而重新将该约束Enable时，Oracle会重建
Unique索引。特别要注意：当Disable PK或Unique约束时，Oracle只会删除对应的Unique索引。可参考Oracle文档中的解释：

If you enable a unique or primary key constraint, and if no index exists on the key, Oracle creates a unique index. This index is dropped if the constraint is subsequently disabled, and Oracle rebuilds the index every time the constraint is enabled.

To avoid rebuilding the index and eliminate redundant indexes, create new primary key and unique constraints initially disabled. Then create (or use existing) nonunique indexes to enforce the constraint. Oracle does not drop a nonunique index when the constraint is disabled, so subsequent ENABLE operations are facilitated.

在Oracle 9i中，用于支持Primary Key和Unique Key约束的索引可独立于约束本身，实现方法是在CREATE TABLE或ALTER TABLE时指定USING INDEX子句，例子如下：

SQL> CREATE TABLE employees
2 (empno NUMBER(6),
3 name VARCHAR2(30),
4 dept_no NUMBER(2),
5 CONSTRAINT emp_pk PRIMARY KEY(empno)
6 USING INDEX
7 (CREATE INDEX emp_pk_idx ON employees(empno) TABLESPACE indx)
8 );

Table created.

SQL> select index_name,owner,table_NAME from all_indexes where owner=’SFA’ AND table_name=’EMPLOYEES’;

INDEX_NAME OWNER TABLE_NAME
————————— ————- ———————-
EMP_PK_IDX SFA EMPLOYEES

SQL> SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,INDEX_NAME FROM ALL_CONSTRAINTS WHERE TABLE_NAME=’EMPLOYEES’;

CONSTRAINT_NAME C TABLE_NAME INDEX_NAME
—————————— — ——————– —————
EMP_PK P EMPLOYEES EMP_PK_IDX

这样做的好处是：

1。可将索引存储在指定的表空间中，从而与表分离

2。通过创建一个非唯一索引，让PK或Unique Key使用，可避免在Enable或Disable PK或Unique Key时重建索引，同时可以消除多余的索引。

在删除约束时可选择保留索引：
1。ALTER TABLE employees DROP PRIMARY KEY KEEP INDEX;
2。ALTER TABLE employees DROP CONSTRAINT emp_pk;–对应的索引必须为非唯一索引

----表的约束部分http://www.cnblogs.com/ljcbest/archive/2010/09/02/1816346.html

oracle Constraint

约束简介
约束用于确保数据库数据满足特定的商业逻辑或者企业规则，如果定义了约束，并且数据不符

合约束，那么DML操作（INSERT、UPDATE、DELETE）将不能成功执行。约束包括NOT NULL、UNIQUE、PRIMARY KEY、FOREING KEY 以及CHECK等五种类型

定义约束
列级约束：
column [CONSTRAINT constraint_name] constraint_type
表级约束：
column ,...,
[CONSTRAINT constraint_name] constraint_type (column,...)

1.定义NOT NULL约束
NOT NULL 约束只能在列级定义，不能在表级定义
例：
CREATE TABLE emp01(
eno INT NOT NULL,
name VARCHAR2(10) CONSTRAINT nn_name2 NOT NULL,
salary NUMBER(6,2)
);
上例中，eno列与name列上定义了NOT NULL约束，其中eno列的NOT NULL约束没有指定约束名，而

name列则指定了约束名nn_name。
可以通过查询user_constraints字典来查看所定义的约束，如：
SQL> select constraint_name,constraint_type from user_constraints
2 where table_name='EMP01';

CONSTRAINT_NAME CO
------------------------------------------------------------ --
SYS_C0010618 C
NN_NAME C
可见，没有给约束名的，系统将会自动的定义一个约束名称，其中约束类型中，P:表示主键，R:

表示外键，C表示NOT NULL或CHECK，U表示UNIQUE。且系统中同一方案下的的约束名不能重复

2.定义UNIQUE约束
定义了惟一约束之后，惟一约束列的列值不能重复，但可以为NULL。UNIQUE约束既可以在列级

定义，也可以在表级定义
例：
CREATE TABLE emp02(
eno INT UNIQUE,name VARCHAR2(10) CONSTRAINT u_name UNIQUE,
salary NUMBER(6,2)
);
同样，如果没有给出约束名，系统会自动定义一个名称，可从查询结果得出
SQL> select constraint_name,constraint_type from user_constraints
2 where table_name='EMP02';

CONSTRAINT_NAME CO
------------------------------------------------------------ --
SYS_C0010623 U
U_NAME
U

3.定义PRIMARY KEY 约束
当定义主键约束后，主键约束列的列值不仅不能重复，而且也不能为NULL。主键约束既可以在

列级定义，也可以在表级定义。一张表最多只能具有一个主键约束，当一个表中的多个列都要为

主键是，可以在表级定义。
例1：列级定义主键
CREATE TABLE depto04(
dno INT PRIMARY KEY,
dname VARCHAR2(10),loc VARCHAR2(20)
);
通过查询user_constraints可得出
SQL> select constraint_name ,constraint_type from user_constraints
2 where table_name='DEPT04';

CONSTRAINT_NAME CO
------------------------------------------------------------ --
SYS_C0010625 P
通过查询user_cons_columns可得出
SQL> select constraint_name,column_name from user_cons_columns
2 where table_name='DEPT04';

CONSTRAINT_NAME COLUMN_NAME
------------------------------------------------------------ ------------

SYS_C0010625 DNO
例2：表级定义主键
CREATE TABLE dept05(
dno INT,
dname VARCHAR2(10),loc VARCHAR2(20),
PRIMARY KEY(dno,dname)
);
通过查询user_constraints可以得出
SQL> select constraint_name,constraint_type from user_constraints
2 where table_name='DEPT05';

CONSTRAINT_NAME CO
------------------------------------------------------------ --
SYS_C0010626 P
通过查询user_cons_columns可得出
SQL> select constraint_name,column_name from user_cons_columns
2 where table_name='DEPT05';

CONSTRAINT_NAME COLUMN_NAME
------------------------------------------------------------ -------------

SYS_C0010626 DNO
SYS_C0010626 DNAME
可见，当在表级定义时，约束名称将会相同，而在user_cons_columns查询是，每一列将对应约束

名称相同的两条记录

4.定义FOREING KEY约束
当定义了外部键约束之后，要求外部键列的数据必须在主表的主键列（或惟一列）中存在，或

者为NULL,FOREING KEY约束既可以在列级定义，也可以在表级定义。
关键字说明：
FOREING KEY:该选项用于指定在表级定义外部键约束。当在表级定义外部键约束时必须指定该选

项，在列级定义外部键约束不需要指定该选项
REFERENCES:该选项用于指定主表名及其主键列。当定义外部键约束时，该选项必须指定。
ON DELETE CASCAED:该选项用于指定级联删除选项。如果在定义外部键约束时指定了该选项，那

么当删除主表数据时会级联删除从表的相关数据。
ON DELECT SET NULL:该选项用于指定转换相关的外部键值为NULL，如果在定义外部键约束时指定

了该选项，那么当删除主表数据时会将从表外部键列的数据设置为NULL。
例1：列级定义外键约束
CREATE TABLE emp04(
eno INT,name VARCHAR2(10),salary NUMBER(6,2),
dno INT CONSTRAINT fk_dno REFERENCES dept04(dno)
);
SQL> select constraint_name,constraint_type from user_constraints
2 where table_name='EMP04';

CONSTRAINT_NAME CO
------------------------------------------------------------ --
FK_DNO R
SQL> select constraint_name,column_name from user_cons_columns
2 where table_name='EMP04';

CONSTRAINT_NAME COLUMN_NAME
------------------------------------------------------------ -----------

FK_DNO DNO
例2：表级定义外键约束
CREATE TABLE emp05(
eno INT,name VARCHAR2(10),salary NUMBER(6,2),
dno INT,
CONSTRAINT fk_dno_name FOREIGN KEY(dno,name) REFERENCES dept05(dno,dname)
);
SQL> select constraint_name,constraint_type from user_constraints
2 where table_name='EMP05'
3 ;

CONSTRAINT_NAME CO
------------------------------------------------------------ --
FK_DNO_NAME R
SQL> select constraint_name,column_name from user_cons_columns
2 where table_name='EMP05';

CONSTRAINT_NAME COLUMN_NAME
------------------------------------------------------------ -----------

FK_DNO_NAME DNO
FK_DNO_NAME NAME

5.定义CHECK约束
CHECK约束既可以在列级定义，也可以在表级定义。CHECK约束允许列为NULL。
例：
CREATE TABLE emp06(
eno INT,name VARCHAR2(10),salary NUMBER(6,2),
CHECK (salary BETWEEN 1000 AND 5000)
);
SQL> select constraint_name,constraint_type from user_constraints
2 where table_name='EMP06';

CONSTRAINT_NAME CO
------------------------------------------------------------ --
SYS_C0010629 C
SQL> select constraint_name,column_name from user_cons_columns
2 where table_name='EMP06';

CONSTRAINT_NAME COLUMN_NAME
------------------------------------------------------------ -----------

SYS_C0010629 SALARY

6.定义复合约束
复合约束即在表级定义，基于多列的复合约束，如：
CREATE TABLE dept05(
dno INT,
dname VARCHAR2(10),loc VARCHAR2(20),
PRIMARY KEY(dno,dname)
);或
CREATE TABLE emp05(
eno INT,name VARCHAR2(10),salary NUMBER(6,2),
dno INT,
CONSTRAINT fk_dno_name FOREIGN KEY(dno,name) REFERENCES dept05(dno,dname)
);

9.3维护约束
9.3.1增加约束
如果增加UNIQUE、PRIMARY KEY、FOREIGN KEY 和CKECK 必须使用ALTER TABLE语句的ADD子句;

如果增加NOT NULL约束，那么必须使用ALTER TABLE语句的MODIFY子句，如：
ALTER TABLE table_name ADD [CONSTRAINT constraint_name]
constraint_type (column,...)
ALTER TABLE table_name MODIFY column
[CONSTRAINT constraint_name] NOT NULL;
9.3.2修改约束名
在同一个方案中，约束名必须惟一,并且约束名也不能与其他对象同名。当鱫IMPDP工具或者IMP

工具导入其他对象时，如发现有同名的对象，将会出错
语法：
ALTER TABLE table_name RENAME CONSTRAINT old_constraint_name
TO new_constraint_name;
例：
ALTER TABLE emp01 RENAME CONSTRAINT SYS_C005028
TO ck_emp01_salary;
9.3.3 删除约束
当删除特定表的主键约束时，如果该表具有相关的从表，那么在删除主键约束

时必须带有CASCAED选项
语法：
ALTER TABLE table_name DROP
CONSTRAINT constraint_name |PRIMARY KEY
例1
ALTER TABLE emp01 DROP CONSTRAINT ck_emp01_salary;
例2
ALTER TABLE dept01 DROP PRIMARY KEY CASCAED
9.3.4禁止约束
禁止约束指使约束临时失效。当禁止了约束之后，约束规则将不再生效。在使用SQL*LOADER或

INSERT装载数据之前，为了加快数据装载速度，应该首先禁止约束，然后装载数据。
语法：
ALTER TABLE table_name
DISABLE CONSTRAINT constaint_name [CASCAED];--CASCAED用于指定级联禁止从表的外部键

约束
例：
ALTER TABLE emp05 DISABLE CONSTAINT SYS_C00502;
9.3.5 激活约束
语法：
ALTER TABLE table_name ENABLE CONSTRAINT constraint_name;
例：
ALTER TABLE emp05 ENABLE CONSTRAINT SYS_C005022;
9.4 显示约束信息
1.USER_CONSTRAINTS
2.USER_CONS_COLUMNS