If I understand your question correctly - and I'm not sure I do - Table 2 is
the parent and Table 1 is the child. In other words, there is a one-to-many
relationship between the parent, Table 2, and the child, Table 1.
Therefore, if the parent table, Table 2, contains one row for each
department of a company and the child table, Table 1, contains one row for
each employee, we would have a situation like this:
Table 2 - DEPARTMENT
===============
Deptno Deptname
A00 Administration
B01 Sales
C01 Manufacturing
Table 1 - EMPLOYEE
=============
Empno Deptno Name
100 A00 Smith
101 B01 Brown
102 C01 Wong
103 A00 Green
104 A00 White
105 C01 Ferguson
If I'm following you so far, you're asking what happens in the DEPARTMENT
table if you delete an employee from the EMPLOYEE table? For example, if you
delete employee 105 or even employee 101 from the EMPLOYEE table, will there
be any impact on the DEPARTMENT table?
The answer is _NO_. The DELETE rule between a parent table and its child
table controls what happens in the _child_ table if a row is deleted from
the _parent_ table; deleting a row in the child table has no effect on the
parent table.
Therefore, if you delete employee 105 from the EMPLOYEE table, the only
effect is that employee 105 is removed from the EMPLOYEE table; there is no
effect of any kind on the DEPARTMENT table. Ditto for employee 101 who is
the only employee in department B01: only the employee 101 row is removed
and there is no impact on the DEPARTMENT table at all.
If you want to see the DELETE rule between the tables take effect, you need
to delete a row from the _DEPARTMENT_ table. If the DELETE rule in effect is
CASCADE, as you have proposed, then deleting the B01 row from DEPARTMENT
will also cause the deletion of all B01 rows in the EMPLOYEE table. By the
same token, deleting the C01 row from the DEPARTMENT table will cause the
deletes of _all_ of the C01 rows in the EMPLOYEE table.
--
Rhino
----- Original Message -----
From: "James Sherwood" <[EMAIL PROTECTED]>
To: "mysqllist" <mysql@lists.mysql.com>
Sent: Monday, July 17, 2006 8:44 AM
Subject: ON DELETE CASCADE question
Hello.
I have a question about on delete cascade.
If i have 2 tables such as this:
Table1 Table2
Prikey Prikey
Table2foreinkey name
name description
description
Now if I delete a row from table1 that has a foreign key from table 2 should
it delete the row in table 2?
Thanks,
James
--------------------------------------------------------------------------------
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.9.10/385 - Release Date: 2006-07-11
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.9.10/385 - Release Date: 2006-07-11
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
