Foriegn Key in tables

[Sonia11]

Hello there,

I am a newbie in MySQL and I am extremely confused about one thing. I am currently creating four tables. There's a primary key I have specified for the first table which is a foriegn key in the second table. Now the problem is that I want the same primary key to be the foriegn key in the third table as well. Whenever I am writing the query, I am getting existing constraint error. So my basic question is, can the same primary key of one table be foriegn key in three table or more? If yes, what would be the difference in the format of writing the query?
Thank you

[smantscheff]

You can use the primary key in one table as a foreign key in as many tables as you like.
You get constraint errors if your data are not consistent - if you insert a foreign key which does not exist as a primary key or if you delete a primary key which is a foreign key in another table.
For further help show your query and the table structure, and the error message, of course.

[Sonia11]

Alright, I didn't delete the pk nor did I insert any fk which doesn'y exist. Here is the query:

CREATE TABLE STUDENT(S_ID VARCHAR(20), S_NAME VARCHAR(35), S_LEVEL VARCHAR(20), MAJOR VARCHAR(30), CONSTRAINT STUDENT_PK PRIMARY KEY (S_ID));

//This contains the primary key which I intend to use here:

CREATE TABLE SERVICES(W_HOUR TIMESTAMP, THEME VARCHAR2(30), S_ID CONSTRAINT FOR_KEY_S_ID REFERENCES STUDENT (S_ID), HOST VARCHAR2(30), LOCATION VARCHAR(35), TYPE VARCHAR(25));

 This query is created successfully but I am having problem creating this one:

 CREATE TABLE BENEFITS(B_CATEGORY VARCHAR(20), AMOUNT NUMBER(8), CONSTRAINT BENEFITS_PK PRIMARY KEY (AMOUNT), S_ID CONSTRAINT FOR_KEY_S_ID REFERENCES STUDENT (S_ID));

 This is showing the following error:
 B_CATEGORY VARCHAR(20), AMOUNT NUMBER(8), CONSTRAINT BENEFITS_PK PRIMARY KEY (AMOUNT), S_ID CONSTRAINT FOR_KEY_S_ID REFERENCES STUDENT (S_ID)
                                                                                                   *ERROR at line 2: ORA-02264: name already used by an existing constraint 

Both the foriegn keys are the same primary key form the same table. What do you think?

[BitBlt]

Constraint names have to be unique in the database. So, instead of using
S_ID CONSTRAINT FOR_KEY_S_ID REFERENCES STUDENT (S_ID)
use something like
S_ID CONSTRAINT FOR_KEY_S_ID_2 REFERENCES STUDENT (S_ID)
and simply increment the naming convention. That way you don't have the naming violation and you can use it as often as you want.

Hope this helps! Good luck!

[Sonia11]

That really helped!! Thanks alot!!

[BitBlt]

Glad to assist. Please mark the thread "solved".