Re: Help with another query!

john wrote:
> Hi,
>
> I have the following table structure, as my installation of MySQL doesnt
> support Foreign keys i have to maintain the referential integrity myself. So
> if i delete a user, how can i make sure that the allocations from the
> allocations table where the user_id is the same as the one being deleted and
> that bookings are deleted where the allocations_id is the same as the
> allocations deleted for that user_id?

Two possibilities:

1. Change the table structure and don't generate a surrogate key for every table. Then the primary key for Allocations would be Project_ID, User_ID. As soon as this is your primary key, you also have these two columns in the table WMS_Bookings and the problem of knowing which user a booking belongs to is trivial.
2. If you can't change the table structure any more, use the following SQL (attention MySQL not standard!!): DELETE FROM WMS_Users, WMS_Allocations, WMS_Booking WHERE WMS_Users.User_ID = @user_id AND WMS_Users.User_ID = WMS_Allocations.User_ID AND WMS_Allocations.Allocation_ID = WMS_Booking.Allocations_ID; This should delete the respective rows from all three tables.

By the way, I wouldn't use cascading referential identity without thorough performance tests. AFAIK, most of the cascading implementations are performance killers since they cascade row by row.

hth,
Heinz

>
> Thanks for your help
>
> # ---------- MySQL dump ----------
> #
> # Table structure for table 'WMS_Allocations'
> #
> CREATE TABLE WMS_Allocations (
> Allocation_ID int(11) DEFAULT '' NOT NULL auto_increment,
> Project_ID int(11) DEFAULT '0' NOT NULL ,
> User_ID int(11) DEFAULT '0' NOT NULL ,
> PRIMARY KEY (Allocation_ID),
> KEY Project_ID (Project_ID,User_ID)
> );
>
> #
> # Table structure for table 'WMS_Bookings'
> #
> CREATE TABLE WMS_Bookings (
> Booking_ID int(11) DEFAULT '' NOT NULL auto_increment,
> Booking_Date date DEFAULT '0000-00-00' NOT NULL ,
> PCT_address varchar(255) ,
> PCT_postcode varchar(255) ,
> PCT_telephone varchar(255) ,
> PCT_manager varchar(255) ,
> PCT_gp varchar(255) ,
> P_address varchar(255) ,
> P_postcode varchar(255) ,
> P_telephone varchar(255) ,
> P_manager varchar(255) ,
> P_gp varchar(255) ,
> Allocation_ID int(11) ,
> PRIMARY KEY (Booking_ID),
> KEY Allocation_ID (Allocation_ID)
> );
>
> #
> # Table structure for table 'WMS_Projects'
> #
> CREATE TABLE WMS_Projects (
> Project_ID int(11) DEFAULT '' NOT NULL auto_increment,
> Project_Name varchar(255) ,
> PRIMARY KEY (Project_ID)
> );
>
> #
> # Table structure for table 'WMS_User'
> #
> CREATE TABLE WMS_User (
> User_ID int(11) DEFAULT '' NOT NULL auto_increment,
> User_Username varchar(100) DEFAULT '' NOT NULL ,
> User_Password varchar(100) DEFAULT '' NOT NULL ,
> User_Name varchar(100) DEFAULT '' NOT NULL ,
> User_Type int(11) DEFAULT '0' NOT NULL ,
> User_Email varchar(100) ,
> PRIMARY KEY (User_ID),
> UNIQUE User_Username (User_Username)
> );
>
>
Received on Tue Mar 25 2003 - 01:58:14 CST