List:Internals« Previous MessageNext Message »
From:sasha Date:September 13 2001 9:17pm
Subject:bk commit into 4.0 tree
View as plain text  
Below is the list of changes that have just been committed into a
4.0 repository of sasha. When sasha does a push, they will be propogated to 
the main repository and within 24 hours after the push to the public repository. 
For information on how to access the public repository
see http://www.mysql.com/doc/I/n/Installing_source_tree.html

ChangeSet@stripped, 2001-09-13 15:17:45-06:00, sasha@stripped
  re-wrote section about foreign keys

  vio/test-ssl
    1.2 01/09/13 15:17:44 sasha@stripped +83 -82
    no change

  Docs/manual.texi
    1.544 01/09/13 15:17:43 sasha@stripped +46 -37
    re-wrote secion about foreign keys

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	sasha
# Host:	mysql.sashanet.com
# Root:	/home/sasha/src/bk/mysql-4.0

--- 1.543/Docs/manual.texi	Mon Sep 10 16:40:51 2001
+++ 1.544/Docs/manual.texi	Thu Sep 13 15:17:43 2001
@@ -3706,7 +3706,7 @@
 * Missing Transactions::        Transactions
 * Missing Triggers::            Triggers
 * Missing Foreign Keys::        Foreign Keys
-* Broken Foreign KEY::          Reasons NOT to Use Foreign Keys constraints
+* Broken Foreign KEY::          Why We Did Not Implement Foreign Keys
 * Missing Views::               Views
 * Missing comments::            @samp{--} as the start of a comment
 @end menu
@@ -3989,60 +3989,69 @@
 
 
 @node Broken Foreign KEY, Missing Views, Missing Foreign Keys, Missing functions
-@subsubsection Reasons NOT to Use Foreign Keys constraints
+@subsubsection Why We Did Not Implement Foreign Keys
 
-@cindex foreign keys, reasons not to use
+@cindex foreign keys, why not implemented
 
-There are so many problems with foreign key constraints that we don't
-know where to start:
+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:
 
 @itemize @bullet
 @item
-Foreign key constraints make life very complicated, because the foreign
-key definitions must be stored in a database and implementing them would
-destroy the whole ``nice approach'' of using files that can be moved,
-copied, and removed.
+Assuming proper design of the relations, foreign key constraints will make it
+more difficult for a programmer to introduce an inconsistency into the
+database
 
 @item
-The speed impact is terrible for @code{INSERT} and @code{UPDATE}
-statements, and in this case almost all @code{FOREIGN KEY} constraint
-checks are useless because you usually insert records in the right
-tables in the right order, anyway.
+Using cascading updates and deletes can simplify the client code
 
 @item
-There is also a need to hold locks on many more tables when updating one
-table, because the side effects can cascade through the entire database. It's
-MUCH faster to delete records from one table first and subsequently delete
-them from the other tables.
+Properly designed foreign key rules aid in documenting relations between
+tables
+@end itemize
+
+Disadvantages:
 
+@itemize @bullet
 @item
-You can no longer restore a table by doing a full delete from the table
-and then restoring all records (from a new source or from a backup).
+MySQL does not yet support enforced referential integrity, so if your
+application depends on it, you will not be able to use it with MySQL until
+we implement this feature.
 
 @item
-If you use foreign key constraints you can't dump and restore tables
-unless you do so in a very specific order.
+Mistakes, that are easy to make in designing key relations, can cause severe
+problems, for example, circular rules, or the wrong combination of cascading
+deletes.
 
 @item
-It's very easy to do ``allowed'' circular definitions that make the
-tables impossible to re-create each table with a single create statement,
-even if the definition works and is usable.
+A properly written application will make sure internally that it is not
+violating referential integrity constraints before proceding with a query.
+Thus, additionaly checks on the database level will only slow down performance
+for such application.
 
 @item
-It's very easy to overlook @code{FOREIGN KEY ... ON DELETE} rules when
-one codes an application. It's not unusual that one loses a lot of
-important information just because a wrong or misused @code{ON DELETE} rule.
+It is not uncommon for a DBA to make such a complex topology of relations that
+it becomes very difficult, and in some cases impossible to backup or restore
+individual tables.
 @end itemize
-
-The only nice aspect of @code{FOREIGN KEY} is that it gives ODBC and some
-other client programs the ability to see how a table is connected and to use
-this to show connection diagrams and to help in building applications.
-
-MySQL will soon store @code{FOREIGN KEY} definitions so that a
-client can ask for and receive an answer about how the original
-connection was made. The current @file{.frm} file format does not have
-any place for it.  At a later stage we will implement the foreign key
-constraints for application that can't easily be coded to avoid them.
 
 
 @node Missing Views, Missing comments, Broken Foreign KEY, Missing functions

Binary files /tmp/test-ssl-1.1-3566 and /tmp/test-ssl-1.2-3566 differ
Thread
bk commit into 4.0 treesasha13 Sep