- 1. Insert into table returns error ORA-0001 unique constraint violated
- 2. IMP returns error that a record cannot be loaded because it already exists
- 3. An 'analyze table xxx validate structure cascade' returns an 'ORA-1499 table/index cross reference failure'
- 4. A 'create index' or enabling a primary key constraint fails with 'ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found' or 'ORA-02299: cannot add or enable constraint failed - duplicate keys found'
1st. and 2nd. case:
An attempt was made to insert/import a record with primary key values that are already used by an existing record.
3rd. case:
proceed as described in note 765613
4th. case:
two records exist with the same primary key values and therefore it's not possible to create the unique index/enable the primary key constraint.
The 4th. case apply to other unique indexes accordingly. When
I talk of the primary key or columns of the primary key it may be
substituted with the columns of other unique indexes, if such indexes
exist for a table.
For the 1st. and 2nd. case one has to find out whether the 'old' record shall be kept or not. Either delete the old record (or delete all old records in the table) and insert/import the new data or keep the old.
The main objective of this note is to describe how to identify the duplicate rows in the 4th. case and how to select and delete them to clear the inconsistency.
In the following we will use as an example a table named T_TAB with primary key columns K_COL1, K_COL2 (column name) and data columns D_COL1, D_COL2.
The table name and column names appear in upper case in the following commands.
They have to be replaced with the actual table name and column names in your particular case. The number of primary key columns may vary.
STEPS TO RESOLVE THE PROBLEM
****************************
- a) IDENTIFY THE COLUMNS BEING PART OF THE PRIMARY KEY
==================================================
In the 4th. case the 'create index' command or the constraint definition will provide the information.
Another way to find the primary key columns is to use the R/3 data dictionary information system - the columns marked as key columns in the display of columns of a table are the ones to use.
- b) IDENTIFY THE DUPLICATE RECORDS
==============================
- Login to the ORA
Unix account
- start SQLPLUS, connect to the database as SAPR3/
- Execute the following query. It will display all rows whith duplicate keys and their rowid (needed to delete later single rows).
abstract:
select alldata.rowid, alldata.*
fromalldata,
(SELECT, ...,
FROM
GROUP BY, ...,
HAVING COUNT(*) > 1) keydata
where alldata.=keydata.
and ...
and alldata.=keydata. ;
our example:
select alldata.rowid, alldata.*
from T_TAB alldata,
(SELECT K_COL1, K_COL2
FROM T_TAB GROUP BY K_COL1, K_COL1
HAVING COUNT(*) > 1) keydata
where alldata.K_COL1=keydata.K_COL1
and alldata.K_COL2=keydata.K_COL2;
The rowid is the database internal unique ID of each row.
Make sure to always compare all primary key columns in the where-clause!
Execution of this select may take a long time for large tables because it is processed as a nested loop join, that means if the table contains let's say 1000 records the whole table will be read 1001 times.
It may be a good idea to create a temporary nonunique index on the key columns before executing the select statement.
The statement to create the nonunique index in our example (logged on as user sapr3):
sqlplus> create index T_TAB_I on T_TAB (column K_COL1, K_COL2)
tablespace PSAPxxxx;
The tablespace is the index-tablespace of the tablespace where the table is stored, for example PSAPPOOLI. After you have executed the select statement you should remove this temporary index again by dropping it. You must connect as user SYSTEM to the database:
sqlplus>drop index sapr3.T_TAB_I;- c) SELECTION
=========Once the records have been identified, one record of each set with duplicate key values has to be selected to be kept, the other one/s is/are to be deleted.
If the table consists of the primary key columns only, just pick one record to be kept, delete the other/s.
You can check of what columns a table exists by entering:
sqlplus>describe sapr3.T_TAB;
If the table has other data columns, select the records with all columns and try to decide, based on the data values, which one to keep.
This will usually require assistance from SAP support or development
It is necessary to select the records to delete with due care.
Otherwise data consistency may be at risk.
If the table contains a column of data type long or long raw, which means it's not possible to select the column in SQLPLUS, update one of the primary key columns with a value not used so far.
E. g. in SQLPLUS:
update T_TAB set K_COL2 = zzz
where rowid = 'xxxxxxxx.xxxx.xxxx'
then use a R/3 display function (transaction se16) to view the data of the records.
(Note: If it is decided later that this records will be kept, the changed key column has to be set back to it's original value using an update command similar to the above)
zzz has to be a data value that is not used yet, to be able to select it in the R/3 display function with a unique value.
zzz has to be enclosed in single quotes if the column K_COL2 is of datatype char or varchar2, it need not to be enclosed if it's a number column.- d) HOW TO DELETE THE RECORDS
=========================Once it has been determined which one of the records to keep and which
to delete the 'extra' records.
one/s to delete,
use, in SQLDBA or SQL*Plus, connected as database user SYSTEM,
the command
delete from sapr3.T_TAB where rowid = 'xxxxxxxx.xxxx. xxxx'
Once that has been done, recreate the primary key index.
Subscribe to: Post Comments (Atom)
No comments:
Post a Comment