2

I want to know is there any way to know whether a record is in use with other records.

Is there any way to find it other than, by catching referential integrity exception?

I generally catch this exception and tell users no to delete or unable to delete the record.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459

2 Answers2

2

You can run a query against related tables.

Jakub Konecki
  • 45,581
  • 7
  • 87
  • 126
  • What If my records has been spreaded into 10-15 tables. I dont want to write it against each and every table. I want some generic way to identify –  Dec 27 '12 at 16:29
  • You can add a new column that will store the precalculated value if the record is referenced from other tables. You can updated the value of this column when the references are updated, so you don't have to run a heavy query on every read. – Jakub Konecki Dec 27 '12 at 16:40
  • You mean to say add a column like IsInUse to track its usage. But again the question arises for its backtracking. In that case on evey delete of the record in the referenced table i have to backtrack the each and every record in that deleting record,Which is again an overkill. –  Dec 27 '12 at 16:49
  • 2
    It's not an overkill, it's your requirement. You want to know if the record is referenced from other table without relying on the exception. I give you the answer: query other tables. If you don't want to pay the price of querying multiple times, than cache the result in a separate column. You can store it as a number and just increment it when the relationship is added and decrement when it is removed. If the value is 0 than you know that the record is not in any relationship. – Jakub Konecki Dec 27 '12 at 16:53
  • What about the System Tables where foreign keys and constraints are stored. I dont the table names nor the SPs that can used to access the data. But an SQL DBA will know – phil soady Dec 27 '12 at 17:54
  • @soadyp - System Tables will store the definition of the relationship. You will still have to execute the actual query yourself. – Jakub Konecki Dec 27 '12 at 18:00
  • correct Jakub, btw i was the +1 above...I agree with you. I was mentioning an alternative the question asker might have beyond your suggestion – phil soady Dec 27 '12 at 18:58
0

Create a stored procedure that uses the FKs in the system tables to build a custom view for each table. In each view, query all the related tables. Run the SP every time you add or remove an FK so that it updates all the views appropriately.

Problem solved.

ErikE
  • 48,881
  • 23
  • 151
  • 196