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.

score 5 · Answer 1 · answered Feb 17 '20 at 11:07

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

First drop foreign key.

ALTER TABLE departamentos_direcciones_pedidos DROP CONSTRAINT departamentos_direcciones_pedidos_ibfk_1;

Second, create the correct foreign key

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

score 3 · Answer 2 · answered Oct 28 '14 at 09:34

3

ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }

The default is NO ACTION.

So try altering your child table back to default.

answered Oct 28 '14 at 09:34

knkarthick24

3,106
15
21

fid number(5) references parent(pid) on delete cascade; so i need to issue alter table child modify fid on delete set default..... right ? – Raghav Oct 28 '14 at 10:45

score 1 · Answer 3 · answered Oct 28 '14 at 09:38

(Oracle) You can only alter the state of a constraint. ON DELETE is not a state. So you need to drop constraint and recreate it.

drop table t1 cascade constraints;
create table t1 (id number unique, rid number constraint t1_fk references t1(id) on delete cascade);

alter table t1 drop constraint t1_fk;
alter table t1 add constraint t1_fk foreign key(rid) references t1(id);

if you're using Oracle there are different dictionary views which might help you to recreate the constraint correctly

score 1 · Answer 4 · edited Aug 08 '20 at 10:51

1

Export the database as a .sql file
Then press ctrl + H to replace all ON DELETE CASCADE with ""
Then drop the tables from the DB and use the new file to instantiate a new one without ON DELETE CASCADE

edited Aug 08 '20 at 10:51

Daniel_Knights

7,940
4
21
49

answered Jan 12 '20 at 17:13

Kerols Monsef

25
2

remove ON DELETE CASCADE

4 Answers4

Linked