Delete Statement Problem

[codeblock]

Hi all, i have a problem in writing a block of SQL code in mysql. The block of code will have too delete a single row/record from about 23 related tables all at once.

The code that i have written so far will only delete 1 row/record in 10 related tables but no more. Will not mysql allow this? Can anyone help me?

Thanks to anyone that can help me out.

[smantscheff]

Show your CREATE TABLE statements and your code block.

[codeblock]

Show your CREATE TABLE statements and your code block.

Hello smantscheff, heres the create table staements and code block.

create database auction;
use auction;

  CREATE TABLE IF NOT EXISTS `bids` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `item_id` int(11) NOT NULL,
  `amount` float NOT NULL,
  `user_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (item_id) REFERENCES items(id)
  ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `basketballcourt` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `item_id` int(11) NOT NULL,
  `basketballcourt` varchar(9) NOT NULL,
  `bbcrt` varchar(30) NOT NULL,
  `bbLength` decimal(9,2) NOT NULL,
  `bbWidth` decimal(9,2) NOT NULL,
  `bbHeight` decimal(9,2) NOT NULL,
  `nobb` int(11) NOT NULL,
  `bbAreaSize` decimal(9,2) NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (item_id) REFERENCES items(id)
  ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `basement` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `item_id` int(11) NOT NULL,
  `basement` varchar(9) NOT NULL,
  `bLength` decimal(9,2) NOT NULL,
  `bWidth` decimal(9,2) NOT NULL,
  `bHeight` decimal(9,2) NOT NULL,
  `nob` int(11) NOT NULL,
  `bAreaSize` decimal(9,2) NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (item_id) REFERENCES items(id)
  ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `attic` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `item_id` int(11) NOT NULL,
  `attic` varchar(9) NOT NULL,
  `aLength` decimal(9,2) NOT NULL,
  `aWidth` decimal(9,2) NOT NULL,
  `aHeight` decimal(9,2) NOT NULL,
  `aAreaSize` decimal(9,2) NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (item_id) REFERENCES items(id)
  ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `additionalpropertyinformation` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `item_id` int(11) NOT NULL,
  `internal` mediumtext NOT NULL,
  `external` mediumtext NOT NULL,
  `surroundinggrounds` mediumtext NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (item_id) REFERENCES items(id)
  ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `additionalprojectinfo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `item_id` int(11) NOT NULL,
  `apinfo` varchar(30) NOT NULL,
  `addprojectinfo` mediumtext NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (item_id) REFERENCES items(id)
  ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `additionalfeatures` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `item_id` int(11) NOT NULL,
  `airconditioning` varchar(9) NOT NULL,
  `underfloorheating` varchar(9) NOT NULL,
  `altenergy` varchar(9) NOT NULL,
  `altdescription` mediumtext NOT NULL,
  `iss` varchar(9) NOT NULL,
  `issdescription` mediumtext NOT NULL,
  PRIMARY KEY (`id`),
  ADD CONSTRAINTS fk_additionalfeatures_items FOREIGN KEY (item_id) 
  REFERENCES items(id)
  ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


CREATE TABLE IF NOT EXISTS `additionalbathroom` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `item_id` int(11) NOT NULL,
  `additionalbathroom` varchar(9) NOT NULL,
  `addbrType` varchar(16) NOT NULL,
  `addbrLength` decimal(9,2) NOT NULL,
  `addbrWidth` decimal(9,2) NOT NULL,
  `addbrHeight` decimal(9,2) NOT NULL,
  `noaddbr` int(11) NOT NULL,
  `addbrAreaSize` decimal(9,2) NOT NULL,
  PRIMARY KEY (`id`),
  ADD CONSTRAINTS fk_additionalbathroom_items FOREIGN KEY (item_id) 
  REFERENCES items(id)
  ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `additionalbedroom` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `item_id` int(11) NOT NULL,
  `additionalbedroom` varchar(9) NOT NULL,
  `addbType` varchar(15) NOT NULL,
  `addbLength` decimal(9,2) NOT NULL,
  `addbWidth` decimal(9,2) NOT NULL,
  `addbbHeight` decimal(9,2) NOT NULL,
  `noaddb` int(11) NOT NULL,
  `addbAreaSize` decimal(9,2) NOT NULL,
  PRIMARY KEY (`id`),  
  ADD CONSTRAINTS fk_additionalbedroom_items FOREIGN KEY (item_id) 
  REFERENCES items(id)
  ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `images` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `item_id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (item_id) REFERENCES items(id)
  ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


CREATE TABLE IF NOT EXISTS `items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `propertytype` varchar(30) NOT NULL,
  `age` int(4) NOT NULL,
  `grade` varchar(30) NOT NULL,
  `style` varchar(30) NOT NULL,
  `nofl` int(4) NOT NULL,
  `adres` varchar(100) NOT NULL,
  `arcd` varchar(7) DEFAULT NULL,
  `pstcd` varchar(7) DEFAULT NULL,
  `zipcode` int(11) DEFAULT NULL,
  `state` varchar(50) DEFAULT NULL,
  `borough` varchar(30) NOT NULL,
  `city` varchar(50) NOT NULL,
  `country` varchar(100) NOT NULL,
  `startingprice` varchar(14) NOT NULL,
  `dateends` datetime NOT NULL,
  `submission_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `endnotified` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

The code below will be executed through php

DELETE auction.items as its, auction.images as ig, auction.additionalbathroom as addbth, auction.additionalbedroom as addbr,
       auction.additionalfeatures as addfr, auction.additionalprojectinfo as addpj, auction.additionalpropertyinformation as addpi,
	   auction.attic as ac, auction.basement as bsm, auction.basketballcourt as bbct, auction.bids as bds
	   
FROM items as its, images as ig, additionalbathroom as addbth, additionalbedroom as addbr,
	 additionalfeatures as addfr, additionalprojectinfo as addpj, additionalpropertyinformation as addpi,
	 attic as ac, basement as bsm, basketballcourt as bbct, bids as bds

WHERE its.id = ig.item_id AND its.id = addbth.item_id AND its.id = addbr.item_id AND its.id = addfr.item_id 
                          AND its.id = addpj.item_id AND its.id = addpi.item_id
						  AND its.id = ac.item_id
			              AND its.id = bsm.item_id AND its.id = bbct.item_id AND its.id = bds.item_id
AND its.id ='1'

[smantscheff]

Without taking a closer look, there is at least one thing fishy: Your tables are defined as MyISAM but use foreign keys. Foreign keys are meaningless in MySQL unless you use InnoDB tables. Therefore it might well be that your data are corrupted (violating relational integrity).

[codeblock]

Without taking a closer look, there is at least one thing fishy: Your tables are defined as MyISAM but use foreign keys. Foreign keys are meaningless in MySQL unless you use InnoDB tables. Therefore it might well be that your data are corrupted (violating relational integrity).

The foreign keys was added recently, the problem accued before that.

[smantscheff]

Please check your data integrity by switching to InnoDB before further probing the problem.

[codeblock]

Please check your data integrity by switching to InnoDB before further probing the problem.

That didn't work either all i got when i ran the sql was 0 row(s) deleted. ( Query took 0.0365 sec )

[smantscheff]

Your delete syntax is flawed. You're not supposed to use field names in DELETE statements. Try without.

[codeblock]

Your delete syntax is flawed. You're not supposed to use field names in DELETE statements. Try without.

Got it working now thanks for your help