October 1, 2009 at 4:16 pm
If you have a table A which has several constraints where the primary key of Table A exists in several junction tables. How can it be possible that all of the data in table A can be gone but the data in the junction tables still exist? Without someone going in and removing the constraints, deleteing all data from table A and then adding back the constraints with the "check existing data" set to no.
October 2, 2009 at 6:41 am
mmmhhh... scenario description it's not clear enough - depending on interpretation it may be working the way it's supposed to work.
Could you please simplify to just two tables and show us how PK and FK constraints are defined?
_____________________________________
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.October 2, 2009 at 6:48 am
A couple of possibilities
Do you use replication... and are the constraints marked as "NOT FOR REPLICATION"?
Are the foreign keys set up with CASCADE deletes?
October 2, 2009 at 6:50 am
Table A has a primarykey called has a primarykey called TblAPrimeKey
Table B has a primarykey called TblBPrimeKey as well as a foreign key from table A called TblAPrimeKey.
There are several records in table B and Table A.
Table A is now empty but there are still records in Table B
October 2, 2009 at 6:51 am
Someone was working on setting the cascading deletes but if that was the case wouldn't the junction tables be empty as well?
October 2, 2009 at 6:55 am
dndaughtery (10/2/2009)
Someone was working on setting the cascading deletes but if that was the case wouldn't the junction tables be empty as well?
Yes...I was just about to edit my post to say ignore the cascade delete bit... I was looking at things the wrong way round.
The bit about replication is still a possiblity.
October 2, 2009 at 6:58 am
I'm not very familiar with replication however I do know that we aren't distributing the data to multi servers. Everything is on one server.
October 2, 2009 at 6:59 am
Is there a way to look into the log files and see who and what happened?
October 3, 2009 at 3:36 pm
Do you still see pkey and fkey relation on tht table? If yes then check sysobjects table for their creation date. It might have happened that someone dropped it and deleted the data followed by its re-creation.
MJ
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply