Database constraints | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Database constraints

Hi i would like a suggestion from all the experts in this forum as i’am involved in design of a software package which is going to be VB6.0 as front end and SQL2005 as backend…..
my question here is most of the constraints like foreign key,unique,check etc do i need to put in the sql server and increase the hardware of the server or is it better to put as a class in front end and make the client thin….
with my recent experience i had observed that a record in a master table takes 6-7 minutes to delete as it has foreign key relation with 9 other tables…..
kindly put on ur opinions based upon your application experience…
Thanking in advance Mat
In my opinion you should put such constraints where they belong. That is in the database. Those constraints are there to enforce the integrity of your data. If you put them in the front-end and someone bypasses that front-end, this integrity is at stake as your database is door-wide open for garbage data. As for your recent experience. Proper deletion of a single row should happen almost instantly. Even when there are foreign keys in the game. Without further information this very long time might be due to blocking. —
Frank Kalis
Microsoft SQL Server MVP
– I second Frank’s opinion
– Also start off with putting FK-indexes on the FK-columns of your child tables (exact column order please) This supports as well joins as deletes, …