By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,360 Members | 3,011 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,360 IT Pros & Developers. It's quick & easy.

Why Should I Create A Foreign Key?

P: n/a
Hi there

I was just wondering why I should explicitly create a foreign key,
especially if i'm not concerned with Cascading/Deleting when a row is
changed.

For example, let's say I have a USERS table, with the column 'USERID' as a
primary key. Other columns would be 'NAME' and 'EMAIL'.

In my users table I have two rows: ["1","Joshua","te**@example.com"] and
["2","Mark","ma**@example.com"].

Now, let's say I have a table that records a user's favorite film
(FAVEFILMS). Columns would be "FAVEID" (auto-increment key), "USERID"
(indexed because we will be looking up by userID) and "FILM".

In this favefilms table, let's put two rows: ["1","2","Jaws"] and
["2","1","Romancing the Stone"].

Obviously there is a relationship between the USERID in the users table and
the USERID in the favefilms table, but what is the purpose of explicitly
defining the USERID in the favefilms table as a foreign key?

If I'm writing a select statement to find Joshua's favorite film, I would
just say "SELECT FILM FROM FAVEFILMS WHERE USERID='1'".

If I've indexed the UserID column as a regular index (as should be done
with any column that will be used in a WHERE clause), does defining it as a
foreign key add anything useful? or does NOT defining it as a foreign key
create any harm?

Just wondering... I mean, hey I have no problem specifying foreign keys, I
was just wondering what the purpose was if I was not interested in
CASCADING or DELETING when changes happen to a table....

Much thanks. I've been using MySQL in web applications for a long time, but
am about to embark on a really big, very important project, so i'm going
back to MySQL 101 to brush up on things I didn't pay much attention to
earlier....

- j

Nov 2 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
The reason is very simple - it avoids that you can delete a row in the
parents table where there are still records in the child table, or that you
can add records to the child table that have no corresponding record in the
parents table.

Of course you could say that with properly written scripts it couldn't
happen anyway, but it gives you an extra plus of security to ensure that you
keep the integrity, no matter what happens ;-).

Markus
Nov 2 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.