----- Original Message -----
From: "Steve Lefevre" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Monday, April 11, 2005 12:26 AM
Subject: foreign key design stratgies
> Hey folks -
>
> I'm having a little problem understanding ON DELETE foreign key
> constraints. Here's my options from the manual:
>
> [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
>
> When do I use each one? I can find anything in the online manual that
> suggests how to construct a database using this.
>
I'm not surprised; the manual is pretty weak on design issues for the most
part.
I am rushed for time so I am going to be fairly brief and superficial in my
answer but it should be a reasonable start for you. By the way, I am gong to
talk about foreign keys more from the perspective of DB2 than MySQL because
I've used DB2 a lot longer; however, I'm reasonably sure that they work
exactly the same way in MySQL as DB2 (or Oracle, or Microsoft SQL Server, or
...) since they all tend to write towards the same SQL standards.
The delete rule for a foreign key always describes what should happen to the
child/dependent rows if the parent row is deleted. Picture a table named
DEPT containing one row for each department in a company with the primary
key being DEPTNO (department number). Picture another table named EMP
containing one row for each employee in the company with the primary key
being EMPNO (employee number). Each row in the EMP table also contains a
department number (WORKDEPT) that represents the department in which the
employee works. WORKDEPT is then a foreign key pointing to the DEPTNO column
of the DEPT table.
If your delete rule is RESTRICT, you are saying that you cannot delete
department D21 from the DEPT table if anyone in the EMP table has a WORKDEPT
of D21. If you try, the database will give you an error message about an
invalid delete of a foreign key and the delete will fail. Therefore, if you
want to delete department D21, you first have to get rid of all rows with a
WORKDEPT of D21 in the EMP table. You could do that by deleting those
employees first or by assigning them to different departments via UPDATE
statements.
If your delete rule is CASCADE, you are saying that you can delete
department D21 from the DEPT table regardless of how many people in the EMP
table have a WORKDEPT of D21; the delete of the DEPT row for D21 will also
cause all rows of employees with a WORKDEPT of D21 to be deleted as well.
Furthermore, if those employee rows are linked to rows in other tables via
foreign keys, those rows will be deleted as well. This is not limited in
depth so it could cause a *lot* of deletes. Therefore, if the D21 employees
were also present in tables that listed employee health claims and the
company baseball team, they would also be deleted from those tables and any
dependents of THOSE tables as well, ad infinitum.
If your delete rule is SET NULL, you are saying that you can delete
department D21 from the DEPT table regardless of how many people in the EMP
table have a WORKDEPT of D21; in this case, the employees in the EMP table
who have a WORKDEPT of D21 will have their WORKDEPT value changed to null
but the rows will still be in the table. This option does not cause ripples
in the other dependent tables.
The NO ACTION rule is very very similar to the RESTRICT rule in effect.
There is one small difference but I don't recall what it is for DB2. Even
the DB2 manual doesn't make the distinction very clear.
DB2 doesn't have a SET DEFAULT rule. If I had to guess, I'd speculate that
it deletes the desired row from the DEPT table and then changes the values
of the D21 rows in the EMP table to a default department number, which was
presumably defined in the original CREATE TABLE statement. But that is
strictly a guess.
I hope this helps,
Rhino
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.9.6 - Release Date: 11/04/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
