Why Should I Create A Foreign Key?

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
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

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","test@example.com"] and

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

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

- j

Re: Why Should I Create A Foreign Key?

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 ;-).


Site Timeline