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