Re: Philosopical debate about constraints

From: "David Henry" <henryd@net-gong.com> > >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. I think your assumptions are "interesting" and probably practically impossible to enforce. :-) ______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com

In article <809fj2$t2t$1@news.xmission.com>, "Obnoxio The Clown" <obnoxio@hotmail.com> wrote: > > From: "David Henry" <henryd@net-gong.com> > > > >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. > > I think your assumptions are "interesting" and probably practically > impossible to enforce. :-) > > ______________________________________________________ > Get Your Private, Free Email at http://www.hotmail.com > I have worked on a system where all users accessed data solely through stored procedures. These procedures had some of the sanity checks you're talking about. However: 1) There were also several programs that ran 'under the covers' that did not use stored procedures because they were long batch jobs. 2) programmers, whether ESQL/C, 4GL, SQL, or Stored procedure programmers (etc...) make mistakes. 3) If a constraint changes, (field 1 used to be required and had to match field 4 in table x, but now it isn't) it's much easier and more reliable to change a single constraint that every stored procedure. 4) does 'on delete cascade' fall under the constraint category? 5) I would think it's more efficient to have the engine do sanity checks than the sp. -- # unrm / ksh: unrm: not found # man cpio Sent via Deja.com http://www.deja.com/ Before you buy.

In article <809fj2$t2t$1@news.xmission.com>, "Obnoxio The Clown" <obnoxio@hotmail.com> wrote: > > From: "David Henry" <henryd@net-gong.com> > > > >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. > > I think your assumptions are "interesting" and probably practically > impossible to enforce. :-) > > ______________________________________________________ > Get Your Private, Free Email at http://www.hotmail.com > I have worked on a system where all users accessed data solely through stored procedures. These procedures had some of the sanity checks you're talking about. However: 1) There were also several programs that ran 'under the covers' that did not use stored procedures because they were long batch jobs. 2) programmers, whether ESQL/C, 4GL, SQL, or Stored procedure programmers (etc...) make mistakes. 3) If a constraint changes, (field 1 used to be required and had to match field 4 in table x, but now it isn't) it's much easier and more reliable to change a single constraint that every stored procedure. 4) does 'on delete cascade' fall under the constraint category? 5) I would think it's more efficient to have the engine do sanity checks than the sp. -- # unrm / ksh: unrm: not found # man cpio Sent via Deja.com http://www.deja.com/ Before you buy.

In article <809fj2$t2t$1@news.xmission.com>, "Obnoxio The Clown" <obnoxio@hotmail.com> wrote: > > From: "David Henry" <henryd@net-gong.com> > > > >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. > > I think your assumptions are "interesting" and probably practically > impossible to enforce. :-) > > ______________________________________________________ > Get Your Private, Free Email at http://www.hotmail.com > I have worked on a system where all users accessed data solely through stored procedures. These procedures had some of the sanity checks you're talking about. However: 1) There were also several programs that ran 'under the covers' that did not use stored procedures because they were long batch jobs. 2) programmers, whether ESQL/C, 4GL, SQL, or Stored procedure programmers (etc...) make mistakes. 3) If a constraint changes, (field 1 used to be required and had to match field 4 in table x, but now it isn't) it's much easier and more reliable to change a single constraint that every stored procedure. 4) does 'on delete cascade' fall under the constraint category? 5) I would think it's more efficient to have the engine do sanity checks than the sp. -- # unrm / ksh: unrm: not found # man cpio Sent via Deja.com http://www.deja.com/ Before you buy.

In article <809fj2$t2t$1@news.xmission.com>, "Obnoxio The Clown" <obnoxio@hotmail.com> wrote: > > From: "David Henry" <henryd@net-gong.com> > > > >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. > > I think your assumptions are "interesting" and probably practically > impossible to enforce. :-) > > ______________________________________________________ > Get Your Private, Free Email at http://www.hotmail.com > I have worked on a system where all users accessed data solely through stored procedures. These procedures had some of the sanity checks you're talking about. However: 1) There were also several programs that ran 'under the covers' that did not use stored procedures because they were long batch jobs. 2) programmers, whether ESQL/C, 4GL, SQL, or Stored procedure programmers (etc...) make mistakes. 3) If a constraint changes, (field 1 used to be required and had to match field 4 in table x, but now it isn't) it's much easier and more reliable to change a single constraint that every stored procedure. 4) does 'on delete cascade' fall under the constraint category? 5) I would think it's more efficient to have the engine do sanity checks than the sp. -- # unrm / ksh: unrm: not found # man cpio Sent via Deja.com http://www.deja.com/ Before you buy.

In article <809fj2$t2t$1@news.xmission.com>, "Obnoxio The Clown" <obnoxio@hotmail.com> wrote: > > From: "David Henry" <henryd@net-gong.com> > > > >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. > > I think your assumptions are "interesting" and probably practically > impossible to enforce. :-) > > ______________________________________________________ > Get Your Private, Free Email at http://www.hotmail.com > I have worked on a system where all users accessed data solely through stored procedures. These procedures had some of the sanity checks you're talking about. However: 1) There were also several programs that ran 'under the covers' that did not use stored procedures because they were long batch jobs. 2) programmers, whether ESQL/C, 4GL, SQL, or Stored procedure programmers (etc...) make mistakes. 3) If a constraint changes, (field 1 used to be required and had to match field 4 in table x, but now it isn't) it's much easier and more reliable to change a single constraint that every stored procedure. 4) does 'on delete cascade' fall under the constraint category? 5) I would think it's more efficient to have the engine do sanity checks than the sp. -- # unrm / ksh: unrm: not found # man cpio Sent via Deja.com http://www.deja.com/ Before you buy.