Help with another query!

From: john <nospam_at_nospam.com>
Date: Mon, 24 Mar 2003 13:23:14 -0000
Message-ID: <NHDfa.1399$YD5.164179_at_wards.force9.net>

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?

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 Mon Mar 24 2003 - 14:23:14 CET