Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Christchurch, New Zealand

    Unanswered: Referential Integrity

    Hi guys,

    Is there a way of finding the foreign keys for a table and therefore determine weather any referential integrity rules would be broken if a record was deleted?

    For example. You have an author you want to delete, but that author has books. You call a procedure to delete the author. The stored procedure checks the foriegn keys, checks the tables and determines what is in them and if any tables have records that would be "orphaned" you return an error reporting what tables have the "would be orphaned" data in them.

    It need to be fairly generic int he manner that it checks the foreign keys and the sub table data.

    This is on behalf of a guy at work so "requirements" may change.

    Anyone got some ideas??


  2. #2
    Join Date
    Aug 2003
    Delft, The Netherlands (EU)
    I see three options:

    1) Try / Error approach: just delete, catch eventual error and handle it
    2) In a SQL Server using ADo: Use OpenSchema to retrieve foreign key constraints
    3) query SQL Server's schema tables

    If these hints are not sufficient, I'll eloborate on your preferred option.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  3. #3
    Join Date
    Feb 2004
    There's always the "deleted item" option. Instead of removing it just mark it as deleted and then filter out deleted items in your search results. I used to think that was a lazy way of doing it, but now I kind of think it's helpful when dealing with product databases. Unless of course you expect to be removing many items over a short period of time.
    I never met an android I didn't like to take apart and sell as scrap

  4. #4
    Join Date
    Jul 2003
    The Dark Planet
    select object_name(fkeyid) from sysforeignkeys where rkeyid = object_id(tablename)
    This will give you the table names which have a foreign key constraint on the table.
    Last edited by Enigma; 10-28-04 at 00:04.
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  5. #5
    Join Date
    Apr 2002
    Toronto, Canada
    what about the referential integrity option

    the only reason i can think of why you'd want to select first before deleting the author, is if you had failed to properly implement referential integrity and were facing the prospect of perhaps leaving orphaned books around

    with referential integrity declared, here's what you do

    1. delete author

    if this is successful, there were no books that would've been "orphaned"

    if the delete is unsuccessful, there were, so then issue your error message
    Last edited by r937; 10-28-04 at 00:04. | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Nov 2003
    Christchurch, New Zealand
    thanks for all the suggestions guys. much appreciated. look like the same sort of stuff I was suggesting to the guy.

    the reason he wants to do this is because he is expecting stupid users who won't understand what they are deleting by deleting a author (following the example).

    I think he is intending to go with a deleted flag (soft delete) at this point.

Posting Permissions

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