Primary Key - Unique Constraint

[sukimac]

I have a composite primary key on one of my tables. It's made up of three columns - all the cols in the table. When I tried to add data where one of the columns is the same I get a unique key violation eg:

insert into prod_attr(prod_id, attr_cd, attr_val)
Select from prod_table prod_id, 'title', title

1 row inserted

BUT when I try
insert into prod_attr(prod_id, attr_cd, attr_val)
Select from prod_table prod_id, 'artist', artist

it gives me a unique key constraint violation - but if the PK is a composite of the 3 keys then those 2 rows are unique. What am I missing? Thanks, folks.

[uday]

please check to see if data already exists in the table that you are inserting into. In your case as long as the 3 columns make up a unique combination, Oracle will not complain.

Here is what I would do

1. Check primary key / Unique Index to make sure it is what you think it is

2. If the table has existing data, check to see if some one else inserted the same data or if you are trying to run the statement twice.

dba

[sukimac]

there was no existing data in the table, PK was the composite. I dropped the PK while doing the insert, then alter the table by adding the pk constraint - worked OK. Not sure, but maybe didn't like the select ??