Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
 Posted - 2005-12-30 : 09:49:14
|
| Hi,Typically an application will have a page which lists some entities from a database table in a grid and they'll be a delete button on each row.If one presses the delete button and item to be deleted is being referenced from else where then of course, they'll be a referential integrity violation and an exception thrown.What I want to do is hide the delete button for all rows which are being referenced. The question is, how does one best retrieve this information?I can think of three ways....1. Have a sub-query on the query which returns the list. This sub-query checks to see if an item exists which is refencing the row in question.2. Do an outer join with the referencing table, group by all the columns in the table being referenced and use an aggregate function on a column from the referencing table to indicate if anything was joined for a given row.3. When a page of results are ready to be rendered, go and do a second query to look for items which reference the items in just the current page of results.Anyone got a better idea? Grids rendering database rows are so common that surely the must be a well known way of dealing with this? If not, do most people just let that exception get thrown upon a delete?Cheers, XF. |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-12-30 : 10:23:35
|
| >>Typically an application will have a page which lists some entities from a database table in a grid and they'll be a delete button on each row.I don't agree with this statement but that doesn't matter. I think we can boil your options to two.1. don't offer a delete option for referenced keys (using one of your 3 options)2. allow the delete option but come up with a gracefull way to handle itI don't really like (my) option 1 for a couple reasons. One is that if a key is referenced by many tables, the check for refs every time data is returned is not practical. Also, with real-time data, the reference may not exist when the data is returned but could exist when the delete is attempted.I would do the checks in the delete SPs before the delete is attempted. If refs exists either use a custom error/with appropriate severity or a return code that the application understands so that the event is handled in a graceful, user-friendly way. The chance of a reference being added after the check is completed still exists but is much less likely this way.Be One with the OptimizerTG |
 |
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2005-12-30 : 10:39:36
|
quote:
>>Typically an application will have a page which lists some entities from a database table in a grid and they'll be a delete button on each row.I don't agree with this statement but that doesn't matter.
So you'd have a delete button on an item detail? |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-12-30 : 10:58:21
|
| my only point was that an application is so general it doesn't "typically" do anything. For example I just opened Outlook, Enterprise Manager, and Turbo Tax and didn't see a delete button on any row of data. Sometimes it's a memu item, or a tool bar, or a right click, or all three. It's really not an issue, I just kind of chuckled when I read "typically an application..." On the other hand, this page does have a delete button on all posts (rows) that can be deleted by whoever is viewing the page so that goes against my original suggestion. We all gotta do whatever the product manager decides on anyway :)Be One with the OptimizerTG |
|
|
|
|
|
|
|
|
Find out if list items are being referenced