Re: Help with another query!

thanks for your reply,

it is ok to change the table structure, and i would rather get it right now rather than later!

So if i implement you suggestion, the primary key for the Bookings table would be User_ID, Project_ID and Booking_ID or would it be just Booking_ID. As there will only be one booking per day i could use Booking_Date also...

I would very much appreciate your comments on this

"Heinz Huber" <XhhuberX_at_no-racon-linz.at-no> wrote in message news:3e800c16$0$21198$91cee783_at_newsreader01.highway.telekom.at...
> 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 = _at_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 - 16:08:15 CET