从 Oracle DB 中查找幽灵约束
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2372163/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Finding ghost constraint from Oracle DB
提问by simon
I had a constraint in a table
我在表中有一个约束
CREATE TABLE "USERSAPPLICATIONS" (
"USERID" NUMBER NOT NULL ,
"APPLICATIONNAME" VARCHAR2 (30) NOT NULL ,
CONSTRAINT "PK_USERSAPPLICATIONS" PRIMARY KEY ("USERID","APPLICATIONNAME")
)
/
Two weeks ago I modified the table, added some columns, deleted the constraint "PK_USERSAPPLICATIONS" and added a surrogate key. I can see in Oracle SQL Developer that the constraint PK_USERSAPPLICATIONS does not exist anymore.
两周前我修改了表，添加了一些列，删除了约束“PK_USERSAPPLICATIONS”并添加了一个代理键。我可以在 Oracle SQL Developer 中看到约束 PK_USERSAPPLICATIONS 不再存在。
Regardless of that, when I try to add two entries with the same userid/applicationName combination, I get an error
无论如何，当我尝试添加两个具有相同 userid/applicationName 组合的条目时，出现错误
SQL Error: ORA-00001: unique constraint (ACCOUNTMP1.PK_USERSAPPLICATIONS) violated
00001. 00000 - "unique constraint (%s.%s) violated"
*Cause: An UPDATE or INSERT statement attempted to insert a duplicate key.
For Trusted Oracle configured in DBMS MAC mode, you may see
this message if a duplicate entry exists at a different level.
*Action: Either remove the unique restriction or do not insert the key.
When I execute the statement
当我执行语句时
SELECT *
FROM user_cons_columns
WHERE constraint_name = 'PK_USERSAPPLICATIONS'
I get zero rows. How can that be? Oracle shouldn't have any knowledge of the constraint PK_USERSAPPLICATIONS as it has been deleted already weeks ago, and I cannot see it in the database either.
我得到零行。怎么可能？Oracle 不应该对约束 PK_USERSAPPLICATIONS 有任何了解，因为它已经在几周前被删除了，而且我也无法在数据库中看到它。
回答by APC
Do you still have the index which was used by that constraint? Because unless you included the DROP INDEXclause when you dropped the constraint it will still be there. Start with
你还有那个约束使用的索引吗？因为除非您DROP INDEX在删除约束时包含该子句，否则它仍然存在。从...开始
SELECT *
FROM user_indexes
WHERE index_name = 'PK_USERSAPPLICATIONS'
/
Alternatively,
或者，
select index_name
from user_indexes
where table_name = 'USERSAPPLICATIONS'
and uniqueness='UNIQUE'
/
or
或者
select index_name
from user_ind_columns
where table_name = 'USERSAPPLICATIONS'
and column_name in ('USERID' ,'APPLICATIONNAME')
/
edit
编辑
Proof of concept
概念证明
SQL> create table t23 (id number not null, alt_key varchar2(10) not null)
2 /
Table created.
SQL> create unique index t23_idx on t23 (id)
2 /
Index created.
SQL> alter table t23 add constraint t23_pk primary key (id) using index
2 /
Table altered.
SQL> insert into t23 values (1, 'SAM I AM')
2 /
1 row created.
SQL> insert into t23 values (1, 'MR KNOX')
2 /
insert into t23 values (1, 'MR KNOX')
*
ERROR at line 1:
ORA-00001: unique constraint (APC.T23_PK) violated
SQL>
So the constraint works. What happens if we drop it, without the DROP INDEX clause?
所以约束有效。如果我们在没有 DROP INDEX 子句的情况下删除它会发生什么？
SQL> alter table t23 drop constraint t23_pk
2 /
Table altered.
SQL> insert into t23 values (1, 'MR KNOX')
2 /
insert into t23 values (1, 'MR KNOX')
*
ERROR at line 1:
ORA-00001: unique constraint (APC.T23_IDX) violated
SQL>
Note the subtle change in the error message. The second failure references the index name, whereas the original message referenced the constraint. If the index name is the same as the constraint name it would be hard to diagnose this.
请注意错误消息中的细微变化。第二个失败引用了索引名称，而原始消息引用了约束。如果索引名称与约束名称相同，则很难对此进行诊断。
If you don't explicitly pre-create the unique index Oracle's default behaviour is to create a non-unique index. Consequently, dropping the constraint without dropping the index does not cause this problem. (Caveat this behaviour is true of 11g. I presume - but cannot be sure - that it is also this way in earlier versions).
如果您没有明确预先创建唯一索引，Oracle 的默认行为是创建一个非唯一索引。因此，删除约束而不删除索引不会导致此问题。（请注意，这种行为适用于 11g。我认为 - 但不能确定 - 在早期版本中也是如此）。
回答by glebreutov
Try to check for index for this columns. In some cases index associated with constraint isn't dropped after constraint deletion
尝试检查此列的索引。在某些情况下，删除约束后不会删除与约束关联的索引
