如何使用SQL查询删除父表和子表行？

I want to delete rows from parent and child tables.

我想删除父表和子表中的行。

For Example

例如

I have a two tables: 1) group 2) group_member

我有两个表：1）组2）group_member

group table have the following fields:

组表包含以下字段：

group_id, Group_name, user_id

group_id，Group_name，user_id

and group_member have the following fields:

和group_member有以下字段：

id, group_id, user_id

id，group_id，user_id

I would like to delete something like this,

我想删除这样的东西，

Supposing group table have user_id 2 and all members of user_id is available on group_member table

假设组表具有user_id 2，并且group_member表上的user_id的所有成员都可用

Table 1 image

表1图像

Table 2 image

表2图像

if delete query fires - all records with id = 19 will be deleted from both tables

如果删除查询触发 - 将从两个表中删除id = 19的所有记录

3 个解决方案

#1

You need to use DELETE CASCADE, just use below tables and test.

您需要使用DELETE CASCADE，只需使用下面的表格并进行测试。

-- ----------------------------
-- Table structure for candidate_master
-- ----------------------------
DROP TABLE IF EXISTS `candidate_master`;
CREATE TABLE `candidate_master` (
  `candidate_id` int(10) unsigned NOT NULL,
  `first_name` varchar(100) DEFAULT NULL,
  `middle_name` varchar(100) DEFAULT NULL,
  `last_name` varchar(100) DEFAULT NULL,
  `phone_number` varchar(25) NOT NULL,
  `callerid` varchar(25) DEFAULT NULL,
  `duration` int(11) DEFAULT NULL,
  `city` varchar(50) DEFAULT NULL,
  `state` varchar(80) DEFAULT NULL,
  `date_modified` datetime DEFAULT NULL,
  `key_skills` text,
  `visa` varchar(20) DEFAULT NULL,
  `status` varchar(100) DEFAULT NULL,
  `pvm` enum('voicemail','ping') DEFAULT NULL,
  `id` int(11) NOT NULL,
  `isDNC` enum('NO','YES') NOT NULL DEFAULT 'NO',
  UNIQUE KEY `number` (`phone_number`) USING BTREE,
  KEY `candidate_master_ibfk_1` (`id`),
  CONSTRAINT `candidate_master_ibfk_1` FOREIGN KEY (`id`) REFERENCES `cgListUpdateLog` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Table structure for cgListUpdateLog
-- ----------------------------
DROP TABLE IF EXISTS `cgListUpdateLog`;
CREATE TABLE `cgListUpdateLog` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `file_name` varchar(255) DEFAULT NULL,
  `pass` int(11) DEFAULT NULL,
  `fail` int(11) DEFAULT NULL,
  `UpdatedOn` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `status` enum('NEW','PROCESSING','PROCESSED','FAILED') DEFAULT NULL,
  `dialStatus` enum('YES','NO') DEFAULT 'NO',
  `notDialed` int(11) DEFAULT NULL,
  `filestatus` enum('processed','pending') DEFAULT 'processed',
  `comment` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=latin1;

For any further help let me know!!

如有任何进一步的帮助，请告诉我！

#2

The other answers are fine, but here is some SQL specific to your question. As mentioned use the database to do the magic for you.

其他答案很好，但这里有一些特定于您的问题的SQL。如上所述，使用数据库为您做魔术。

Add a FOREIGN KEY constraint to the group_member table with the ON DELETE CASCADE reference option as such:

使用ON DELETE CASCADE引用选项向group_member表添加FOREIGN KEY约束，如下所示：

ALTER TABLE `group_member`
ADD CONSTRAINT `fk_group_member_group` FOREIGN KEY ( `group_id` )
REFERENCES `group` ( `group_id` ) ON DELETE CASCADE;

That will ensure a delete on the group with a specific id will cascade down to the group_member table.

这将确保具有特定ID的组上的删除将级联到group_member表。

#3

I suggest you to use ON CASCADE DELETE for this scenario.

我建议你在这个场景中使用ON CASCADE DELETE。

From MYSQL Doc:

来自MYSQL Doc：

CASCADE: Delete or update the row from the parent table, and automatically delete or update the matching rows in the child table. Both ON DELETE CASCADE and ON UPDATE CASCADE are supported. Between two tables, do not define several ON UPDATE CASCADE clauses that act on the same column in the parent table or in the child table.

CASCADE：从父表中删除或更新行，并自动删除或更新子表中的匹配行。支持ON DELETE CASCADE和ON UPDATE CASCADE。在两个表之间，不要定义几个ON UPDATE CASCADE子句，这些子句作用于父表或子表中的同一列。

Note: Cascaded foreign key actions do not activate triggers.

注意：级联外键操作不会激活触发器。

#1