Cascade on delete????

[nealios]

Hello,

 

I have two tables in a database. One which stores customers and one which stores jobs. Each job has a customer allocated.

 

When i delete a customer from the customer  table i want it to delete all the corrosponding jobs from the Job table. Ive looked into delete on cascades as i think thats what needs to be done.

 

The problem is im having trouble with the syntax and im getting an error when trying to implement it. Im not sure where to put the delete on cascade. I gather i can alter my existing tables and add delete on cascade under the foreign key reference.

 

 

Can anyone help?

 

 

My tables are below

 

 

Many thanks

 

CREATE TABLE `Job` (
  `JobID` int(20) NOT NULL auto_increment,
  `StartDate` date default NULL,
  `EndDate` date default NULL,
  `Price` decimal(7,2) default NULL,
  `JobAddress1` varchar(25) default NULL,
  `JobAddress2` varchar(25) default NULL,
  `JobTown` varchar(25) NOT NULL,
  `JobCounty` varchar(25) NOT NULL,
  `JobPostcode` varchar( default NULL,
  `Description` varchar(500) default NULL,
  `Materials` decimal(7,2) default NULL,
  `cid` int(20) NOT NULL,
  `InvoiceDate` date default NULL,
  `EngineerID` int( default NULL,
  `LabourID` int(2) default NULL,
  `PaidID` int(2) default NULL,
  `cpstart` date default NULL,
  `cpend` date default NULL,
  `serialno` varchar(10) default NULL,
  PRIMARY KEY  (`JobID`),
  KEY `cid` (`cid`),
  KEY `EngineerID` (`EngineerID`),
  KEY `LabourID` (`LabourID`),
  KEY `PaidID` (`PaidID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=42 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `customer`
-- 

CREATE TABLE `customer` (
  `cid` int(20) NOT NULL auto_increment,
  `title` varchar(5) default NULL,
  `first_name` varchar(20) default NULL,
  `surname` varchar(20) default NULL,
  `address1` varchar(25) default NULL,
  `address2` varchar(25) default NULL,
  `town` varchar(25) default NULL,
  `county` varchar(25) default NULL,
  `postcode` varchar( default NULL,
  `telephone` varchar(15) default NULL,
  `mobile` varchar(15) default NULL,
  `email` varchar(35) default NULL,
  `additional_info` varchar(500) default NULL,
  PRIMARY KEY  (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=30 ;

-- 
-- Constraints for dumped tables
-- 

-- 
-- Constraints for table `Job`
-- 
ALTER TABLE `Job`
  ADD CONSTRAINT `Job_ibfk_1` FOREIGN KEY (`EngineerID`) REFERENCES `Engineer` (`EngineerID`),
  ADD CONSTRAINT `Job_ibfk_2` FOREIGN KEY (`LabourID`) REFERENCES `LabourType` (`LabourID`),
  ADD CONSTRAINT `Job_ibfk_3` FOREIGN KEY (`PaidID`) REFERENCES `Paid` (`PaidID`); 

[fenway]

Did you take a look here?

[nealios]

Hi Fenway,

 

I looked at a similar link, entering the sql into phpmyadmin wasnt working for some reason. Managed to get it working though by doing it directly in phpmyadmin, when i posted the question i wasnt aware that this was possible!

[aschk]

From my knowledge you need to just add a bit to the end of the below statement

ALTER TABLE `Job` ADD CONSTRAINT `Job_ibfk_1` FOREIGN KEY (`EngineerID`) REFERENCES `Engineer` (`EngineerID`) ON DELETE CASCADE

 

Check the syntax docs though. I'm never 100% right