Many database scholars and programmers feel very strongly that referential integrity should be enforced inside the database server. Indeed, in many cases, this approach is very helpful. However, in talking with many database users we have observed that foreign keys are often misused, which can cause severe problems. Even when used properly, it is not a magic solution for the referential integrity problem, although it does make things easier in some cases.
Because of the above observations, we did not assign implementing foreign keys a high priority. Our user base consisted of mostly of developers who did not mind enforcing referential integerity inside the application code, and in fact, preferred to do it that way because it gave them more control.
However, in the last couple of years, our user base has expanded a great deal and we now have many users who would like to have the enforced referential integrity support inside MySQL. So we will implement the foreign keys in the near future, although at this point we cannot provide a definite delivery date.
Some advantages of foreign key enforcement:
Michael Babcock <email@example.com>: I would be tempted to implement foreign key storage in a seperate file in the directory for the database in question. With current developments in file systems, splitting out the table files into multiple files might even make versioning and other features easier to implement if initally slower on some file systems.
Joseph Powers <firstname.lastname@example.org>: The main reason to have foreign keys is to maintain database relations. With an open database system you don't always have full control over the database. A good example of this is a company that purchases a payroll system from a vendor. Most vendors don't release the source code because they don't want to lose control of the program. If the database used by the payroll system is open (ie: MySQL, MS-SQL server, interBase, ...) the user can still create their own queries and make modifications to the stored data. What happens if the user decides to add the a new table related to the employee table to store additional information? Using foreign keys, the user could link it to the employee table with UPDATE CASCADE and DELETE CASCADE; now when ever the payroll system changes a key field or purges data, the users table will also be updated. Without the source code to the payroll system there is no way for the user to modify the programs to maintain the new table. Another place were foreign keys helpful is during program maintenance. When ever a table is added or removed from the program, all the code that maintains database relations must also be updated (good luck finding and fixing all of them in a large system). In general lack of foreign keys is not a problem for in-house projects because all the source code is accessible. However, if you plan on marketing the product, then you need to expect some users to change the way it functions in their environment. If relations are enforced by the database system then you can reduce the areas in which the user can damage the data. PS: My job is modifying the vendor supplied systems to meet company, state, and other requirements. Thankfully, my vendors supply full source code...
Brian Kerhin <email@example.com>: Joseph Powers thank you for your information, I was about NOT to use foreign keys on my open product, that would have been a booo boo, sad MySQL doesn't support this... Kind of wish (I know it's valuable to have multiple approaches to the same product) that there was one open SQL DB, then all the MySQL effert could be pooled help the open cause more. ... Why isn't this website real time, adding this comment to a MySQL database and dumping it out on request??
Jon Rista <firstname.lastname@example.org>: I'm glad to see your planning to implement foreign keys. They are far more useful than your trying to let on, and it amazes me how you try to downplay their usefulness and power. While it is the job of the database developer to correctly implement foreign keys, the people who have been asking for them, such as myself, DO know how to successfully use and implement them, many on a wide range of database servers (MS SQL Server, IBM DB2, Oracle, to name a few). I have had great success using foreign keys to do all my integrity checking for me in Microsoft SQL Server and IBM DB2 7.1. ADO allows me to check the returned error code and string when an integrity constraint is violated, which allows me to skip writing additional checks in my code, and bypass a performance hinderance. I'm quite sure I can do the same thing with MySQL if foreign keys will be implemented, and that would be a MAJOR plus for me. I develop products that aim to be database independant, and its been a real bummer not beeing able to support MySQL for its lack of standards support, foreign keys and, even more importantly, nested select statements. I really hope you intend to implement foreign key constraints and nested selects. I also hope that sometime in the near future you will implement features like triggers, checks (additional integrity management. Take a look at MS SQL Server documentation), and stored procedures. Such features would save me a tremendous amount of time coding mysql compatability functions.
Hugh Damann <email@example.com>: The lack of a foreign key implementation is one of the reasons I choose PostgreSQL over MySQL except for the most rudimentary databases. And please re-think the apparent attitude of protecting me from my own mistakes. I know how to design a database with foreign keys. If I screw it up, too bad for me.
|© 1995-2001 MySQL AB|