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
