RE: Philosopical debate about constraints

I prefer to use all the constraints that database server could provide. In case that I don't need one of them I could just disable it. The advantange is that all the integrity and entity constraints are encapsulated in the database and my programs are just code for the user interface and business rules. The database server uses auto-created indexes for verification of RI constraints, which are needed anyway for joins; I could save a lot of work having all those indexes already created. -----Original Message----- From: Sebastian Paul Avarvarei [mailto:proteus@romus.com] Sent: Mi'rcoles 10 de Noviembre de 1999 8:02 AM To: informix-list@iiug.org Subject: Re: Philosopical debate about constraints >> 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. Hi! First thing I learned to my first databse design training was that I must forget about applications and programmers. I must create a database, not a program. Defining relations and constrains not only takes better care of your data integrity, but it also helps defining a more clear data model (helping this way the programmers too). You also gain flexibility: if the reality changes, it's easier to modify the rules in the database than in applications. Defining rules only in application does not increases speed either. In fact, many times it decreases the speed (especially for interpreted languages). I would use stored procedures only for very complex validations, that can be implemented otherwise as relations/constraints. Regards, Sebastian Paul A. E-mail: proteus@romus.com

Hi all, I agree that check constraints are a fine thing to work with - for tests that work within one row of course. But at the moment I would be carefully using foreign key constraints (at least with 7.x, don't know about 9.x). Consider the following: 1. You have a table with a primary key containing columns A and B. And column A is also referencing another table. You need a unique index for the primary key and a duplicate index for the foreign key constraint. This second index is of no use for the engine, the compound index is also used if you have a select that has only column A in the where-clause - asuming that field A is heading the unique index. That way you have some unnecessary indices slowing down inserts, updates and deletes only satisfying the formal need of the foreign key. 2. Consider a large table with a column referencing a small parameter-table. You will get a multi-million-rows index with only a few distinct values - this is very slow on updates with btree-indices. A bitmap-index would be fine, but is not available in 7.x 3. Enabling a foreign key constraint is sooo slow. There is HPL to load data fast, there is parallel index build feature, but you wait hours and hours for completion of a constraint-enable on a table where an index is build in less than 10 minutes. I have done several database reorgs. Enabling foreign keys was the most time consuming part by far. Therefore my 2 (euro-) cents: Don't use foreign key constraints in a production environment. Comments? Paul

Hi all, I agree that check constraints are a fine thing to work with - for tests that work within one row of course. But at the moment I would be carefully using foreign key constraints (at least with 7.x, don't know about 9.x). Consider the following: 1. You have a table with a primary key containing columns A and B. And column A is also referencing another table. You need a unique index for the primary key and a duplicate index for the foreign key constraint. This second index is of no use for the engine, the compound index is also used if you have a select that has only column A in the where-clause - asuming that field A is heading the unique index. That way you have some unnecessary indices slowing down inserts, updates and deletes only satisfying the formal need of the foreign key. 2. Consider a large table with a column referencing a small parameter-table. You will get a multi-million-rows index with only a few distinct values - this is very slow on updates with btree-indices. A bitmap-index would be fine, but is not available in 7.x 3. Enabling a foreign key constraint is sooo slow. There is HPL to load data fast, there is parallel index build feature, but you wait hours and hours for completion of a constraint-enable on a table where an index is build in less than 10 minutes. I have done several database reorgs. Enabling foreign keys was the most time consuming part by far. Therefore my 2 (euro-) cents: Don't use foreign key constraints in a production environment. Comments? Paul

Hi all, I agree that check constraints are a fine thing to work with - for tests that work within one row of course. But at the moment I would be carefully using foreign key constraints (at least with 7.x, don't know about 9.x). Consider the following: 1. You have a table with a primary key containing columns A and B. And column A is also referencing another table. You need a unique index for the primary key and a duplicate index for the foreign key constraint. This second index is of no use for the engine, the compound index is also used if you have a select that has only column A in the where-clause - asuming that field A is heading the unique index. That way you have some unnecessary indices slowing down inserts, updates and deletes only satisfying the formal need of the foreign key. 2. Consider a large table with a column referencing a small parameter-table. You will get a multi-million-rows index with only a few distinct values - this is very slow on updates with btree-indices. A bitmap-index would be fine, but is not available in 7.x 3. Enabling a foreign key constraint is sooo slow. There is HPL to load data fast, there is parallel index build feature, but you wait hours and hours for completion of a constraint-enable on a table where an index is build in less than 10 minutes. I have done several database reorgs. Enabling foreign keys was the most time consuming part by far. Therefore my 2 (euro-) cents: Don't use foreign key constraints in a production environment. Comments? Paul

Paul Herger wrote: > > Hi all, > > I agree that check constraints are a fine thing to work with - for tests > that work within one row of course. > > But at the moment I would be carefully using foreign key constraints (at > least with 7.x, don't know about 9.x). Consider the following: > > 1. You have a table with a primary key containing columns A and B. And > column A is also referencing another table. You need a unique index for the > primary key and a duplicate index for the foreign key constraint. This > second index is of no use for the engine, the compound index is also used if > you have a select that has only column A in the where-clause - asuming that > field A is heading the unique index. That way you have some unnecessary > indices slowing down inserts, updates and deletes only satisfying the formal > need of the foreign key. Actually the optimizer WILL use the singleton index on column A only when it is more efficient to do so. The optimizer checks the nlevels and nleaves and uses these to estimate the average number of IOs per key find when choosing indexes. So while on one table the two indexes may indeed be roughly equivalent and the optimizer will just pick the first index declared that begins with the column on another table they may not be the same depth and so the singleton index will be selected. This is a bad general rule and is best left up to the optimizer. > 2. Consider a large table with a column referencing a small parameter-table. > You will get a multi-million-rows index with only a few distinct values - > this is very slow on updates with btree-indices. A bitmap-index would be > fine, but is not available in 7.x You are correct that this is a poor use of foreign keys, ie to validate attribute values. Foreign keys are meant as a means to link records and prevent orphaned rows. The validation you describe is better performed by a check constraint. So on this one we agree, but it is not a problem with foreign keys just database design. > 3. Enabling a foreign key constraint is sooo slow. There is HPL to load data > fast, there is parallel index build feature, but you wait hours and hours > for completion of a constraint-enable on a table where an index is build in > less than 10 minutes. I have done several database reorgs. Enabling foreign > keys was the most time consuming part by far. > > Therefore my 2 (euro-) cents: Don't use foreign key constraints in a > production environment. I have dozens of databases to maintain. My largest and most recurring headaches come from databases that have no RI constraints and rely on the applications to maintain integrity (and I view trigger and stored procedure based integrity checking to be the same as application level checking it's just coded in SQL and SPL rather than C or FORTRAN). How often have a been called over because a "database problem" is not allowing some user to see some data. Just the other day I helped the new programmer who inherited our employee attendence application figure out why he and some other user could not modify their "When I'm out call..." notations on their records but I could. I found the for him his SS# was the link between two tables in two different database (which should be one and would have to be for RI but that's another story) was not set in one of the tables and the other user had two rows in that table one with his SS# and another with his employee ID duplicated in the SS# column which is the row that the app was picking up. If the keys and RI had been set up properly this could not happen. We wasted 3 days together looking at the application code for the display function, and he spend days before coming to me to complain about this "database problem", all wasted time. OH, 4 months ago I helped the last programmer of this app to clean up just these problems and he then fixed the application so "this sh** can't happen anymore". Nope. No amount of time spend enabling foreign keys is too much to spend to gain data integrity, peace of mind, reliability, and most of all sleep (I sleep much better when I need not worry about my data). Bottom line I vote for FULL relation integrity constraints. Primary keys, foreign keys, check constraints, defaults, not null constraints, whatever. Hell if they had anti-stupid-programmer/manager constraints I'd use those also. Art S. Kagel