On 21/12/2005, Dušan Pavlica wrote:
> Hello,
> I have master-detail tables and I would like to delete in one
> statement master record and all detail records but not every master
> record has details. MySQL versions 4.1.10 and higher. Could someone
> help me, please, to create such a query?
>
> Example:
> CREATE TABLE `master_tbl` (
> `ID` int(10) unsigned NOT NULL auto_increment,
> `Desc` varchar(45) NOT NULL default '',
> `Data` varchar(45) NOT NULL default '',
> PRIMARY KEY (`ID`)
> ) ENGINE=InnoDB;
>
> CREATE TABLE `detail_tbl` (
> `Master_ID` int(10) unsigned NOT NULL default '0',
> `ID` int(10) unsigned NOT NULL default '0',
> `Desc` varchar(45) NOT NULL default '',
> PRIMARY KEY (`Master_ID`)
> ) ENGINE=InnoDB;
You realize you can only have one detail record per Master_ID (since
you made Master_ID the PK in the detail table)?
Since you are using InnoDB, you should enforce referential integrity
with referential actions:
CREATE TABLE `detail_tbl` (
`Master_ID` int(10) unsigned NOT NULL default '0',
`ID` int(10) unsigned NOT NULL default '0',
`Desc` varchar(45) NOT NULL default '',
PRIMARY KEY (`Master_ID`),
FOREIGN KEY (`Master_ID`) REFERENCES `master_tbl` (`ID`)
ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB;
That way, you only have to
DELECT FROM master_tbl WHERE Master_ID = 10;
to delete both master and detail records.
--
felix
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
