Adding a new foreign key rule to NULL on delete

By Claring on 27 Oct 2017

So I somehow thought that Ignore would automatically set the Id of the foreign key field to NULL, I was sad to discover that this is not the case.

For non mandatory foreign keys I would love to NULL the value on delete, instead of just keeping the value (which effectively makes the constraint useless, since you still have to do a table scan on the target table to know if the Id is actually there).

Kilian Hekhuis27 Oct 2017

The problem being that you could have thousands if not millions of references to a foreign key, and your delete statement would take half a day to complete :).

Claring27 Oct 2017

That's true, but it would be the same for a CASCADE DELETE right? (which might be even worse if the cascaded table also has been referenced as a foreign key)

Kilian Hekhuis27 Oct 2017

No, a cascaded delete is handled by the database. An update must be handled by the Platform (afaik there's no delete constraint that can set the foreign key to NULL).

Justin James28 Oct 2017

Other problem is that the system does do NULL, it does 0. And since a record with ID 0 will never exist in the DB, it means that the DB constraint will be set to "ignore". So you STILL have the problem where you have a useless constraint.

J.Ja

Justin James28 Oct 2017

*does not do NULL

Kilian Hekhuis30 Oct 2017

Well, for foreign keys, and that's what we're talking about here, it does do NULL of course.

Claring31 Oct 2017

On a database you could put the delete rule on SET NULL instead of CASCADE.

You could also put it on SET DEFAULT to make it reset to the default value.

No need to let the outsystems platform handle something the database could right?

Claring31 Oct 2017

https://www.techonthenet.com/sql_server/foreign_keys/foreign_delete_null.php

^ (Kinda wish I could edit my posts on an idea so I don't have to keep replying to add new information)

Kilian Hekhuis31 Oct 2017

True, if the database can do it automagically, the platform could support it. However, the Platform supports multiple databases, and I think the currently supported features are available for all of those. Not sure if Oracle (probably) or MySQL (probably not) supports it as well?

Claring1 Nov 2017

As far as I know MSSQL and MySQL support this.

I'm not sure if there is a database which doesn't support this.