Do FK Constraints do full table scan on delete?

[gdrenfrew]

Hello,


Do constraints do a full table scan of the referencing table before it allows the deletion of a record from the referenced table?

Am trying to decide if this is a worthwhile overhead, or if I'd be better sticking with stored procedures/triggers which check if records exist in the referencing table by doing SELECT count(*) from T_REFERENCING where id = OLD.ID before allowing users to delete records via frontend.

Thanks
Graeme

 

[dmhirsch]

A foreign key is in fact a particular type of index, so no table scan is necessary. They are very low overhead (per documentation) compared to triggers. But if it is your goal to prevent deletions on the front end, you will probably want to use your select in order to find out ahead of time, rather than just letting the delete fail and deal with the error.

 

[gdrenfrew]

Hi, thanks for your response.

I'm in the middle of experimenting with the best way to handle the error (537) caused by a constraint violation.

Prior to implementing foreign key constraints, we avoided deleting parents and the resultant child orphans via stored procedures before it happened, which I thought worked fine (and still does, we just want to make things totally robust).

I take it from your experience you think that's a better way to do things (stored procs), rather than handling the failed deletion ex post facto. (Would also save me from rewriting system wide error-handling class)

Thanks

 

[dmhirsch]

Actually, I don't use a lot of stored procedures, except for complicated updates. Instead, I create the "delete" functionality in the business logic tier, which sequences cascading deletes as necessary to avoid constraint errors. My goal is to have SQL Server generate no errors at all - any error should be an unanticipated exception.

 

[gdrenfrew]

I see. Our goal was also to have SQL Server generate no errors, so we didn't have FK constraints. And as usual we're right at the end of the development cycle.

However, customer is nervous that the data isn't as solid as it might be (admittedly caused mostly by balls-ups by us at the front-end), so to put their mind at rest we're trying to implement database integrity with FKs. For the most part we already let our stored procs and business objects take care of that.

Hopefully this extra level of integrity protection won't cause too many headaches (man, just as i typed that I had a preminition of impending doom).

(FYI, the database design wasn't pinned down at the start because they refused(!) to give us a spec for over a year and we just muddled along as best we could. bonkers).

thanks for your advice
graeme