CODEWITHSUNDEEP
sqloracle-databaseinsertoracle12c
user3224907
user3224907

Reputation: 768

Oracle inserting into table with composite key

I have a table, TBL_1, with the following fields:

TBL_ID NUMBER (pk),
CREATE_DATE DATE (pk),
TBL_IND VARCHAR2(1)

The primary key is on TBL_ID and CREATE_DATE, I am trying to perform an insert statement but getting an error ORA-00001: unique constraint (primary key) violated.

There is a before insert trigger setting the NEW.CREATE_DATE as SYSDATE. The insert statement looks like:

  INSERT INTO TBL_1 (tbl_id,tbl_ind)
  SELECT tbl_id,'Y' 
    FROM tbl_info;

The actual query is a little more complex but I just wanted to point out it is a INSERT INTO SELECT statement. Is it possible if there is a duplicate tbl_id the trigger used the same exact date for both rows thus causing a duplicate error? How can I avoid this?

Upvotes: 0

Views: 745

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269673

I don't think it is a good idea to have create_date as part of the primary key. I would suggest that you use a sequence value instead.

If you don't mind reducing the number of rows, you can do:

INSERT INTO TBL_1 (tbl_id,tbl_ind)
    SELECT DISTINCT tbl_id, 'Y' 
    FROM tbl_info;

Or, if you still want all rows inserted, then restructure your data to use a sequence instead of the creation date.

Upvotes: 1

Related Questions