February 16, 2010 at 7:30 am
Hey all.
I was wondering how to do this.
Basically I have a lot of tables, with very complex constraints over many tables. This is a huge database of which the design cannot be changed as it is to gain certification.
For every table in it, I have added a deletion flag, so that I never actually delete any rows, only flag them as deleted. However, I would like to also check before logically deleting a row, that it would have conformed to referential integrity if there was no such thing as deletion flags. So basically I need to check every table that is a child to it through a foreign key constraint, and make sure there are no 'active' rows. i.e. there must be no rows that match on the child table, or all the rows would have to also be set to deleted through the flag, before it allows me to delete the parent row.
I have been playing about with a sql script to try and do this and I am running into problems. I am using sys.foreign_keys and sys.foreign_key_columns to get to the information that I need. This gives me all child tables, along with the columns that are linked to my table. I then try and create a dynamic piece of sql to read the table for a count of rows, but cannot seem to (using my current while loop logic) replace a hard coded value with the actual values I need to do a search on...for example the parent tables columns to match.
There must be an easier way for me to do this! And this, I would assume, must be a common problem with anyone who wishes to do logical deletions and keep constraints in check.
Any ideas?
Cheers.
February 16, 2010 at 11:04 am
despite my moral opposition to soft deletes I think you could maybe use a try catch block to accomplish what you need. We warned It's pure ugly hackery and totally untested and probably shouln't even be put on the internet.
begin try
begin transaction
delete from foo where somecondition = 'true'
rollback transaction
update foo set deleted=1 where somecondition = 'true'
end try
begin catch
raiserror('that wont work', 11,1)
end catch
Craig Outcalt
February 16, 2010 at 11:18 am
Hi.
It was actually one my first options I looked at before trying to do it the more complicated way lol..however, this would only work if the child tables did not also have their rows logically deleted. But as these also only get logically deleted, the physical delete in a transaction would always pass.
February 17, 2010 at 2:28 pm
:pinch: soft deletes? why?
Either way, I can see poster writting a function to handle the soft constraining/deleting. Function shall be smart enough to query SS catalog for information about relationships between tables... also, how deep chain of referential integrity is.
By the way ... I'm officially against soft deletes.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 17, 2010 at 4:32 pm
paul.davidson.uk (2/16/2010)
There must be an easier way for me to do this! And this, I would assume, must be a common problem with anyone who wishes to do logical deletions and keep constraints in check.
I think you are making a mistake to do this dynamically at runtime. I suggest writing some scripts to generate at design-time some static SQL delete procedures. Each delete proc should check dependent rows before deleting. That way you avoid static code, make your production code easier to read and maintain and probably will do checks more efficiently as well.
February 18, 2010 at 2:31 am
Yes, I ended up doing it that way as I could not find a way to do it dynamically. Only problem now is for any release I will need to regenerate ALL SP's to pick up any changes to constraints in the database which I wanted to avoid initially, but for performance and ease of debugging I've done it this way.
Thanks.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply