Why do we need to use Foreign Keys? [duplicate]

Question

Possible Duplicate:
Should I use foreign keys?

Ok, let's assume we have two tables, users and comments. In comments we have a column comment_made_by_user_id and it means which user typed that particular comment. Why do we need to specify it as a foreign key? If we don't do that, it will still work. We specify primary keys, because it makes queries faster as far as I know (we need to search only for one row while when we don't have a primary key/index, we have to go through all rows). Is it just a good coding practise?

Answer 1

Firstly - foreing key is a constraint. If you have 1-to-many or many-to-many relations in database, foreign keys will be very useful. Find more information here - Referential integrity.

Also they have some good actions - RESTRICT, CASCADE, SET NULL, NO ACTION; read this information in documentation.

So, foreign keys and database itself can do some work for you.

Answer 2

By making it a foreign key you are enforcing the relationship between the tables If you have a foreign key you can't put a user_id that is not in the user table.

Answer 3

When you use foreign keys you get:

1. Data integrity
2. faster queries.

users:
user id:

• 1
• 2
• 3

Comments:
user:

• 1
• 2
• 4 XXX invalid as 4 isn't in the users table.

Read Wikipedia please for more details about Data integrity

Answer 4

It creates data integrity in the database. Consider you remove an users, then you would end up with a lot of comments linked to an invalid user if you forget to remove the comments manually with a separate query. With foreign keys you could set it to remove all the comments automatically as you remove an user (or update changes, like if you would change the user id).