remove ON DELETE CASCADE

Question

I have a child table. and foreign key there with ON DELETE CASCADE while creating the table.

There are no records either in child or parent table.

I want the primary key, foreign key to be as they are but want to remove only the CASCADING option from the child table .

is there anyway that i can Alter that child table.

Thank you.

Answer 1

The table:

SHOW CREATE TABLE table; 

CREATE TABLE `table` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `id_departamento` int(11) unsigned DEFAULT NULL,
  `name` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `id_departamento` (`id_departamento`),
  CONSTRAINT `departamentos_direcciones_pedidos_ibfk_1` FOREIGN KEY (`id_departamento`) REFERENCES `departamentos` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

1. First drop foreign key.

ALTER TABLE departamentos_direcciones_pedidos DROP CONSTRAINT departamentos_direcciones_pedidos_ibfk_1;

2. Second, create the correct foreign key

ALTER TABLE departamentos_direcciones_pedidos ADD FOREIGN KEY (id_departamento) REFERENCES departamentos(id);