Can Declarative RI be tested for existence of a reference?

[glenn_barber]

Our application (SA 11) uses declarative RI extensively to insure consistency and to prevent the application from deleting accounts or codes which are relied upon in records throughout the system.

Is there a way to test for existence of a dependency prior to deletion using the RI rather than forcing an error (deletion) or just issuing a query which checks the database.

If our application issues its own query for existence, the danger is that the RI and the application query can get out of synch.

[Former Member]

While there are sometimes good reasons - mostly ease of implementation - to use ON DELETE CASCADE, in my experience as a DBA we never used cascading constraints. This was due to one, or a combination of, the following factors:

1. The application wanted to query the extant rows prior to attempting the DELETE, so that a specific error message, or possibly sets of error messages, could be generated.
2. The quantity of data was large enough that doing all the DELETE operations in a single transaction was too cumbersome (in terms of concurrency with other connections) and so the DELETE operations had to be done piecemeal.
3. There was substantially greater risk of deadlock amongst concurrently updating connections.
4. It was desirable to perform the DELETE in the background, rather impacting the response time to the user.

I think that's what Anil is referring to with his suggestion of the MERGE statement; perform the logic you want in your application, and (consequently) turn off DELETE CASCADE altogether so that you can be certain that all RI relationships hold no matter what your application does.

[glenn_barber]

We have only been using cascade delete in very small transaction units of work on single transaction sets when the entire transaction (or master record) and its subordinate data are being removed (a rather infrequent activity). Our application class library was designed to insert unit of work top-to-bottom and to utilize the cascade delete to avoid the bottom-to-top deletion process which can be tedious and more client intensive. Its been working successfully for us without problems for quite some time. For the reasons above, we would never consider a cascade delete in other situations. In the future we may be moving off the Client-Server architecture and may need to reconsider some of these architectures.

We have used the MERGE statement in some other application situations but I will have to revisit its syntax to see how we could use it to avoid a pre-delete query to ensure the record is not being referenced without individually querying each known reference in he application. Although we use that technique successfully, it opens the door to getting the large application and the data model out of synch.

[VolkerBarth]

Another point to think about deals with distributed datases (like replication systems😞

What should be done when a "parent row" (with its dependent "child rows") is deleted in database db1 to which (in parallel) a new dependent child has been added in db2 - now that delete operation arrives at db2 and must handle a row not existing in db1.

Here CASCADING DELETE seems a useful enhancement, as otherwise the replication logic would have to handle these dependencies explicitly (and somewhat independent of the application logic that may use DELETE RESTRICTED for the very good reasons you have mentioned).

That's a design question I haven't found a good answer yet...

[Former Member]

RI constraints get invoked upon updates to the database, and not queries, so the short answer to your question is no.

Are you using RI constraints for DELETE operations that are other than ON DELETE RESTRICT?

[glenn_barber]

Thanks Glenn

We are using Cascade Delete for some subordinate dependent data.

[Former Member]

Take a look at the MERGE statement which should provide exactly the semantics you need and resolve the issue of the "RI and the application query getting out of synch" since it's the MERGE itself that internallu performs the query.

[VolkerBarth]

FWIW, I'm not sure whether this satisfies your requirements - just as a note:

The sa_check_commit system procedure
Checks for outstanding referential integrity violations before a commit.

I don't find the docs very clear about that - it justs tells about the table and the role name of an offending FK relationship...