Rhino wrote:
----- Original Message -----
From: "Merlin" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Sunday, October 09, 2005 1:31 PM
Subject: deleting rows in 2 tables
Hi there,
I am wondering if there is a possiblity to delete rows in more than one
table with one query.
At the moment I am doing this:
I do have at the moment 3 querys!:
# get table1_id
SELECT table1_id
from ...
WHERE ID = ...
# delete dependent entry
DELETE
FROM $DB.$T5
WHERE
ID = '$data[table1_id]'
LIMIT 1
# delete main entry
DELETE
FROM $DB.$T4
WHERE
ID = '$data[id]'
LIMIT 1
Is there a better solution as this?
Have you considered using foreign keys with DELETE CASCADE rules?
Basically, if you define the dependent tables as children of the first
(parent) table via the appropriate foreign keys and primary keys, and if you
establish DELETE CASCADE on the foreign keys, you could be sure that
whenever you deleted a row in the parent table, all dependent rows in all
dependent tables are deleted automatically. In other words, you set up the
rules in your table definitions and then simply delete what you like from
the parent table; the dependent rows will be deleted for you without you
having to write any explicit DELETE statements for the dependent tables.
For example, if your parent table was EMPLOYEE and your dependent tables
were EMPLOYEE_CARS and EMPLOYEE_CLAIMS, you'd have the definitions set up
roughly like this:
create table EMPLOYEE
empno integer not null,
...
primary key(empno));
create table EMPLOYEE_CARS
empno integer not null,
licence_plate_state char(2) not null,
licence_plate_number char(8) not null,
...
primary key(empno, licence_plate_state, licence_plate_number),
foreign key(empno) references EMPLOYEE on delete cascade));
create table EMPLOYEE_CLAIMS
empno integer not null,
claim_number integer not null,,
...
primary key(empno, claim_number),
foreign key(empno) references EMPLOYEE on delete cascade));
Then, once the tables are defined, all your program needs to do is delete a
given employee and the employee's cars and claims will be deleted
automatically, via the DELETE CASCADE rules in the definitions of the
dependent tables:
delete from EMPLOYEE where empno = 10;
---
If you want to use this approach, I believe you have to be using InnoDB
tables; I don't think the other "engines" support foreign keys.
By the way, this whole concept is called "Referential Integrity" and is very
useful for ensuring that your tables are consistent with one another.
Rhino
Excellent info Rhino. This will help me greatly with my application!
*
Jason
*
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
