Page 1 of 1

foreign keys

Posted: Thu Jul 13, 2006 10:44 am
by Luke
In a one-to-many relationship where I am working with the many side... for example users & articles... do I need to do anything special to the foreign key other than name it something like user_id in the articles table?

Posted: Fri Jul 14, 2006 3:11 am
by GM
No. You can call it what you like, but I personally find it easier to name it the same as in the other table.

This is why I avoid field names like "ID", because when you come to use this as a foreign key, you will probably already have a field called "ID" in the table.

I personally do this:

Code: Select all

CREATE TABLE user (
id_user INT(8) unsigned not null auto_increment,
de_user VARCHAR(8) not null,
...
...
PRIMARY KEY (id_user));

CREATE TABLE post (
id_post INT(8) unsigned not null auto_increment,
id_user INT(8) unsigned not null,
...
PRIMARY KEY (id_post));
Here the id_user is a foreign key to the post table, but I've kept the field name the same. Usually the fact that it begins with "id_" is enough to tell me a) that it is a foreign key and b) which table it is a key to.