Ask Your Question
1

how to add cascade on delete using sql

asked 2016-06-13 23:11:07 +0100

jay Arr gravatar image

I have an internet published Base application SecretaryBird and just discovered that I've missed a CASCADE on DELETE off an existing relationship between two tables.

Adding this on the relationship diagram is really easy however my users are not database experts (they are poets) and I cannot ask them to do this.What I want to do is have them exercise a built in update application option which will execute (hidden from them) some SQL to update the relationship and add the CASCADE on DELETE.

I know that I''ll have to change a foreign key and edit the database schema. I've looked at various examples but none do exactly what I want. They seem to imply that I'll have to delete and recreate the relationship - something I don't want to do since a) users will have data in the two tables & b) if anything goes wrong it could seriously mess up a users database.

So can anyone help with the SQL please? FYI: the database is HSQLDB

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2016-06-18 17:36:26 +0100

jay Arr gravatar image

Ratslinger, thank you for your answer.

edit flag offensive delete link more
2

answered 2016-06-14 00:01:11 +0100

Ratslinger gravatar image

updated 2016-06-14 00:16:19 +0100

Used similar to this : ALTER TABLE YOURTABLENAME ADD CONSTRAINT fk_name_yourname FOREIGN KEY (FIELDNAME) REFERENCES OTHERTABLE (OTHERFIELDNAME) ON DELETE CASCADE

Also, see this link.

Edit:

If you have an existing key already (without the DELETE CASCADE) you must first drop the existing key:

ALTER TABLE YOURTABLENAME DROP CONSTRAINT fk_existingkey_name
edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-06-13 23:11:07 +0100

Seen: 136 times

Last updated: Jun 18 '16