Do you have a question? Post it now! No Registration Necessary. Now with pictures!
- Posted on
- Why Should I Create A Foreign Key?
- Good Man
November 2, 2005, 7:45 pm
rate this thread
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","firstname.lastname@example.org"] 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
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
Re: Why Should I Create A Foreign Key?
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
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 ;-).
- » Americanas.com SALDO de TVs Com at 80% de Desconto (29798)
- — Newest thread in » MySQL Database Forum