mysql on delete cascade error

[kalster]

I am trying to delete child nodes using the parent_id, id. like the following...

 

id | parent_id

1  | 0

2  | 1

3  | 2

 

deleting id 2 should also delete id 3. How to get the follow code to work?

 

i am using mysql 5.1. this code gives a query syntax error on line number... but no error message. 

"CREATE TABLE test(
  `id` INT(20) NOT NULL AUTO_INCREMENT,
  `parent_id` INT( NOT NULL,
  `title` TEXT NOT NULL,
  FOREIGN KEY (parent_id) REFERENCES test (id) ON DELETE CASCADE,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 ";

when i use the following code, there is no errors.

"CREATE TABLE test(
  `id` INT(20) NOT NULL AUTO_INCREMENT,
  `parent_id` INT( NOT NULL,
  `title` TEXT NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=myisam DEFAULT CHARSET=utf8 ";

notice in the above code that i have changed the word INNODB to MYISAM for the ENGINE and removed the FOREIGN KEY. this code works but no "on delete cascade".

[Jacques1]

Foreign keys can only reference unique fields, so the PRIMARY KEY must come before the FOREIGN KEY. Otherwise MySQL thinks that id is a non-unique field.

 

Declaring parent_id as NOT NULL also makes no sense, because then you won't be able to insert any rows at all. What's the parent of the very first row? It can't have one.

[kalster]

the value of the very first row would be 1 for parent_id. i will make the changes and then reply to this topic. can you give me an example on when i would use not null?

[kalster]

i made your suggested changes and with this query it gives an error message that all queries were not done.

$query = "INSERT INTO test (parent_id) VALUES ('1')";
$result = mysqli_query($link, $query);

[Jacques1]

Whatever strange reason you may have for fumbling with self-referencing records, don't do it. It's weird, and it comes with a lot of problems. For example, you won't be able to delete the row (unless you temporarily remove the foreign key and then add it again).

 

Just use NULL like everybody else. In this case, NULL means that there is no parent.

 

I also have no idea why you quote the number 1. Numbers are not strings. Yes, MySQL is sloppy and lets you do it, but it's still poor practice.

[kalster]

ok. thank you. i will use two tables

Edited November 1, 2014 by kalster

[Barand]

One table will suffice if you use NULL to represent no parent as Jaques stated

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `col1` int(11) DEFAULT NULL,
  `parent_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk1` (`parent_id`),
  CONSTRAINT `fk1` 
    FOREIGN KEY (`parent_id`) 
    REFERENCES `test` (`id`) 
    ON DELETE CASCADE 
    ON UPDATE NO ACTION
)

mysql> select * from test;
+----+------+-----------+
| id | col1 | parent_id |
+----+------+-----------+
|  1 |  222 |      NULL |   (top level, no parent)
|  2 |  333 |         1 |
|  3 |   44 |      NULL |   (top level, no parent)
|  4 |   22 |         1 |
|  5 |   32 |         2 |
|  6 |   21 |         3 |
|  7 |   43 |         3 |
+----+------+-----------+
7 rows in set (0.00 sec)

mysql> DELETE FROM test WHERE id = 1;
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+----+------+-----------+
| id | col1 | parent_id |
+----+------+-----------+
|  3 |   44 |      NULL |
|  6 |   21 |         3 |
|  7 |   43 |         3 |
+----+------+-----------+
3 rows in set (0.00 sec)