Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
What do you mean by define foreign key as part of an application? –  ssmusoke Mar 22 '12 at 10:01
3  
do you mean that there are people who DO NOT IMPLEMENT FOREIGN KEYS at the database level? I cannot believe it ... –  Philippe Grondier Mar 22 '12 at 14:14
1  
If you've ever had to write a database-agnositic app, you would find that implementing foreign keys isn't always even possible. Foreign keys make some things harder - like purging a partition of a table that is the parent of another partitioned table. That's not to say that one shouldn't use foreign keys when one can - but there are valid reasons NOT to use them when the rule of thumb says one should. –  Adam Musch Mar 22 '12 at 14:22
    
What kind of database-agnostic app are you talking about? If it is an enterprise deployment then I'd hope that there was a high level of cooperation between Dev/Ops DBAs and Developers. –  swasheck Mar 22 '12 at 14:37
    
@AdamMusch - You're writing a 'database agnostic' app with enough data volume to worry about table partitions on both sides of a parent-child relationship? –  ConcernedOfTunbridgeWells Mar 22 '12 at 15:02

4 Answers 4

up vote 31 down vote accepted

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.

share|improve this answer
4  
If I could give you a million votes I would. I work with data from any different client databases and see the same thing all the time. Doing this in the application is a prescritpion for bad data. –  HLGEM Mar 22 '12 at 13:47
2  
"little more than a conceit" -- too true! –  ErikE Mar 23 '12 at 5:06

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.

share|improve this answer

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
share|improve this answer
    
ON DELETE CASCADE (in Oracle) is the least possibly efficient way to perform purge operations. Deleting one parent row with 100 children causes 101 delete statements to be issued. –  Adam Musch Mar 22 '12 at 14:24
    
I cannot see efficiency. I see a necessity. Please, correct me if I'm wrong; –  Abdul Ahad Monty Mar 22 '12 at 16:54
    
@Adam: Oracle is a rather crappy SQL Database in many respects. This is apparently one of them. Most other SQL databases will convert a cascading delete into the equivlent of one delete statement per impacted table (assuming no cycles, and that the cascades do not need to delete from the same table twice for different forign key constraints). I just verified that on Microsoft Sql Server. –  Kevin Cathcart Mar 22 '12 at 18:25
2  
@Kevin: Thank you for attempting to turn this into a religious issue with your comments. Your work here is noted. –  Adam Musch Mar 23 '12 at 14:16
1  
SQL Server exclusivly locks and marks the parent row as deleted (LOP_DELETE_ROWS/LCX_MARK_AS_GHOST). At this point any updates/insets that would reference the deleted parent will stall in the FK check stage (and would be rolled back if the delete transaction succeeds). It then does the equivelent of Delete from children where parentid=____. Since no new rows referencing the parent row can be added, it does not need to lock any rows other than those it is deleting. Deleteing them bottom up would be no better, unless you used multiple top level transactions. –  Kevin Cathcart Mar 23 '12 at 18:42

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.

share|improve this answer
1  
"When updating rows in a child table, the parent row also is locked." On what platform is this true? I'm assuming you aren't talking about updating the key itself (and if you are, then you should be using surrogate keys; they are never updated under normal circumstances). –  Nick Chammas Mar 23 '12 at 5:27
2  
@NickChammas: some ORM issue updates on all columns by default, even for columns that are not updated, which causes statements like UPDATE t SET parent_id = :parent_id, col1 = :col1... WHERE id = :id. In this case the parent row will be locked, however the lock is a shared lock, many rows can concurrently update children :) –  Vincent Malgrat Mar 23 '12 at 9:38
1  
Home of the scary DBA: Do foreign key constraints help performance? –  ypercube Mar 23 '12 at 12:00
2  
@AaronBrown: good example to illustrate your point: in some cases (dependent upon DB engine) foreign keys negatively impact concurrency. I was able to test a similar scenario with Oracle 11g (but I suspect the behaviour would be the same for older versions) and the sessions didn't block each other. –  Vincent Malgrat Mar 23 '12 at 13:25
2  
@Vincent - "Some ORM[s] issue updates on all columns by default." That's terrible, especially if the optimizer doesn't know any better than to actually update every column. Aaron, would you call this behavior characteristic of a "well-established, robust ORM"? –  Nick Chammas Mar 23 '12 at 15:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.