Sometimes I get a unique key violation error for an index that isn't unique.
有时，我得到一个不唯一的索引的唯一违反键错误。
I have an Oracle 10g database.
我有一个Oracle 10g数据库。
There are two indexes on different tables for which is happens. Their details are:
在不同的表上有两个索引。他们的细节:
CREATED 14-JAN-14 20.14.11
LAST_DDL_TIME 14-JAN-14 20.14.11
OWNER MEDDBA
INDEX_NAME STATUS_REQUEST
INDEX_TYPE NORMAL
TABLE_OWNER MEDDBA
TABLE_NAME STATUS
TABLE_TYPE TABLE
UNIQUENESS NONUNIQUE
COMPRESSION DISABLED
PREFIX_LENGTH
TABLESPACE_NAME MEDTBS
INI_TRANS 2
MAX_TRANS 255
INITIAL_EXTENT 65536
NEXT_EXTENT 1048576
MIN_EXTENTS 1
MAX_EXTENTS 2147483645
PCT_INCREASE
PCT_THRESHOLD
INCLUDE_COLUMN
FREELISTS
FREELIST_GROUPS
PCT_FREE 10
LOGGING YES
BLEVEL 1
LEAF_BLOCKS 89
DISTINCT_KEYS 67
AVG_LEAF_BLOCKS_PER_KEY 1
AVG_DATA_BLOCKS_PER_KEY 2
CLUSTERING_FACTOR 190
STATUS VALID
NUM_ROWS 8161
SAMPLE_SIZE 8161
LAST_ANALYZED 01-APR-14 22.00.09
DEGREE 1
INSTANCES 1
PARTITIONED NO
TEMPORARY N
GENERATED N
SECONDARY N
BUFFER_POOL DEFAULT
USER_STATS NO
DURATION
PCT_DIRECT_ACCESS
ITYP_OWNER
ITYP_NAME
PARAMETERS
GLOBAL_STATS YES
DOMIDX_STATUS
DOMIDX_OPSTATUS
FUNCIDX_STATUS
JOIN_INDEX NO
IOT_REDUNDANT_PKEY_ELIM NO
DROPPED NO
CREATED 14-JAN-14 20.14.04
LAST_DDL_TIME 14-JAN-14 20.14.04
OWNER MEDDBA
INDEX_NAME SEQUENCE_ID2
INDEX_TYPE NORMAL
TABLE_OWNER MEDDBA
TABLE_NAME SEQUENCE
TABLE_TYPE TABLE
UNIQUENESS NONUNIQUE
COMPRESSION DISABLED
PREFIX_LENGTH
TABLESPACE_NAME MEDTBS
INI_TRANS 2
MAX_TRANS 255
INITIAL_EXTENT 65536
NEXT_EXTENT 1048576
MIN_EXTENTS 1
MAX_EXTENTS 2147483645
PCT_INCREASE
PCT_THRESHOLD
INCLUDE_COLUMN
FREELISTS
FREELIST_GROUPS
PCT_FREE 10
LOGGING YES
BLEVEL 1
LEAF_BLOCKS 43
DISTINCT_KEYS 1
AVG_LEAF_BLOCKS_PER_KEY 43
AVG_DATA_BLOCKS_PER_KEY 120
CLUSTERING_FACTOR 120
STATUS VALID
NUM_ROWS 7795
SAMPLE_SIZE 7795
LAST_ANALYZED 01-APR-14 22.00.10
DEGREE 1
INSTANCES 1
PARTITIONED NO
TEMPORARY N
GENERATED N
SECONDARY N
BUFFER_POOL DEFAULT
USER_STATS NO
DURATION
PCT_DIRECT_ACCESS
ITYP_OWNER
ITYP_NAME
PARAMETERS
GLOBAL_STATS YES
DOMIDX_STATUS
DOMIDX_OPSTATUS
FUNCIDX_STATUS
JOIN_INDEX NO
IOT_REDUNDANT_PKEY_ELIM NO
DROPPED NO
The error:
错误:
java.lang.RuntimeException: org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL []; ORA-00001: unique constraint (MEDDBA.STATUS_REQUEST) violated; nested exception is java.sql.SQLException: ORA-00001: unique constraint (MEDDBA.STATUS_REQUEST) violated
2 个解决方案
#1
0
The error message tells you there's a unique CONSTRAINT. You can have a constraint and index with the same name, e.g.
错误消息告诉您有一个惟一的约束。可以有一个同名的约束和索引。
create table t1(pk number);
create index t1_idx on t1(pk);
alter table t1 add constraint t1_idx unique(pk);
insert into t1 values(1);
insert into t1 values(1);
raises the exact same error you're getting.
引起的误差和你得到的是一样的。
So, if this column isn't supposed to be unique, you can drop the constraint with
因此，如果这一列不应该是唯一的，那么可以使用
alter table <table_name> drop constraint <constraint_name>;
And, BTW: If you really have a table named SEQUENCE, I strongly recommend renaming it. SEQUENCE is a reserved word, and having a table with this name will cause you a lot of pain in the future.
顺便说一句:如果您确实有一个名为SEQUENCE的表，我强烈建议重新命名它。序列是一个保留的词，拥有一个有这个名字的表将会在将来给你带来很多痛苦。
#2
0
It should not be a surprise if you have unique constraint on the table based on non-uniuqe index - Oracle allows this to support deferrable constraints:
如果基于非uniuqe索引对表有惟一的约束，这并不奇怪——Oracle允许这种约束支持可延迟的约束:
SQL> create table t (x int, y int);
SQL> create index t_x on t(x);
SQL> alter table t add constraint t_x unique (x)
2 /
SQL> select uniqueness from user_indexes where index_name = 'T_X';
UNIQUENES
---------
NONUNIQUE
SQL> insert into t values(1,2);
SQL> insert into t values(1,2);
insert into t values(1,2)
*
error in line:
ORA-00001: unique constraint (SCOTT.T_X) violated
So you should check your table which probably has correspondent constraints.
因此，您应该检查您的表，它可能具有相应的约束。
#1
0
The error message tells you there's a unique CONSTRAINT. You can have a constraint and index with the same name, e.g.
错误消息告诉您有一个惟一的约束。可以有一个同名的约束和索引。
create table t1(pk number);
create index t1_idx on t1(pk);
alter table t1 add constraint t1_idx unique(pk);
insert into t1 values(1);
insert into t1 values(1);
raises the exact same error you're getting.
引起的误差和你得到的是一样的。
So, if this column isn't supposed to be unique, you can drop the constraint with
因此，如果这一列不应该是唯一的，那么可以使用
alter table <table_name> drop constraint <constraint_name>;
And, BTW: If you really have a table named SEQUENCE, I strongly recommend renaming it. SEQUENCE is a reserved word, and having a table with this name will cause you a lot of pain in the future.
顺便说一句:如果您确实有一个名为SEQUENCE的表，我强烈建议重新命名它。序列是一个保留的词，拥有一个有这个名字的表将会在将来给你带来很多痛苦。
#2
0
It should not be a surprise if you have unique constraint on the table based on non-uniuqe index - Oracle allows this to support deferrable constraints:
如果基于非uniuqe索引对表有惟一的约束，这并不奇怪——Oracle允许这种约束支持可延迟的约束:
SQL> create table t (x int, y int);
SQL> create index t_x on t(x);
SQL> alter table t add constraint t_x unique (x)
2 /
SQL> select uniqueness from user_indexes where index_name = 'T_X';
UNIQUENES
---------
NONUNIQUE
SQL> insert into t values(1,2);
SQL> insert into t values(1,2);
insert into t values(1,2)
*
error in line:
ORA-00001: unique constraint (SCOTT.T_X) violated
So you should check your table which probably has correspondent constraints.
因此，您应该检查您的表，它可能具有相应的约束。