2

Related to this question: Remove rows NOT referenced by a foreign key

But I don't want to delete just yet, and I am not sure I understand the accepted answer there.

I have a table with many rows. Some of them are duplicate (yes I am going to fix the problem generating the duplicates). Now, before removing any duplicates (all is duplicate EXCEPT it's id!), I'd like to make sure the rows are not referenced by another table, in order to be sure I can delete them.

COL_1  COL_2   COL_3    ID
abc    cde     fgh      1
abc    cde     fgh      2
abc    cde     fgh      3

I want to see if I can delete row 2 and 3, and see if those IDs are referenced somewhere... Is that even possible?

Community
  • 1
  • 1
transient_loop
  • 5,984
  • 15
  • 58
  • 117
  • 1
    Only if there's a foreign key relationship actually defined on them within the database. If no foreign keys, you could check unique indexes to see if the ID columns is in the index. If it is, then you could check system tables for columns that make reference to the column, or use system tables to check all code objects to see if the field 'ID' is referenced. In other words, without the relationship defined... Good luck as you'll have A LOT of work ahead of you. Your database may have a view like 'ALL_DEPENDENCIES' (oralce) which may help identify. some relationships.. all heavy work. – xQbert Jun 20 '14 at 20:34
  • Thanks...sounds like a lot of MANUAL work...Yes there are foreign keys, and I'd look only for references through them. – transient_loop Jun 21 '14 at 17:14
  • If you have foreign key constraints, and they're defined with `ON DELETE NO ACTION` (which is the default), then the database will do this check for you. – Nick Barnes Jun 21 '14 at 23:41

0 Answers0