2

I've been using table associations with SQL (MySQL) and Rails without a problem, and I've never needed to specify a foreign key constraint.

I just add a table_id column in the belongs_to table, and everything works just fine.

So what am I missing? What's the point of using the foreign key clause in MySQL or other RDBMS?

Thanks.

Nathan
  • 7,627
  • 11
  • 46
  • 80

5 Answers5

2

A foreign key is a referential constraint between two tables

The reason foreign key constraints exist is to guarantee that the referenced rows exist.

The foreign key identifies a column or set of columns in one (referencing or child) table that refers to a column or set of columns in another (referenced or parent) table.

  • you can get nice "on delete cascade" behavior, automatically cleaning up tables

There are lots of reason of using foreign key listed over here: Why Should one use foreign keys

Community
  • 1
  • 1
Vishal Suthar
  • 17,013
  • 3
  • 59
  • 105
  • So I guess a follow up (though pretty much implied in your response) is... is it really worth it? – Nathan Jan 01 '13 at 06:02
1

Rails (ActiveRecord more specifically) auto-guesses the foreign key for you.

... By default this is guessed to be the name of the association with an “_id” suffix.

Foreign keys enforce referential integrity.

Foreign key: A column or set of columns in a table whose values are required to match at least one PrimaryKey values of a row of another table.

See also:

miku
  • 181,842
  • 47
  • 306
  • 310
1

The basic idea of foreign keys, or any referential constraint, is that the database should not allow you to store obviously invalid data. It is a core component of data consistency, one of the ACID rules.

If your data model says that you can have multiple phone numbers associated with an account, you can define the phone table to require a valid account number. It's therefore impossible to store orphaned phone records because you cannot insert a row in the phone table without a valid account number, and you can't delete an account without first deleting the phone numbers. If the field is birthdate, you might enforce a constraint that the date be prior to tomorrow's date. If the field is height, you might enforce that the distance be between 30 and 4000 cm. This means that it is impossible for any application to store invalid data in the database.

"Well, why can'd I just write all that into my application?" you ask. For a single-application database, you can. However, any business with a non-trivial database that stores data used business operations will want to access data directly. They'll want to be able to import data from finance or HR, or export addresses to sales, or create application user accounts by importing them from Active Directory, etc. For a non-trivial application, the user's data is what's important, and that's what they will want to access. At some point, they will want to access their data without your application code getting in the way. This is the real power and strength of an RDMBS, and it's what makes system integration possible.

However, if all your rules are stored in the application, then your users will need to be extremely careful about how they manipulate their database, lest they cause the application to implode. If you specify relational constraints and referential integrity, you require that other applications modify the data in a way that makes sense to any application that's going to use it. The logic is tied to the data (where it belongs) rather than the application.

Note that MySQL is absolute balls with respect to referential integrity. It will tend to silently succeed rather than throw errors, usually by inserting obviously invalid values like a datetime of today when you try to insert a null date into a datetime field with the constraint not null default null. There's a good reason that DBAs say that MySQL is a joke.

Bacon Bits
  • 30,782
  • 5
  • 59
  • 66
0

Foreign keys enforce referential integrity. Foreign key constraint will prevent you or any other user from adding incorrect records by mistake in the table. It makes sure that the Data (ID) being entered in the foreign key does exists in the reference table. If some buggy client code tries to insert incorrect data then in case of foreign key constraint an exception will raise, otherwise if the constraint is absent then your database will end up with inconsistent data.

Habib
  • 219,104
  • 29
  • 407
  • 436
0

Some advantages of using foreign key I can think of:

  • Make data consistent among tables, prevent having bad data( e.g. table A has some records refer to something does not exist in table B)

  • Help to document our database

  • Some framework is based on foreign keys to generate domain model

phnkha
  • 7,782
  • 2
  • 24
  • 31
  • There was mention of a performance hit though.. what about this? – Nathan Jan 01 '13 at 06:09
  • There is a little performance hit on inserts, updates and deletes because the FK has to be checked. But for a long run and a large scale project, I think there's no reason for not using FK – phnkha Jan 01 '13 at 06:12