Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2012
    Posts
    9

    Unanswered: Composite Primary Key question

    I have a new question regarding composite primary keys on another table I have created.

    I have the following table with the following definition:

    CREATE TABLE "APSOM"."CPULIST"
    ( "CPU_ID" VARCHAR2(10 BYTE),
    "SERVER_ID" VARCHAR2(10 BYTE),
    "CREATED_DATETIME" TIMESTAMP (6),
    "UPDATED_DATETIME" TIMESTAMP (6)
    )

    with the following composite PK definition:

    ALTER TABLE "APSOM"."CPULIST" ADD CONSTRAINT "CPULIST_PK" PRIMARY KEY ("CPU_ID", "SERVER_ID")

    Then, I inserted data in the following way:

    insert into CPULIST
    values ('CPU1', 'P1', SYSDATE, SYSDATE);

    with following CPU ID values from 'CPU1' to 'CPU16' for SERVER ID value 'P1' also inserted as well.

    Now, I am trying to insert values for SERVER ID value 'P2'

    insert into CPULIST
    values ('CPU1', 'P2', SYSDATE, SYSDATE);

    and I am getting the following error message:

    Error starting at line 1 in command:
    insert into CPULIST
    values ('CPU1', 'P2', SYSDATE, SYSDATE)
    Error report:
    SQL Error: ORA-00001: unique constraint (APSOM.XPKCPULIST) violated
    00001. 00000 - "unique constraint (%s.%s) violated"
    *Cause: An UPDATE or INSERT statement attempted to insert a duplicate key.
    For Trusted Oracle configured in DBMS MAC mode, you may see
    this message if a duplicate entry exists at a different level.
    *Action: Either remove the unique restriction or do not insert the key.


    This error does not make sense to me. Any help would be appreciated.

    Thanks,

    Patrick Quinn
    Operations
    Turning Point Global Solutions

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    select column_name from all_cons_columns
    where constraint_name = 'XPKCPULIST'
    and owner = 'APSOM';

    post results from SQL above
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Sep 2012
    Posts
    9
    Using the following SQL command:

    select column_name from all_cons_columns
    where constraint_name = 'CPULIST_PK'
    and owner = 'APSOM';

    I get the following records

    1. CPU_ID
    2. SERVER_ID

    Patrick Quinn
    Operations
    Turning Point Global Solutions

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    As I said in another topic you opened (here) (but you might not have seen it yet): copy/paste the whole SQL*Plus session so that we could see what you exactly did and how Oracle responded.

  5. #5
    Join Date
    Sep 2012
    Posts
    9
    I am not using SQL Plus, I am using Oracle SQL Developer. As for the output, I have already provided all of the output generated by the last insert statement.

    Patrick Quinn
    Operations
    Turning Point Global Solutions

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    OK then, solution is simple.
    Quote Originally Posted by You
    Either remove the unique restriction or do not insert the key.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    SQL Error: ORA-00001: unique constraint (APSOM.XPKCPULIST) violated
    I couldn't see "APSOM.XPKCPULIST" in your posts.
    You might not show all of your trials or all related DDLs.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •