0
 Followers
4
 Likes

Adding a new foreign key rule to NULL on delete

Database
On our radar

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).

Created on 27 Oct 2017
Comments (10)

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 :).

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)

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).

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

*does not do NULL

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

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?

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)

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?

As far as I know MSSQL and MySQL support this.

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

views
189
Followers
0