Philosopical debate about constraints
A design/philosophy question rather than a bug: if all database access goes through stored procedures that already do their own sanity checks, are declarative constraints just wasted overhead? Replies were near-unanimous in favour of declaring constraints in the engine: hand-coded checks get duplicated across procedures and drift as code changes, DBAs/other tools or future applications can bypass the procedures (orphaned rows are common), engine checks on unique/primary keys are typically faster and lock less, and constraints can help the optimiser. A couple of posters allowed that hand checks may win for very simple, stable models, and triggers or CHECK constraints were suggested for rules SQL can't express declaratively. No single "answer" was accepted, but the thread's consensus was to use NOT NULL, UNIQUE and FOREIGN KEY constraints and reserve procedure code for error handling and exotic rules.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Assuming: 1. All application access to my database is via stored procedures. 2. My stored procedures can be relied upon to do sanity checks regarding relationships between tables then is it really necessary to define constraints? Isn't that just an unnecessary overhead and just duplicating the work of my sanity checks? Your thoughts would be appreciated.
Makes sense to me. However, two issues come up: 1. Aren't you then duplicating in applications code something that is a. Already built into the database. b. Something the database engine (presumably) does more efficiently. 2. Relying on applications programers to insure data integrity. Good intentions aside, something will be forgotten over time. Imagine the person who goes into the code 3 years from now (who was hired 1 year ago) and forgets something. I could see this method's reliability inversely proportional to the complexity of the data model. Just my 2 cents. -- Barry Speaking only for myself, to do otherwise would be presumptuous. David Henry wrote: > Assuming: > 1. All application access to my database is via stored procedures. > 2. My stored procedures can be relied upon to do sanity checks regarding > relationships between tables > > then is it really necessary to define constraints? Isn't that just an > unnecessary overhead and just duplicating the work of my sanity checks? > > Your thoughts would be appreciated.
Makes sense to me. However, two issues come up: 1. Aren't you then duplicating in applications code something that is a. Already built into the database. b. Something the database engine (presumably) does more efficiently. 2. Relying on applications programers to insure data integrity. Good intentions aside, something will be forgotten over time. Imagine the person who goes into the code 3 years from now (who was hired 1 year ago) and forgets something. I could see this method's reliability inversely proportional to the complexity of the data model. Just my 2 cents. -- Barry Speaking only for myself, to do otherwise would be presumptuous. David Henry wrote: > Assuming: > 1. All application access to my database is via stored procedures. > 2. My stored procedures can be relied upon to do sanity checks regarding > relationships between tables > > then is it really necessary to define constraints? Isn't that just an > unnecessary overhead and just duplicating the work of my sanity checks? > > Your thoughts would be appreciated.
David, IMHO, IF the application is coded correctly AND your not concerned about someone who has update authority (like a DBA) changing your data outside the App. THEN I would have to say that referential constraints are a waste of time, disk and CPU, which boils down to wasted $money$. David Henry wrote: > > Assuming: > 1. All application access to my database is via stored procedures. > 2. My stored procedures can be relied upon to do sanity checks regarding > relationships between tables > > then is it really necessary to define constraints? Isn't that just an > unnecessary overhead and just duplicating the work of my sanity checks? > > Your thoughts would be appreciated.
David, IMHO, IF the application is coded correctly AND your not concerned about someone who has update authority (like a DBA) changing your data outside the App. THEN I would have to say that referential constraints are a waste of time, disk and CPU, which boils down to wasted $money$. David Henry wrote: > > Assuming: > 1. All application access to my database is via stored procedures. > 2. My stored procedures can be relied upon to do sanity checks regarding > relationships between tables > > then is it really necessary to define constraints? Isn't that just an > unnecessary overhead and just duplicating the work of my sanity checks? > > Your thoughts would be appreciated.
My experience has been: "If there is any possible for data to become corrupt, then it will be". I suppose this is just a special case of Murphy's law. I am a database consultant, so I have been exposed to a lot of databases. I can say without reservation that in *every* case where constraints are are not enforced at the engine level, I have found corrupt data. One of the most common problems is orphaned child rows. Application code (or stored procedures) will limit the user's choice on insert so that the database starts out with good integrity, but later someone comes along and deletes a parent row, or tries to "refresh" a parent table but uses the wrong source, and since there aren't any foreign keys, data gets orphaned. The speed with which database engines check foreign keys is really pretty darn fast, because the parent row for a foreign key must be unique (I never point foreign key to anything but the primary key, but some engines let you point to any unique key), so the parent is uniquily indexed and very easy to search. I don't see how either stored procedure or application code could perform the integrity check more quickly. The other good thing about foreign key constraints is that they stay in place even if you have to make significant changes to your stored procedures. Most business rules that can be represented are quite stable, so I think it makes sense to use foreign keys instead of writing code. Thanks, Bill David Henry <henryd@net-gong.com> wrote in message news:809aae$r39$1@news2.inter.net.il... > Assuming: > 1. All application access to my database is via stored procedures. > 2. My stored procedures can be relied upon to do sanity checks regarding > relationships between tables > > then is it really necessary to define constraints? Isn't that just an > unnecessary overhead and just duplicating the work of my sanity checks? > > Your thoughts would be appreciated. > > >
David Henry <henryd@net-gong.com> wrote in message news:809aae$r39$1@news2.inter.net.il... > Assuming: > 1. All application access to my database is via stored procedures. > 2. My stored procedures can be relied upon to do sanity checks regarding > relationships between tables This can be a very good idea and it can also be a very very bad idea. In my experience my own procedures sometimes check constraints more efficiently than the DBMS because I know exactly what needs to be checked. Exceptions usually are the key constraints and referential integrity constraints. The downside is that everything then depends upon my own programming skills and my own insight into the complexities of the constraints and how they can be violated by certain updates. This can get even a bigger problem if I am not the only one writing the stored procedures. The biggest problems occur when constraints are added, removed or changed afterwards. Since your constraints will be implicitly coded into your stored produres it will be difficult to find all the parts that need to be changed. Keep in mind that one constraint may lead to several checks in several stored procedures. So good documentation of your constraints and how they are implemented in your procedures will be of the utmost importance. And to write the new code you will probably have to familiarize yourself again with all the constraints to see if they don't interact in any funny way. So, my advice would be: don't do it unless you have only a few relatively simple constraints, are a very good programmer, a very good logician, and if you are sure that your constraints will not change in the near future. Kind regards, -- Jan Hidders
David Henry <henryd@net-gong.com> wrote in message news:809aae$r39$1@news2.inter.net.il... > Assuming: > 1. All application access to my database is via stored procedures. > 2. My stored procedures can be relied upon to do sanity checks regarding > relationships between tables This can be a very good idea and it can also be a very very bad idea. In my experience my own procedures sometimes check constraints more efficiently than the DBMS because I know exactly what needs to be checked. Exceptions usually are the key constraints and referential integrity constraints. The downside is that everything then depends upon my own programming skills and my own insight into the complexities of the constraints and how they can be violated by certain updates. This can get even a bigger problem if I am not the only one writing the stored procedures. The biggest problems occur when constraints are added, removed or changed afterwards. Since your constraints will be implicitly coded into your stored produres it will be difficult to find all the parts that need to be changed. Keep in mind that one constraint may lead to several checks in several stored procedures. So good documentation of your constraints and how they are implemented in your procedures will be of the utmost importance. And to write the new code you will probably have to familiarize yourself again with all the constraints to see if they don't interact in any funny way. So, my advice would be: don't do it unless you have only a few relatively simple constraints, are a very good programmer, a very good logician, and if you are sure that your constraints will not change in the near future. Kind regards, -- Jan Hidders
David Henry <henryd@net-gong.com> wrote in message news:809aae$r39$1@news2.inter.net.il... > Assuming: > 1. All application access to my database is via stored procedures. > 2. My stored procedures can be relied upon to do sanity checks regarding > relationships between tables This can be a very good idea and it can also be a very very bad idea. In my experience my own procedures sometimes check constraints more efficiently than the DBMS because I know exactly what needs to be checked. Exceptions usually are the key constraints and referential integrity constraints. The downside is that everything then depends upon my own programming skills and my own insight into the complexities of the constraints and how they can be violated by certain updates. This can get even a bigger problem if I am not the only one writing the stored procedures. The biggest problems occur when constraints are added, removed or changed afterwards. Since your constraints will be implicitly coded into your stored produres it will be difficult to find all the parts that need to be changed. Keep in mind that one constraint may lead to several checks in several stored procedures. So good documentation of your constraints and how they are implemented in your procedures will be of the utmost importance. And to write the new code you will probably have to familiarize yourself again with all the constraints to see if they don't interact in any funny way. So, my advice would be: don't do it unless you have only a few relatively simple constraints, are a very good programmer, a very good logician, and if you are sure that your constraints will not change in the near future. Kind regards, -- Jan Hidders
David Henry <henryd@net-gong.com> wrote in message news:809aae$r39$1@news2.inter.net.il... > Assuming: > 1. All application access to my database is via stored procedures. > 2. My stored procedures can be relied upon to do sanity checks regarding > relationships between tables This can be a very good idea and it can also be a very very bad idea. In my experience my own procedures sometimes check constraints more efficiently than the DBMS because I know exactly what needs to be checked. Exceptions usually are the key constraints and referential integrity constraints. The downside is that everything then depends upon my own programming skills and my own insight into the complexities of the constraints and how they can be violated by certain updates. This can get even a bigger problem if I am not the only one writing the stored procedures. The biggest problems occur when constraints are added, removed or changed afterwards. Since your constraints will be implicitly coded into your stored produres it will be difficult to find all the parts that need to be changed. Keep in mind that one constraint may lead to several checks in several stored procedures. So good documentation of your constraints and how they are implemented in your procedures will be of the utmost importance. And to write the new code you will probably have to familiarize yourself again with all the constraints to see if they don't interact in any funny way. So, my advice would be: don't do it unless you have only a few relatively simple constraints, are a very good programmer, a very good logician, and if you are sure that your constraints will not change in the near future. Kind regards, -- Jan Hidders
David Henry <henryd@net-gong.com> wrote in message news:809aae$r39$1@news2.inter.net.il... > Assuming: > 1. All application access to my database is via stored procedures. > 2. My stored procedures can be relied upon to do sanity checks regarding > relationships between tables This can be a very good idea and it can also be a very very bad idea. In my experience my own procedures sometimes check constraints more efficiently than the DBMS because I know exactly what needs to be checked. Exceptions usually are the key constraints and referential integrity constraints. The downside is that everything then depends upon my own programming skills and my own insight into the complexities of the constraints and how they can be violated by certain updates. This can get even a bigger problem if I am not the only one writing the stored procedures. The biggest problems occur when constraints are added, removed or changed afterwards. Since your constraints will be implicitly coded into your stored produres it will be difficult to find all the parts that need to be changed. Keep in mind that one constraint may lead to several checks in several stored procedures. So good documentation of your constraints and how they are implemented in your procedures will be of the utmost importance. And to write the new code you will probably have to familiarize yourself again with all the constraints to see if they don't interact in any funny way. So, my advice would be: don't do it unless you have only a few relatively simple constraints, are a very good programmer, a very good logician, and if you are sure that your constraints will not change in the near future. Kind regards, -- Jan Hidders
David Henry <henryd@net-gong.com> wrote in message news:809aae$r39$1@news2.inter.net.il... > Assuming: > 1. All application access to my database is via stored procedures. > 2. My stored procedures can be relied upon to do sanity checks regarding > relationships between tables This can be a very good idea and it can also be a very very bad idea. In my experience my own procedures sometimes check constraints more efficiently than the DBMS because I know exactly what needs to be checked. Exceptions usually are the key constraints and referential integrity constraints. The downside is that everything then depends upon my own programming skills and my own insight into the complexities of the constraints and how they can be violated by certain updates. This can get even a bigger problem if I am not the only one writing the stored procedures. The biggest problems occur when constraints are added, removed or changed afterwards. Since your constraints will be implicitly coded into your stored produres it will be difficult to find all the parts that need to be changed. Keep in mind that one constraint may lead to several checks in several stored procedures. So good documentation of your constraints and how they are implemented in your procedures will be of the utmost importance. And to write the new code you will probably have to familiarize yourself again with all the constraints to see if they don't interact in any funny way. So, my advice would be: don't do it unless you have only a few relatively simple constraints, are a very good programmer, a very good logician, and if you are sure that your constraints will not change in the near future. Kind regards, -- Jan Hidders
When you build a database what you do is capturing a piece of the reality in a model. And is desirable the behavior of the model be as close as possible to the reality independent of the application that use it. Perhaps in the future another application will use this database. If the database maintain its integrity by itself, no problem with other applications o direct accesses to the database. Regards Raimundo Lozano David Henry wrote: > Assuming: > 1. All application access to my database is via stored procedures. > 2. My stored procedures can be relied upon to do sanity checks regarding > relationships between tables > > then is it really necessary to define constraints? Isn't that just an > unnecessary overhead and just duplicating the work of my sanity checks? > > Your thoughts would be appreciated.
When you build a database what you do is capturing a piece of the reality in a model. And is desirable the behavior of the model be as close as possible to the reality independent of the application that use it. Perhaps in the future another application will use this database. If the database maintain its integrity by itself, no problem with other applications o direct accesses to the database. Regards Raimundo Lozano David Henry wrote: > Assuming: > 1. All application access to my database is via stored procedures. > 2. My stored procedures can be relied upon to do sanity checks regarding > relationships between tables > > then is it really necessary to define constraints? Isn't that just an > unnecessary overhead and just duplicating the work of my sanity checks? > > Your thoughts would be appreciated.
When you build a database what you do is capturing a piece of the reality in a model. And is desirable the behavior of the model be as close as possible to the reality independent of the application that use it. Perhaps in the future another application will use this database. If the database maintain its integrity by itself, no problem with other applications o direct accesses to the database. Regards Raimundo Lozano David Henry wrote: > Assuming: > 1. All application access to my database is via stored procedures. > 2. My stored procedures can be relied upon to do sanity checks regarding > relationships between tables > > then is it really necessary to define constraints? Isn't that just an > unnecessary overhead and just duplicating the work of my sanity checks? > > Your thoughts would be appreciated.
David> Assuming: David> 1. All application access to my database is via stored procedures. David> 2. My stored procedures can be relied upon to do sanity checks regarding David> relationships between tables David> then is it really necessary to define constraints? Isn't that just an David> unnecessary overhead and just duplicating the work of my sanity checks? Well, to me it seems the handwritten sanity checks are the the real duplication here. Use database-provided integrity constraints whereever possible, and use handwritten stuff *only* for things that cannot be implemented using the standard SQL constructs (NOT NULL, UNIQUE, FOREIGN KEY, CHECK constraint. Don't forget the latter one, it's very powerful). This way, the separation of concerns is much clearer, it's clearly less work, it's bug-free, it's likely to be very fast (probably faster than handwrittencode), and as a bonus it's easier to port to another RDBMS. Cheers, Philip -- If information has state, are we now in the liquid, gaseous or plasmatic phase? ----------------------------------------------------------------------------- Philip Lijnzaad, lijnzaad@ebi.ac.uk | European Bioinformatics Institute,rm A2-24 +44 (0)1223 49 4639 | Wellcome Trust Genome Campus, Hinxton +44 (0)1223 49 4468 (fax) | Cambridgeshire CB10 1SD, GREAT BRITAIN PGP fingerprint: E1 03 BF 80 94 61 B6 FC 50 3D 1F 64 40 75 FB 53
Philip Lijnzaad <lijnzaad@ebi.ac.uk> wrote in message news:u7so2ed9kl.fsf@ebi.ac.uk... > > David> Assuming: > David> 1. All application access to my database is via stored procedures. > David> 2. My stored procedures can be relied upon to do sanity checks regarding > David> relationships between tables > > David> then is it really necessary to define constraints? Isn't that just an > David> unnecessary overhead and just duplicating the work of my sanity checks? > > Well, to me it seems the handwritten sanity checks are the the real > duplication here. Use database-provided integrity constraints whereever > possible, and use handwritten stuff *only* for things that cannot be > implemented using the standard SQL constructs (NOT NULL, UNIQUE, FOREIGN KEY, > CHECK constraint. Don't forget the latter one, it's very powerful). It would be very powerful if you could use arbitrary SELECT statements in a CHECK constraint. However, I don't think many database engines allow that, at least not MS SQL Server (and I don't think Sybase either?). If Microsoft SQL Server supported advanced CHECK constraints, I would move much of my stored procedures code to CHECK constraints. Using stored procedures invariably makes you think 'procedurally', instead of 'logically'. I completely agree with you on UNIQUE, NOT NULL, and FOREIGN KEY constraints, however. Regards, Eric --------------------------- J. Eric Mortensen 1000&1 Development eric@1000-1.com www.1000-1.com ---------------------------
eric wrote: > If Microsoft SQL Server supported advanced CHECK constraints, I would move > much of my stored procedures code to CHECK constraints. Using stored > procedures invariably makes you think 'procedurally', instead of > 'logically'. I'd rather use triggers wherever constraints are not suitable. > I completely agree with you on UNIQUE, NOT NULL, and FOREIGN KEY > constraints, however. dito > Eric Denis Jedig
You might want to consider that if you have multiple stored procedures updating the same set of tables, you would have to duplicate the code which ensures referential integrity into each stored procedure. The alternative would be to have your stored procedures call funtions to perform your 'sanity checks'. In either case IMHO it would be very unlikely that these checks would be performed as fast as the DBMS can check integrity constraints, after all it is optimised to do so :) IMHO 'sanity checks' in Stored Procedures should be used to handle errors more elegantly, and I prefer to code them to intelligently report a referential integrity violation back to the app. If I have to run some code to check if there could be a problem, when ususally there won't be - which you would have to do if you had no ref integrity, then I'm doing a lot of extra work for the few cases where there ends up being a problem. I feel that it's better to go ahead and execute the insert/update/delete, then if I get an error from the DBMS decide how to handle it. Regards, David. David Henry wrote: > Assuming: > 1. All application access to my database is via stored procedures. > 2. My stored procedures can be relied upon to do sanity checks regarding > relationships between tables > > then is it really necessary to define constraints? Isn't that just an > unnecessary overhead and just duplicating the work of my sanity checks? > > Your thoughts would be appreciated.
Also, more and more DBMS are capable of exploiting constraints in their query optimization. E.g. for historical data or "partitioned views" Cheers Serge David Pattinson wrote: > You might want to consider that if you have multiple stored procedures > updating the same set of tables, you would have to duplicate the code which > ensures referential integrity into each stored procedure. The alternative > would be to have your stored procedures call funtions to perform your > 'sanity checks'. In either case IMHO it would be very unlikely that these > checks would be performed as fast as the DBMS can check integrity > constraints, after all it is optimised to do so :) > > IMHO 'sanity checks' in Stored Procedures should be used to handle errors > more elegantly, and I prefer to code them to intelligently report a > referential integrity violation back to the app. If I have to run some code > to check if there could be a problem, when ususally there won't be - which > you would have to do if you had no ref integrity, then I'm doing a lot of > extra work for the few cases where there ends up being a problem. I feel > that it's better to go ahead and execute the insert/update/delete, then if I > get an error from the DBMS decide how to handle it. > > Regards, David. > > David Henry wrote: > > > Assuming: > > 1. All application access to my database is via stored procedures. > > 2. My stored procedures can be relied upon to do sanity checks regarding > > relationships between tables > > > > then is it really necessary to define constraints? Isn't that just an > > unnecessary overhead and just duplicating the work of my sanity checks? > > > > Your thoughts would be appreciated.
>> then is it really necessary to define constraints? Isn't that just an unnecessary overhead and just duplicating the work of my sanity checks? << It is necessary for safety, as other postings have said. However, there are two other factors: 1) Doing the work on the server is usually faster and cheaper than doing over and over on slower clients. 2) Constraints are logical predicates which can be used by a smart optimizer to improve your SQL execution plans. --CELKO-- Sent via Deja.com http://www.deja.com/ Before you buy.
In article <80us6n$74m$1@nnrp1.deja.com>, joe_celko@my-deja.com wrote: > > >> then is it really necessary to define constraints? Isn't that just an > unnecessary overhead and just duplicating the work of my sanity > checks? << > > It is necessary for safety, as other postings have said. However, there > are two other factors: > > 1) Doing the work on the server is usually faster and cheaper than doing > over and over on slower clients. Except that the question was regarding doing it in a stored procedure, which execute on the server. Except for this slight remark, I agree with you... > > 2) Constraints are logical predicates which can be used by a smart > optimizer to improve your SQL execution plans. > > --CELKO-- > > Sent via Deja.com http://www.deja.com/ > Before you buy. > Sent via Deja.com http://www.deja.com/ Before you buy.
David Henry wrote: > > Assuming: > 1. All application access to my database is via stored procedures. > 2. My stored procedures can be relied upon to do sanity checks regarding > relationships between tables > > then is it really necessary to define constraints? Isn't that just an > unnecessary overhead and just duplicating the work of my sanity checks? > > Your thoughts would be appreciated. David, in addition to the wealth of good points that others have already posted, I'd like to point out the following: If you are working with an RDBMS that provides multi-version read consistency like e.g. Oracle (sorry, I don't know how Informix implements transaction isolation), you simply can't do a reliable check for referential integrity without exclusively locking entire tables. This would of course have a tremendous negative effect on data access concurreny, performance and system throughput. The RDBMS engine can always do such constraint checks much more efficiently, because on the lowest level it is not bound to a transactional "read commited" view of the data; internal foreign key constraint checking could e.g. be implemented by just locking a single index leaf block for the short amount of time an "insert into ..."-statement is being processed. Also, declarative integrity constraints have the added benefit that you declare them only once and they will be in effect and protect your data regardless of who accesses your data and by what programs. If you rely only on programmed checks, and you happen to have only one nasty bug in your code, data integrity is lost, even if all the remaining 99.999% of your code is bug-free. Best regards, Peter F'up2 comp.databases.theory -- There are people in this world who don't want to learn what they need to know about computers. Those people are not to make my decisions for me. If they want my Linux, they can come and pry it out of my cold, dead hands. (Paul Ferris)