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.
| Author |
Topic |
|
dkekesi
Starting Member
38 Posts |
 Posted - 2011-04-04 : 13:47:20
|
| Hello Experts,On the GUI front-end of my application users are allowed to delete rows from tables. These tables have FK constraints in place to keep the data structure consistent. If a delete operation is attempted on a data row I do not want to permit deletion if the data is referenced in another table. I could catch the exception but in .NET all I have is SQLException which can refer to many things and it is not a nice way (actually, there's an error code for FK exceptions but that is DB engine-specific).A SP that checks if the data (identified by a PK) is referenced somewhere else could be lot nicer. While I can easily create a SP that will work for a specific table and FK setup, but I have a lot of tables (176 as of this moment, to be exact) and the number is growing. I also have many FKs and they're also growing in numbers. And I really do not want to keep track of them (especially since I'm not the only one handling these problems).What would be really nice is to have a SP that accepts two parameters: 1. table name as string, 2. ID as integer that represents the PK in the identity column named "ID". What the SP should do is look through all tables that use the PK (parameter 2) of the table (parameter 1) and returns true if the data is used somewhere else and returns false is the data is not used in any table.I know I have to use the system tables, but I am not familiar with their connections.Can anyone guide me in the right direction?Thanks a lot and sorry for the lengthy request.Best Regards,Daniel |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
dkekesi
Starting Member
38 Posts |
Posted - 2011-04-05 : 03:37:01
|
Thanks for your help, Tara. I'd better take your advice if you're indeed the Almighty SQL Goddess  Best Regards,Daniel |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-04-05 : 13:09:23
|
quote:
Well I'm no expert
What a crock!  quote:
but I'm pretty sure the experts in the field would agree with me on this one.
If we didn't, we'd awaken your SQL Goddess anger, and who knows what havoc would entail... |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2011-04-05 : 13:25:06
|
| Tara is very humble she is Almighty SQL Goddess!!If you don't have the passion to help people, you have no passion |
|
|
|
|
|
|
|
|
General Foreign Key constraint checking