Where should you define foreign keys?

Question

Is it better to define foreign keys in the database or in the code part of an application?

Answer 1

Put the foreign keys on the database. Even if you validate the data in the application before you save it the FK's are a good piece QA backup. For a first approximation, applications always have data issues. Leaving controls like this out of the system just invites failure modes where data gets corrupted silently.

There's nothing like working in data warehousing for a few years to see this in action. You spend your time picking up the pieces after noddy mistakes by application developers who thought they could enforce data integrity in the application code. Spend any time doing this and you will conclude that application managed data integrity is little more than a conceit.

In addition, the query optimiser can use foreign keys to infer things about table joins, so FK's will result in more efficient query plans.

There are plenty of other benefits to foreign keys as well. Do everyone a favour - put the FK's on the database.

Answer 2

Referential Integrity should be handled on the lowest possible level, which would be the underlying database. Relational Database Management Systems are optimized to handle this. It doesn't make sense to reinvent the proverbial wheel.

It is acceptable to define domain logic in the application code to prevent the DML statement to even cause an RI exception, but this should not be seen as a replacement for foreign key relationships in the database.

Answer 3

I'm going to go out on a limb here fully expecting this to get down-voted since this is a DBA-focused group.

I agree that using strict foreign keys is the best decision in most scenarios. However, there are some cases where foreign keys cause more problems than they solve.

When you are dealing with very highly concurrent environment such as a high traffic web application, and are using a well-established, robust ORM, foreign keys can cause locking problems that make scaling and maintaining a server difficult. When updating rows in a child table, the parent row also is locked. In many scenarios, this can drastically limit concurrency due to locking contention. Additionally, sometimes you have to perform maintenance on individual tables, such as archival processes where you may need to (intentionally) break referential integrity rules, at least temporarily. With foreign keys in place, this can be incredibly difficult and in some RDBMSes disabling foreign key constraints will cause a rebuild of the table, a time consuming process that can require substantial downtime.

Understand that I'm including the caveat that you must use a robust framework that is capable of understanding referential integrity external to the database. Still, you will likely end up with some referential integrity issues. However, there are many cases where it just isn't that big a deal to have orphaned rows or minor referential integrity violations. I would argue that the majority of web applications fall under this category.

That being said, no one starts out as Facebook. Start by defining foreign keys in your database. Monitor. If you end up having problems, understand that you may need to drop some of those constraints to scale.

In conclusion: Most databases should have foreign keys. Highly concurrent environments might be better off without foreign keys. If you reach that point, you might need to consider dropping those constraints.

I'm going to go don my flame-retardant suit now.

EDIT 2012-03-23 7:00AM

In thinking about the locking consequences of foreign keys, I neglected to mention the cost of all the additional row lookups that are implicitly generated internally, adding to server load.

Ultimately, my point is that foreign keys are not free. In many cases, the cost is worth it, but there are scenarios where that cost exceeds their benefit.

EDIT 2012-03-23 7:38AM

Let's be concrete. I'm choosing MySQL/InnoDB in this example, which is not highly respected for its foreign key behavior, but it's what I am most familiar with and is likely the most commonly used web database. I'm not certain other database would fare better with the example I'm about to show.

Consider a child table with a foreign key referencing the parent. As an example, see the film and film_actor tables in the sakila sample database in MySQL:

CREATE TABLE `film` (
  `film_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `description` text,
  `release_year` year(4) DEFAULT NULL,
  `language_id` tinyint(3) unsigned NOT NULL,
  `original_language_id` tinyint(3) unsigned DEFAULT NULL,
  `rental_duration` tinyint(3) unsigned NOT NULL DEFAULT '3',
  `rental_rate` decimal(4,2) NOT NULL DEFAULT '4.99',
  `length` smallint(5) unsigned DEFAULT NULL,
  `replacement_cost` decimal(5,2) NOT NULL DEFAULT '19.99',
  `rating` enum('G','PG','PG-13','R','NC-17') DEFAULT 'G',
  `special_features` set('Trailers','Commentaries','Deleted Scenes','Behind the Scenes') DEFAULT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`film_id`),
  KEY `idx_title` (`title`),
  KEY `idx_fk_language_id` (`language_id`),
  KEY `idx_fk_original_language_id` (`original_language_id`),
  CONSTRAINT `fk_film_language` FOREIGN KEY (`language_id`) REFERENCES `language` (`language_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_film_language_original` FOREIGN KEY (`original_language_id`) REFERENCES `language` (`language_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8

CREATE TABLE `film_actor` (
  `actor_id` smallint(5) unsigned NOT NULL,
  `film_id` smallint(5) unsigned NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`,`film_id`),
  KEY `idx_fk_film_id` (`film_id`),
  CONSTRAINT `fk_film_actor_actor` FOREIGN KEY (`actor_id`) REFERENCES `actor` (`actor_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_film_actor_film` FOREIGN KEY (`film_id`) REFERENCES `film` (`film_id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

The relevant constraint is film_actor (fk_film_actor_film) for my example.

session1> BEGIN;
session1> INSERT INTO film_actor (actor_id, film_id) VALUES (156, 508);
Query OK, 1 row affected (0.00 sec)

session2> BEGIN;
session2> UPDATE film SET release_year = 2005 WHERE film_id = 508;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Note that I was unable to update an unrelated field in the parent row while inserting into the child table. This happens because InnoDB is holding a shared lock on the row where film.film_id = 508 due to the FK constraint on film_actor, thus the UPDATE to that row cannot get the exclusive lock required. If you reverse that operation and run the UPDATE first, you have the same behavior, but the INSERT is blocked.

session1> BEGIN;
session1> UPDATE film SET release_year = 2005 WHERE film_id = 508;
Query OK, 1 row affected (0.00 sec)

session2> BEGIN;
session2> INSERT INTO film_actor (actor_id, film_id) VALUES (156, 508);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Consider a users table in a web application where there are often dozens of related tables. Essentially any operation on any related row prevents an update to the parent row. That can be a challenging problem when you have multiple foreign key relationships and a lot of concurrency.

FK constraints can make workarounds for table maintenance challenging as well. Peter Zaitsev from Percona has a blog post about this that explains it better than I can: Hijacking Innodb Foreign Keys.

Answer 4

It is good practice to use foreign key in the database. It helps-

• to keep data integrity by removing the possibility of unwanted data
• to increase performance. In systems which auto index fields, foreign key references can give a performance boost
• to write less code by the programmer. like, using ON DELETE CASCADE