Multiple syntax errors in the script
And a DDL statement implicitly commits, so your transaction block here is meaningless
And the Insert commands have too many values for the columns they list
And they refer to non-existent referenced key values
When these problems are fixed and the ddl is simplified to just what's required ...
drop table if exists grupos;
CREATE TABLE `grupos` (
`id_grupo` int primary key auto_increment,
`grupo` varchar(255) NOT NULL,
`prueba` int DEFAULT NULL ,
FOREIGN KEY (`prueba`) REFERENCES `grupos` (`id_grupo`)
ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB;
INSERT INTO `grupos` (`id_grupo`, `grupo`, `prueba`) VALUES
(1, 'a', NULL),
(2, 'b', NULL),
(3, 'c', NULL),
(4, 'd', 3);
select * from grupos;
update grupos set prueba=2 where prueba=3;
select * from grupos;
delete from grupos where id_grupo=2;
select * from grupos;
... we get these results ...
drop table if exists grupos;
Query OK, 0 rows affected (0.02 sec)
localhost.test:
CREATE TABLE `grupos` (
-> `id_grupo` int primary key auto_increment,
-> `grupo` varchar(255) NOT NULL,
-> `prueba` int DEFAULT NULL ,
-> FOREIGN KEY (`prueba`) REFERENCES `grupos` (`id_grupo`)
-> ON DELETE SET NULL ON UPDATE CASCADE
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)
localhost.test:
localhost.test:
INSERT INTO `grupos` (`id_grupo`, `grupo`, `prueba`) VALUES
-> (1, 'a', NULL),
-> (2, 'b', NULL),
-> (3, 'c', NULL),
-> (4, 'd', 3);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
localhost.test:
localhost.test:
select * from grupos;
+----------+-------+--------+
| id_grupo | grupo | prueba |
+----------+-------+--------+
| 1 | a | NULL |
| 2 | b | NULL |
| 3 | c | NULL |
| 4 | d | 3 |
+----------+-------+--------+
4 rows in set (0.00 sec)
localhost.test:
update grupos set prueba=2 where prueba=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
localhost.test:
select * from grupos;
+----------+-------+--------+
| id_grupo | grupo | prueba |
+----------+-------+--------+
| 1 | a | NULL |
| 2 | b | NULL |
| 3 | c | NULL |
| 4 | d | 2 |
+----------+-------+--------+
4 rows in set (0.00 sec)
localhost.test:
delete from grupos where id_grupo=2;
Query OK, 1 row affected (0.00 sec)
localhost.test:
select * from grupos;
+----------+-------+--------+
| id_grupo | grupo | prueba |
+----------+-------+--------+
| 1 | a | NULL |
| 3 | c | NULL |
| 4 | d | NULL |
+----------+-------+--------+
3 rows in set (0.00 sec)
What's the problem?