Help with SQL; detect deletes that are prevented due to reference constraint

You know how using Studio manager if you try to delete a record and it fails due to a reference constraint, you get a message like:

The DELETE statement conflicted with the REFERENCE constraint "FK_tds_efile_classify_log_tds_admin_users". The conflict occurred in database "tdsdev", table "dbo.tds_efile_classify_log", column 'FK_user_id'.

Is there a command I can issue to check for this condition, and if the condition exists, it give the same message, but if the condition does not exist, don't do the delete?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
Perhaps a better idea would be to create a Stored Procedure that has the deleted row's Primary Key as a parameter, and have it check.  Then have your front-end execute the SP with the parameter.  Something like this..

CREATE PROC your_proc (@FK_user_id int) AS

-- Witty code commentary goes here

IF NOT EXISTS( SELECT FK_user_id FROM tds_efile_classify_log WHERE FK_user_id = @FK_user_id) 
  -- Your DELETE statement goes here
  SELECT 'Delete successful message' 
  SELECT 'No can do Hoss... '


Open in new window

HLRosenbergerAuthor Commented:
I guess I'm not following, or did not ask the question correctly.  here's my situation - I have a table of users that has a user_id primary key column.   A certain set of users are now gonna have new user_ids.   The user table is related to a number of other tables in our system.  For those users with the new userid, I need to change the Foreign Key userid in all these other tables.  I need a way to detect all these changes.  Know what I mean?  Or a way to update the Foreign Key userid in all other tables, one user at a time.
Jim HornMicrosoft SQL Server Data DudeCommented:
Explain in great detail with some sample data 'I need to change the Foreign Key userid in all these other tables. ', as my initial reaction is changing foreign key relationships on the fly is not a good road to go down.  Better would be to create foreign key constraints with a second column to guarantee uniqueness.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

HLRosenbergerAuthor Commented:
I totally agree.  All my tables use an identity column for the Primary key - except one, the user table.  In this table the PK is the user ID and it's not an identity column - the user_id value is determined by an external Human Resources third party system.   Is it what it is and it cannot be changed, so that's not an issue here.

So, to give one example, there's also an incident report (IR) table. It has a foreign key that relates it to the user_id table, a FK_create_userid - the user_ID of the person that created the IR.   Suppose I've created multiple IRs and my user_id is 1234.   Now, my new user_id 9999.   So after adding the new user (actually the same user, but different user_id) to the user table, I need to change every occurrence of 1234 to 9999 in the IR table for the FK_create_userid column.  And I don't want to do this manually, since this issue exists for about 30 users, and and across multiple tables, not just the IR table.

Do this make sense?
Jose TorresCertified Database AdministratorCommented:
Two things to consider hard referential integrity which you can identify by reviewing the foreign keys and soft referential integrity which do not have any foreign keys created.
So before looking at a solution you should identify all objects associated with the user_id.
Once you have that you can device a strategy to change and test in non-prod environment.
Just off the top of my head I would drop the foreign keys, drop the primary key.
Update the user_id in the parent table update the user_id in all the other tables, re-create the primary key and foreign keys.
But I would only do that if it's a one shot deal.
HLRosenbergerAuthor Commented:
I want to "identify all objects associated with the user_id."  Agreed.  That's what i need help with.    There must be a way to list all reference constraints for a column across the database based on all tables.

If I try to do a delete of a user record, it tells me a reference constraint, the first one it hits.  if I were to "fix" that reference constraint, and try the delete again, it would now give the next reference constraint it hits, provided there is one.  etc, etc.   I'm trying to determine all these reference constraints, or as you say "identify all objects associated with the user_id" .
HLRosenbergerAuthor Commented:
Ah, this gives me all the referenced tables/columns in the database, for the specified table,.

   OBJECT_NAME(f.parent_object_id) TableName,
   COL_NAME(fc.parent_object_id,fc.parent_column_id) ColName
   sys.foreign_keys AS f
   sys.foreign_key_columns AS fc
      ON f.OBJECT_ID = fc.constraint_object_id
   sys.tables t
      ON t.OBJECT_ID = fc.referenced_object_id
   OBJECT_NAME (f.referenced_object_id) = 'YourTableName'
Scott PletcherSenior DBACommented:
Specify those FKs with "ON UPDATE CASCADE".  Then when you UPDATE the parent table, SQL will automatically cascade it to related tables for you.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HLRosenbergerAuthor Commented:
Scott -

Ah, so I could add "ON UPDATE CASCADE" to all those foreign keys now, then when I change the PK data in the parent user table,  it propagates to all the related table FKs?
Scott PletcherSenior DBACommented:
Yes.  If the key in the parent table was 1234, and you changed it to 9999, SQL would change all explicit FKs (those you defined in DDL with "FOREIGN KEY ... REFERENCES ...") in child tables from 1234 to 9999.  "NO ACTION" is the default, meaning SQL basically ignores the change, but "CASCADE" is a valid option too.
HLRosenbergerAuthor Commented:
Scott - that works great.  learn something new every day..
HLRosenbergerAuthor Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.