Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2002
    Location
    Virginia
    Posts
    13

    Question Unanswered: creating constraints

    I am trying to create constraints that will create my pk @ fk. When I run my code I am retuned the following error.

    "cannot validate (RWJ7P.PK_EMP) - primary key violated"

    this is my code

    "SQL> alter table emp
    2 add constraint pk_emp
    3 primary key (employee_id);"

    I have checked my syntax and I believe its correct, has anyone any suggestions.

    Regards,
    Roger W. Johnson Jr.


  2. #2
    Join Date
    Oct 2002
    Location
    Virginia
    Posts
    13

    Re: creating constraints

    Nevermind, I figured it out. I had previously dropped the table which should have dropped the constraints with it. But not in this case. I again dropped the table and then dropped the constraint and this allowed me to create the PK.

    Roger


    I am trying to create constraints that will create my pk @ fk. When I run my code I am retuned the following error.

    "cannot validate (RWJ7P.PK_EMP) - primary key violated"

    this is my code

    "SQL> alter table emp
    2 add constraint pk_emp
    3 primary key (employee_id);"

    I have checked my syntax and I believe its correct, has anyone any suggestions.

    Regards, [/SIZE][/QUOTE]
    Roger W. Johnson Jr.


  3. #3
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189

    Duplicate entries

    Hello,

    when you create a primary key on a table, Oracle checks if the primary key is unique. So, if you have already records in the table f.e.

    employee_id
    1
    2
    2
    3
    4
    5

    and use the primary key
    alter table emp add constraint pk_emp primary key (employee_id);
    you will get an error cause the key is not unique.

    Hope that helps ?

    Manfred Peter
    (Alligator Company GmbH)
    http://www.alligatorsql.com

  4. #4
    Join Date
    Oct 2002
    Location
    Virginia
    Posts
    13
    Sort of, I thought that if I used a naming convention of adding pk_ would make it unique.
    Roger W. Johnson Jr.


  5. #5
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189
    Hello,

    the name PK_ is only a help for you, that you know, that this is a PK ... you can create Primary Key with the suffix pk_ but they are still primary key. Anyway ... naming a primary key with th suffix PK_ is a good idea.

    So, the best way to create table with primary keys is

    1) create table
    2) create primary key (perhaps in the CREATE TABLE statement)

    and after that

    3) inserting datas.

    Hope that helps ?

    Manfred Peter
    (Alligator Company GmbH)
    http://www.alligatorsql.com

  6. #6
    Join Date
    Oct 2002
    Location
    Virginia
    Posts
    13
    Makes perfect sense, thanks!
    Roger W. Johnson Jr.


Posting Permissions

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