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??

Cheers,
ShaunI 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.|||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.|||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.|||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|||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.