Foreign Key question 1

[indrahig]

Hi,
Is there a way to find out that a record has been used as foreign key in other tables (without using SELECT syntax)?

example:
- TableA
AId AName
------ ---------
001 Anonymous1
002 Anonymous2

- TableB
BId BName AId
------ --------- ------
111 AnyName 001

Column AId in TableB is a foreign key to Table A.
I want to find out that AId 001 has been used in TableB,
but without using SELECT statement.

any help will be appreciated.

 

[DotNetGnat]

This below link has a query to find all the foreign key relationships...

http://www.aspfaq.com/show.asp?id=2520

but you said you did not wanted in a select statement...but hope it helps someone else...

-DNG

 

[indrahig]

haha, thanks DNG.
Here is what I am trying to do exactly.
I am trying to delete one record in TableA (AId = 001),
but It has been used as foreign key in other tables (more than one table).

If the record has been used in other tables, the record is not supposed to be deleted.
And the application (I developed) should show message: "Cannot delete record, It has been used" (or something like that).

I can check all the tables that has relationship with TableA with "select" statement or "if exists" statement, to make sure the record has not been used and ok to delete it.

If there are 2 or 3 tables, then it's ok to do that, but I have more than 20 tables that have relationship with TableA.

So, I want to know, is there any ways to checked that record has been used or not on other tables without using "select" statement against each tables.

 

[jbenson001]

The only other way I see that you can do that is to try to delete the row in the parent table. If that key exists as a FK in any other table, you will get a Foerign Key Constraint error. You can then trap that error and display your message.

Jim

 

[indrahig]

thanks Jim,
Yes, that is what I am doing now.
here is the code:

begin tran
delete from TableA where AId = '001'
rollback tran

It will generate error if the record has been used (and the application trap it and display message).
But I don't think this is a good solution, considering the locking that will occur because of the transaction.

thanks anyway.

 

[jbenson001]

That is the only way i can think to do what you want without using separate select statments. Good point about the locking. I would say to creat a stored procedure to query each table, Select @var = count(*) from tableA, @var = @var + count(*) from tableB etc.... Then return the final rowcount, IF > 0 then generate the error. I know it is using selects that you don't want to use, however it does avoid the locking issue.

 

[indrahig]

thanks Jim/jbenson001,
I guess by selecting each table is the only solution I can afford right now.
And by creating stored procedure making it more effective and the maintenance will be a lot easier (if more tables added to relationship).
I keep wondering why I haven't think about it.
A star for you.

 

[jbenson001]

Thanks for the star and glad I could help
Jim