How to check that a record of master table is being used/referenced by child table

Question

In my ERP application, I am taking a extra field named 'IsRemoved' as boolean. Whenever a user deletes any record, the record is not deleted - only its "IsRemoved" column gets value 'true'. It helps us to be able to recover data when ever we want, and its working fine.

The problem is that when ever user deletes record of Master Table, how can we check that all its child table not referring this record (because we do not preform physical deletion, we just mark "isremoved" field as true)?

Please provide me any query or sp from which I can do check that master record is used in any of its child or not.

Answer 1

Can you, within a transaction, insert a replacement record with all values the same other than IsRemove='true', then remove the original record? This will generate a new primary key for the replacement record, so that old references cannot remain.

I assume you wish to detect the condition that child references a deleted record, and treat this as an error.

Answer 2

I think the comments on the question itself are quite pertinent. The question, at this point, is vague.

But, assuming that the child table also has a IsRemoved field - after all what would be the point of the child records remaining available if the master record is marked as removed? - why don't you implement a trigger on Master that, if IsRemoved is changed, also changes the IsRemoved flag on the Child?

This way the need to check the status of the master on the child is completely eliminated as they will be in sync as it pertains to the active or inactive status.

Answer 3

From experience I have to tell you this design is horrible to work with. Consider changing the design to copy the data to an 'audit trail' table then physically remove it from the main table.

If you won't consider this, at the very least bury this in a VIEW and do everything you can to avoid exposing this to anyone wanting to query the database, using INSTEAD OF triggers on the VIEW if necessary. Otherwise, expect applications to have frequent bugs because someone forgot to add the AND isremoved = 0 predicate required by every query that uses this table.

But this 'answer' doesn't address the real question.

Yes. Sorry 'bout that. But sometimes you have to cure the disease rather than merely treat the symptoms.

The design is compromised: a table should model a single entity type, whereas this is modelling two. How can I tell? Because the OP has stated that once 'removed' the entity has different data requirement, by saying "The problem is ... how can we check that all its child table not referring this record".

So the 'real' answer is: move the entity to another distinct table.

But if you are in the business of treating symptoms then here's an answer:

1. Add the IsRemoved column to your so-called 'child' tables, with DEFAULT false and ensure it is NOT NULL.
2. Add a CHECK constraint to each so-called 'child' table to test isremoved = false (or whatever 'boolean' means in your SQL product).
3. Add a compound key (e.g. using UNIQUE or PRIMARY KEY) to your so-called 'master' table on (IsRemoved, <existing key columns here>), or alter an existing key accordingly.
4. Add FOREIGN KEY constraints to each so-called 'child' table to reference the compound key created above.

Answer 4

You can find the referenced table of master data table if we have put proper foreign key constraint in all the child tables using following query:

SELECT uc.table_name MAIN_TABLE_NAME, ucc.column_name MAIN_TABLE_COLUMN_NAME, ucc_ref.TABLE_NAME AS REFERENCED_TABLE_NAME, ucc_ref.COLUMN_NAME AS REFERENCED_COLUMN_NAME, ucc_ref.position FROM USER_CONSTRAINTS uc, USER_CONS_COLUMNS ucc_ref, USER_CONS_COLUMNS ucc WHERE uc.CONSTRAINT_TYPE = 'R' AND uc.R_CONSTRAINT_NAME = ucc_ref.CONSTRAINT_NAME and ucc.Constraint_Name = uc.constraint_name and ucc.table_name = uc.table_name and ucc_ref.position = ucc.position and uc.table_name = ? order by ucc_ref.TABLE_NAME,ucc_ref.column_name

This query works in oracle. I am not sure about other databases.

Once you have find out the referenced table, you need to find whether the master data record you are trying to delete exists in the referenced table or not. If active record exists in the child table, you can throw an exception. The key thing here is that finding the referenced record alone is not suffient. We might have cases where we find the master record reference in the child table but child record has also been cancelled. We have written an util for the delete pre check of master data using above concept.