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,

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]

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

Sort of, I thought that if I used a naming convention of adding pk_ would make it unique.

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

Makes perfect sense, thanks!