The forum will be down for scheduled maintenance on Saturday, March 4 beginning at 10am EST. Actual downtime is unknown but may be several hours.

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.

asked 17 Jan '12, 15:57

Glenn%20Barber's gravatar image

Glenn Barber
1.1k234051
accept rate: 0%

edited 15 Mar '13, 21:14

Mark%20Culp's gravatar image

Mark Culp
22.5k9129264


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?

permanent link

answered 17 Jan '12, 16:37

Glenn%20Paulley's gravatar image

Glenn Paulley
10.7k568104
accept rate: 43%

Thanks Glenn

We are using Cascade Delete for some subordinate dependent data.

(17 Jan '12, 18:15) Glenn Barber

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.

permanent link

answered 18 Jan '12, 11:32

Glenn%20Paulley's gravatar image

Glenn Paulley
10.7k568104
accept rate: 43%

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.

(18 Jan '12, 11:59) Glenn Barber

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

(18 Jan '12, 12:39) Volker Barth

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.

permanent link

answered 18 Jan '12, 08:51

anil%20goel's gravatar image

anil goel
161
accept rate: 0%

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

permanent link

answered 13 Jun '12, 09:58

Volker%20Barth's gravatar image

Volker Barth
29.5k291441646
accept rate: 32%

edited 13 Jun '12, 10:00

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×58
×22

question asked: 17 Jan '12, 15:57

question was seen: 1,042 times

last updated: 15 Mar '13, 21:14