如何级联删除父表行

I trying to come up with the best solution to delete rows from child table in my sql.

我试图找到最好的解决方案来删除我的SQL中子表的行。

e.g I have two tables, one is called users the other is called users_information. The second table, users_information uses a foreign key called user_id which is actually the "id" in "users" table.

例如,我有两个表,一个叫做用户,另一个叫做users_information。第二个表users_information使用名为user_id的外键,实际上是“users”表中的“id”。

I want to use the cascade delete option, whenever I delete a row from "users" table (by using "id" ofc) I'd like to remove the same row inside users_information where id = user_id.

我想使用级联删除选项,每当我从“users”表中删除一行时(通过使用“id”ofc)我想删除users_information中id = user_id的同一行。

I'm having a hard time figuring it out, tried looking and found How do I use on delete cascade in mysql?

我很难搞清楚,尝试查找并找到如何在mysql中使用删除级联?

But that isn't explained well, I'm sure it is the solution I aim for nevertheless.

但这并没有得到很好的解释,我确信这是我的目标解决方案。

Here's my tables structure, If you see anything I might be doing wrong / not the best way, tell me.

这是我的桌子结构,如果你看到任何我可能做错了/不是最好的方式,请告诉我。

CREATE TABLE `users_information` (
    `id` int(20) NOT NULL AUTO_INCREMENT,
    `user_id` int(11) NOT NULL,
    `first_name` varchar(15) NOT NULL,
    `last_name` varchar(15) NOT NULL,
    `country` varchar(30) NOT NULL,
    `profession` varchar(40) NOT NULL,
    `gender` varchar(6) NOT NULL,
    `birthday` datetime NOT NULL,
    `city` varchar(50) NOT NULL,
    PRIMARY KEY (`id`),
    KEY `user_id` (`user_id`)
    CONSTRAINT `myForeignKey` FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

    CREATE TABLE `users` (
    `id` int(20) NOT NULL AUTO_INCREMENT,
    `password` varchar(82) NOT NULL,
    `email` varchar(60) NOT NULL,
    `created` date NOT NULL,
    `user_type` int(2) NOT NULL,
    `active` smallint(2) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

I forgot to mention: the code for "users_information" produces the following error:

我忘了提到:“users_information”的代码会产生以下错误:

* #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONSTRAINT myForeignKey FOREIGN KEY (user_id) REFERENCES users (id) O' at line 13

*#1064 - 您的SQL语法有错误;查看与您的MySQL服务器版本对应的手册,以便在第13行使用“CONSTRAINT myForeignKey FOREIGN KEY(user_id)REFERENCES用户(id)O”附近使用正确的语法

1 个解决方案

#1

Add a comma after the following line

在以下行之后添加逗号

KEY user_id (user_id)

KEY user_id(user_id)

#1