duplicate unique primary key error

Database
Fan

duplicate unique primary key error

Hi,

I had to modify a column's width so i created a temporary table, with modified column width, and populated that temporary table through (insert into temp select * from original_table).
Later I dropped the original_table and renamed the temporary table to the original one.

The original_table had id as the unique primary index and was defined as "Id INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY".

Now I am not able to insert data in this table manually through insert query. It gives me error as "duplicate unique primary key error".

Any solutions to rectify this situations. Please Help!!

Thanks & Regards,

2 REPLIES
Teradata Employee

Re: duplicate unique primary key error

GENERATED BY DEFAULT columns are not guaranteed to be unique anyway, but if you're sure that's what you want: create another table but specify that generated values START WITH a value beyond anything already present in the table; INSERT/SELECT the data, drop old, rename again.

Since you have to copy the data to a new table anyway, consider whether GENERATED ALWAYS NOCYCLE would be better for your application. (Of course, then you wouldn't use "SELECT *" because you're not allowed to specify a value to insert into that column).
Enthusiast

Re: duplicate unique primary key error

Sharad,

UPK error occurs only when the UPI column that you are trying to insert is alraedy present there in your table.
Check if you are passing identity column value also , if yes then don't pass it . It will be taken automatically.If this is not your problem then look at this scenerio:

1. CREATE SET TABLE temp_tables.XYZ
(
id INTEGER GENERATED BY DEFAULT as IDENTITY,
ch CHAR(1) )
UNIQUE PRIMARY INDEX ( id );

2.
insert into temp_tables.XYZ values(,'a');
insert into temp_tables.XYZ values(,'b');

3.

insert into temp_tables.XYZ values(4,'c'); //No problem here since UPI(1,2,4) is still unique

4.

insert into temp_tables.XYZ values(,'c'); //No problem here since UPI(1,2,3,4) is still unique

5.

insert into temp_tables.XYZ values(,'c');

//Code = 2801: Duplicate unique prime key error in TEMP_TABLES.XYZ.

5. Now again execute the same statement

insert into temp_tables.XYZ values(,'c');

// Completed. 1 rows processed //UPI now (1,2,3,4,5)

Strange !
Now lets come to your case, the initial inserts that you have done are mass inserts, that means each AMP has reserved its own starting value of identity column . Now when you try to insert your row the next Identity value is already there on one of the AMP.

Let me know if you find some other reason of getting that error message.

Regards,

DEEPU