What's wrong with foreign keys? [closed]

Like many things, it's a tradeoff. It's a question of where you want to do the work to verify the data integrity:

(1) use a foreign key (a single point to configure for a table, feature is already implemented, tested, proven to work)

(2) leave it to the users of the database (possible multiple users/apps updating the same table (s) meaning more potential points of failure and increased complexity in testing).

It's more efficient for the database to do (2), easier to maintain and less risk with (1).

One good principle of data structure design is to ensure that every attribute of a table or object be subject to a well-understood constraint. This is important because if you or your program can count on valid data in the database, you are less likely to have program defects caused by bad data. You also spend less time writing code to handle error conditions, and you are more likely to write error-handling code up front.

In many cases these constraints can be defined at compile-time, in which case you can write a filter to ensure that the attribute always falls within range, or the attempt to save the attribute fails.

However, in many cases these constraints can change at run-time. For example, you may have a "cars" table that has "colour" as an attribute which initially takes on the values, say, of "red", "green" and "blue". It is possible during the execution of the program to add valid colours to that initial list, and new "cars" added may take on any colour in the up-to-date list of colours. Furthermore, you usually want this updated list of colours to survive a program restart.

To answer your question, it turns out that if you have a requirement for data constraint that can change at run-time, and those changes must survive a program restart, foreign keys are the simplest and most concise solution to the problem. The development cost is the addition of one table (e.g. "colours", a foreign key constraint to the "cars" table, and an index), and the run-time cost is the extra table lookup for the up-to-date colours to validate the data, and this run-time cost is usually mitigated by indexing and caching.

If you don't use foreign keys for these requirements, you must write software to manage the list, look valid entries, save it to disk, structure the data efficiently if the list is large, ensure that any updates to the list don't corrupt the list file, provide serial access to the list in case there are multiple readers and/or writers, and so on. i.e. You need to implement a lot of RDBMS functionality.

In a project I worked on there was often implicit rather than explicit relationships so that numerous tables could be joined on the same column.

Take the following table

Address

• AddressId (PK)
• EntityId
• EntityType
• City
• State
• Country
• Etc..

Possible values of EntityType may be Employee, Company, Customer, and the EntityId refers to the primarky key of whichever table you were interested in.

I don't really think this is the best way to do things, but it worked for this project.

In DB2, if MQTs (Materialized Query Tables) are used, foreign key constraints are required for the optimizer to choose the right plan for any given query. Since they contain the cardinality information, the optimizer uses the metadata heavily to use a MQT or not.

Quite often we receive the errors with FK constraints Cannot add or update a child row: a foreign key constraint fails Suppose there are two tables inventory_source and contract_lines, and we are referring inventory_source_id in contract_lines from inventory_source and suppose we want to delete record from inventory_source and the record is already present in contract_lines or we want to delete the PK column from Base table, we get errors for FK constraints, we can avoid it using the steps jotted below.

CREATE TABLE inventory_source ( inventory_source_id int(11) NOT NULL AUTO_INCREMENT, display_name varchar(40) NOT NULL, state_id int(11) NOT NULL, PRIMARY KEY (inventory_source_id), KEY state_id (state_id), CONSTRAINT ba_inventory_source_state_fk FOREIGN KEY (state_id) REFERENCES ba_state (state_id) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8; CREATE TABLE contract_lines( contract_line_id int(11) NOT NULL AUTO_INCREMENT, inventory_source_id int(11) NULL , PRIMARY KEY (contract_line_id), UNIQUE KEY contract_line_id (contract_line_id), KEY AI_contract_line_id (contract_line_id), KEY contract_lines_inventory_source_fk (inventory_source_id), CONSTRAINT contract_lines_inventory_source_fk FOREIGN KEY (inventory_source_id) REFERENCES ba_inventory_source (inventory_source_id) ) ENGINE=InnoDB AUTO_INCREMENT=135 DEFAULT CHARSET=utf8 ; 

We can overcome it using the following steps:-

1. Delete or update the row from the inventory_source will automatically delete or update the matching rows in the contract_lines table and this is known as cascade delete or update.
2. Another way of doing it is setting the column i.e inventory_source_id in the contract_lines table to NULL, when a record corresponding to it is deleted in the inventory_source table.
3. We can restrict the parent table for delete or update in other words one can reject the delete or update operation for the inventory_source table.
4. Attempt to delete or update a primary key value will not be permitted to proceed if there is a related foreign key value in the referenced table.

Wowowo...
Answers everywhere. Actually this is the most complicated topic I have ever encountered. I use FKs when they are needed but on production environment I rarely use them.

Here is my whys I rarely use the Fks:

1. Most of the time I am dealing with huge data on small server to improve performance I need to remove the FKs. Because when you have FKs and you do Create, Update or Delete the RDBMS first check if there no constraint violation and if you have huge DB that could be something fatal

2. Sometimes I need to import data from others places and because I am not too sure of how well structured they are, I simply drop the FKs.

3. In case you are dealing with multiple DBs and having reference key in an other DB will not go well(as for now) until you remove the FKs (cross database relations)
4. They was also a case when you write an application which will seat on whatever RDBMS or you want your DB to be exported and imported in any RDBMS system in this case each specific RDBMS system has his own way of dealing with FKs and you will probably be obliged to drop the use of FKs.

5. If you user RDBMS platform (ORMs) you know that some of them offer their own mapping depending on the solution and technicality their offer and you don't care about creating the tables and their FKs.

6. Before the last point will be knowledge to deal with DB that has FKs and the knowledge to write an application that does all the Job without the need of FK 7. Lastly as I started saying it all depend on your scenario, in case knowledge is not a barrier. You will always want to run the best of the best you can get!

Thank you everybody!

I always thought it was lazy not to use them. I was taught it should always be done. But then, I didnt listen to Joel's discussion. He may have had a good reason, I don't know.