February 9th, 2004, 08:40 PM
Keep track of references to table entries/rows
Hello, I am new to db's, so I don't know if this is just common knowledge or what...
I want to design my tables so that maintenance/pruning will be easy in the future. The issue is that most fields have many-to-many relationships, and I do not want to delete rows that have references to them.
So, I am trying to figure out an efficient way to keep track of the number of references an entry in a table has so that on a regular basis I may delete all entries in all tables with no references to them. (Does this make any sense at all???).
Is there an efficient way to do this? I was thinking of adding a column in each table for the number of references the entry had to it. This seems like a safe way to do it, but it also seems like it adds A LOT OF OVERHEAD. Every time an insert is done (into any table that references other tables), I must update other table(s) entry(ies) to increment the number. Of course, also, whenever a delete is made, I have to update all referenced entries in all concerned tables to decrement their corresponding counts (number of refs).
I had been looking at the FOREIGN KEY constraint, and it seems that all I have found is that when the primary key it is linked to is deleted, then I can set all referencing foreign keys to delete as well. This is the opposite of what I want to do though (I want to delete entries once no other entries from other tables reference it).
Please help! There has to be a better way to do this. I am sorry if I didn't explain this very well. Oh yeah, I am using MS SQL Server 2000.