2

I trying to come up with the best solution to delete rows from child table in my 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.

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.

I'm having a hard time figuring it out, tried looking and found How do I use on delete cascade in 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:

* #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

Community
  • 1
  • 1
elad.chen
  • 2,375
  • 5
  • 25
  • 37
  • 1
    you've got the correct basic structure. with that FK, if a user is deleted, any associated records in users_information will also be deleted. – Marc B Mar 08 '13 at 16:47
  • I'm sorry I forgot to mention the code for "users_information" produces an error. I'll update my question. – elad.chen Mar 08 '13 at 16:49
  • the fields being linked in an FK relationship DO have to have IDENTICAL definitions. you've got `int(11)` and `int(20)` for the users.id/users_information.userid fields. The bracketed number is just a display hint (how many digits to show) to mysql, but it still counts as a "difference" for FK link purposes. – Marc B Mar 08 '13 at 16:50
  • I'm not quite sure, but tried to follow what you said, I still receive the error I mentioned in my question. – elad.chen Mar 08 '13 at 16:53
  • 3
    you're missing a comma on the KEY line immediately before the constraint. – Marc B Mar 08 '13 at 16:54
  • 2
    Make sure that you create the users table before users_information. – landons Mar 08 '13 at 16:55
  • @MarcB .. This happens to me to often this past to weeks :). Code was validated and works, Thanks man. – elad.chen Mar 08 '13 at 16:58
  • 3
    By adding a comma after **KEY `user_id` (`user_id`)** and changing the order of the table creation I was able to get this to work exactly as you require. [Example on SQL Fiddle](http://www.sqlfiddle.com/#!2/3ada1/1) – GarethD Mar 08 '13 at 16:58
  • @MarcB Actually the `(11)` and `(20)` do not pose a problem for the FKs. See the working link by Gareth above. – ypercubeᵀᴹ Mar 08 '13 at 17:23
  • This question appears to be off-topic because it is about fixing a syntax error. – RandomSeed Jul 29 '13 at 23:34

1 Answers1

2

Add a comma after the following line

KEY user_id (user_id)

Abhijit Buchake
  • 142
  • 1
  • 9