Check if Foreign Key Constraint is violated? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Check if Foreign Key Constraint is violated?

Author

Topic

reggiepangilinan
Starting Member

2 Posts

Posted - 2008-12-10 : 22:54:51

hello guys, is there a way to check a violated foreign key constraint

prior to the execution of the delete statement... here is my example..

(Table1)


PK_Table1    Name
1            Sample1
2            Sample2
3            Sample3
4            Sample4

(Table2)


PK_Table2    FK_Table1    Name
1            1            Sample1
2            1            Sample2
3            1            Sample3
4            1            Sample4

lets say i want to execute a delete statement

delete from table1 where PK_Table1 = 1

after executing this statement it will return an error because the FK

constraint was violated. now my question is, is there a way to check

that a FK constraint will be violated before the execution of the

delete statement?

thank you.

nathans
Aged Yak Warrior

938 Posts

Posted - 2008-12-11 : 02:12:08

No way other than to inspect the relationships yourself (existence check in the FK tables prior to issuing del).

Is the ultimate goal to successfully process the delete? Or is it to not issue the delete at all if it would throw a fk violation? If the former, look into cascade deletes, if later then do the existence check:

 if exists(select 1 from table2 where PK_Table1 = 1) 
begin
print 'fk violation'
end else
begin
print 'no violation'
end

Nathan Skerl

nathans
Aged Yak Warrior

938 Posts

Posted - 2008-12-11 : 02:15:35

You could also leverage Try Catch blocks to issue the delete and then deal with the FK violation in the catch. Its hard to offer solutions without knowing the ultimate goal here.

Nathan Skerl

reggiepangilinan
Starting Member

2 Posts

Posted - 2008-12-11 : 21:51:49

thank you for your answer nathan, well the goal is to successfully delete the record of course, and if the foreign key constraint will be violated, inform the user that the record that is about to be deleted is being referenced by other records.

cascade delete, i think is not a good solution. because it will delete all the records that is referenced to the record that will be deleted. users might end up wondering where the other records are. you know how users are... demanding and everything. They don't really care about this FK constraint and other technical stuff..

anyway. i think i will be using the other solution checking the existence in the FK tables.

thanks again man! :)

nathans
Aged Yak Warrior

938 Posts

Posted - 2008-12-11 : 23:29:51

Yup, I think youre on the right track.


if exists(select 1 from table2 where ...)
begin
--raise your custom return code or raiserror so app can show user msg
    set @ReturnCode = -1
    -- or
    raiserror('You cannot delete this record.', 16, 1)
end else
begin
    delete from table1 where ...
end

Nathan Skerl

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources