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

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.

---

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.

---

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.

---

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.

---

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  d 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 found 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 the 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 sufficient. We might have cases where we find the master record reference in the child table, but the child record has also been cancelled. We have written an util for the delete pre check of master data using above concept.

---

USING THIS LOGIC:

SELECT t1.*
FROM Table1 AS t1
WHERE NOT EXISTS
      ( SELECT *
        FROM Table2 AS t2
        WHERE t2.FKcolumn = t1.PKcolumn
          AND t2.columnX IS NULL
      )