Results 1 to 12 of 12

Thread: Finding orphaned records

  1. #1
    Join Date
    Mar 2004
    Posts
    9

    Finding orphaned records

    I am trying to find records that are orphaned in a data base after thier parent record (in another table) has been deleted.

    Something happened to the constraints and I had no idea until now this had happened.

    I have the faint rustle of a query in query sort of thing but my knowledge needs expansion.

    PS: sorry for the cross post - just noticed that I was in the wrong forum before.

    Thanks,
    Matt

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    select * from childtable
    where fk_column not in (select pk_column from parenttable)

  3. #3
    Join Date
    Mar 2004
    Posts
    9
    thanks for the quick reply.

    I tried the following:

    select sArticleID from tblarticle
    where sArticleID NOT IN (select sArticleID from tblprime)

    but no result.

    sArticleID id the PK in tblarticle
    sArticleID id the FK in tblprime

    What am I doing wrong here?

    Thanks Matt

  4. #4
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    select sArticleID from tblprime
    where sArticleID NOT IN (select sArticleID from tblarticle)

  5. #5
    Join Date
    Mar 2004
    Posts
    9
    Thanks for the reply skhanal ,
    Had to sleep on the problem and am just now back at the desk.

    The last format works but is not the right way around for this problem.

    I need to find the orphan data in tblarticle not in tblprime so the keys are reversed - no FK in tblarticle.

    Tried truning round the code but that just didn't do it.

    At least it is forcing me to research SQL again.

  6. #6
    Join Date
    Mar 2004
    Posts
    9
    Thanks skhanal,

    I recieved the solution in another forum (a mistaken cross post.

    http://forums.databasejournal.com/sh...7406#post87406

    using a right outer join and looking for a null value on the common column worked.

    Thanks for the posts.

    Matt

  7. #7
    Join Date
    Mar 2004
    Posts
    12
    You might also try:

    select Dependent.* from Dependent
    where not exists (select * from Parent where Parent.KEYCOLUMN = Dependent.REFERINGCOLUMN)

  8. #8
    Join Date
    Mar 2004
    Posts
    9
    Thanks Peter,

    Works a treat.

    And my next question for the panel:
    What is the more correct form - outer join or the latest solution?

    Thanks Matt

  9. #9
    Join Date
    Mar 2004
    Posts
    12
    I think mine is preferred because it avoids a potential problem where the is null test will cause an error with columns declared as not null which is typically the case for Foreign Keys.
    Just because the fk gets dropped doesnt mean the not null will go away.

    My way expects that the relationship is truely a FK relationship in the dependent table column.

    However, if some orphans are permissible then the other way should be preferred because it indicates that the fk relation isn't entirely true and the child row has a weaker relation to the parent row.

  10. #10
    Join Date
    Mar 2003
    Location
    NJ
    Posts
    201
    The technique that I posted is not bound to referencial integrity. You could use this way in any situation.

    But most likely the not exist technique is recommanded.

  11. #11
    Join Date
    Mar 2004
    Posts
    9
    Many thanks for the help and advise.

    I can see differences in the approaches more clearly now. Thank you both for shining a little light on this for me.

    The more I learn the more I discover there is still yet to learn.

    Thanks
    Matt

  12. #12
    Join Date
    Mar 2004
    Posts
    8

    Dup Records

    During Db setup sometimes I forget to add my key fields , howerver I can find duplicates in a single table using the following:
    SELECT Field1, Field2, Field3, Field4, RecordID
    FROM YourTable
    WHERE (RecordID IN
    (SELECT [RecordID]
    FROM [YourTable] AS Tmp
    GROUP BY [RecordID]
    HAVING COUNT(*) > 1)) AND (RecordID <> 0)
    ORDER BY RecordID

    With some modification you can utilize this to search for orphand records.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •