using foreign key constraints

Question

using foreign key constraints

minerj

SSC Journeyman

Points: 87
More actions
March 20, 2002 at 1:48 pm

#78177

I would like to use foreign key constraints to ensure referential integrity in my SQLServer database, but I don't see how I can easily generate a user-friendly error message in my front-end application when an ADO error occurs due to violating one of the foreign key constraints. Any suggestions greatly appreciated.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

crosspatch Hall of Fame Points: 3168 More actions · Answer 1

OK The trick is in capturing the error and doing something with it. To do this I use a small footprint object to handle all database access. This has two methods 1 to execute any non-returning SQL (call to an SP, update, insert, delete queries etc.) The other returns an ADO recordset. Both methods take a ByRef blank string parameter. My error handler in the methods populates this string with the error number and error message which can then be picked up by the calling app.

Sooo the ADO error doesn't cause the application to balk (cos the error is handled by the called object) It gets back a nice string telling you all about the error and you can then use your creative writing skils to translate that into something meaningful for the user.

I've used this technique in VB, DELPHI and ASP applications. If you like you can get the source of the VB object from my web site http://www.reallyneat.co.uk choose samples, vb, Generic ado. (I'm building the site at the mo' so I apologise for an X-Files effects you may suffer

I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961

minerj SSC Journeyman Points: 87 More actions · Answer 2

Thanks for the detailed reply. A further question: Suppose I have a delete that can violate several foreign key constraints in different child tables, can I easily determine from the errors passed back which constraint I am violating? Thanks.

crosspatch Hall of Fame Points: 3168 More actions · Answer 3

Don't rightly know is the answer. It'll depend on how detailed SQLSERVER is when returning the error to ADO. To be honest with you I tend to avoid cascading deletes if I can just for that *** reason 'which *** constraint are you violating?"

It's more tacky but I do each delete individually whithin the same transaction, easier to track which one's blown. But performance is an issue

I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961

minerj SSC Journeyman Points: 87 More actions · Answer 4

Ok, this jives with the little research I have done. Some other databases allow you to define custom error messages with each foreign key constraint but this doesn't appear to be the case with SQLServer 7. I have inherited a horrible application where I'm guessing the perpetrators couldn't spell "referential integrity" let alone implement it. I was hoping the fastest way to apply it would be by defining foreign key constraints on the database (i.e. I don't have to wade into the cesspool of code), but I hate simply passing back barely understandable messages back to a user.

Thanks for the insight.