Primary key uses

Hi all, [Informix 7.3 (migrating -> 9.2), Sun Solaris 5] In the current database, nearly every table has a Primary Key set, yet there are only 2 Referential Keys setup. Most of these primary keys are not being referenced, other than for data validation and data retrieval. What would be the differences (both positive and negative) caused by dropping these restraints and replacing them with Unique Indexes? Of course, I'll leave the single Primary key that is being referenced active until we modify the software to handle the constraint. Is it the general concensus that Referential Integrity should be handled on a software level or via triggers? Thanks, Michael Hoffman

In general there is no differences between 'unique key' and 'primary key' constraint. You can to use both. For example you can write: create table x ( id int not null unique, id_1 int not null unique ); create table y ( i int not null, i_1 int not null, foreign key(i) referencesx(id), foreign key(i_1) refercences x(id_1) ); as well as create table x ( id int not null primary key ) create table y ( i int not null, i_1 int not null, foreign key(i) referencesx ); The table x seems to have two primary key ? But it's ugly solution. In theory, table can't have two or more primary keys. There is no any overhead for using primary key instead of unique constraint - for server it is equal. It's common practice to handle Referential Integrity by Referential Constraints if it's possible, in other cases you can to use triggers and stored procedures, but keep in mind - usage of stored procedures and trigger have much more overhead then usage of Referential Constraints. Michael Hoffman <mrh@panix.com> ''''' ' ''''''''':8tptbv$3i7$1@news.panix.com... > Hi all, > [Informix 7.3 (migrating -> 9.2), Sun Solaris 5] > > In the current database, nearly every table has a Primary Key set, yet > there are only 2 Referential Keys setup. Most of these primary keys are not > being referenced, other than for data validation and data retrieval. > > What would be the differences (both positive and negative) caused by > dropping these restraints and replacing them with Unique Indexes? Of course, > I'll leave the single Primary key that is being referenced active until we > modify the software to handle the constraint. > > Is it the general concensus that Referential Integrity should be > handled on a software level or via triggers? > > Thanks, > Michael Hoffman >

Thanks for the info, although I'm more interested in the differences between the Primary key Constraint and the Unique INDEX (not the unique constraint). Anyone? Anyone? In <8tr8h3$5tq2@www.informix.com> article, Sergey E. Volkov mentioned that: : In general there is no differences between 'unique key' and 'primary key' : constraint. : You can to use both. : For example you can write: : create table x ( id int not null unique, id_1 int not null unique ); : create table y ( i int not null, i_1 int not null, foreign key(i) references : x(id), foreign key(i_1) refercences x(id_1) ); : as well as : create table x ( id int not null primary key ) : create table y ( i int not null, i_1 int not null, foreign key(i) references : x ); : The table x seems to have two primary key ? : But it's ugly solution. In theory, table can't have two or more primary : keys. : There is no any overhead for using primary key instead of unique : constraint - for server it is equal. : It's common practice to handle Referential Integrity by Referential : Constraints if it's possible, in other cases you can to use triggers and : stored procedures, but keep in mind - usage of stored procedures and trigger : have much more overhead then usage of Referential Constraints.

I believe the general consensus would be/should be to use referential integrity in the database via primary and foreign keys. Most modern systems implement this. Where you do not find this is typically in systems developed by people not knowledgeable on the relational theory and/or people with old file I/O or mainframe experience. I believe this is better for the following reasons (this is coming from a person who is primarily a developer, but also a DBA and a sysadmin): 1. Performance is better (with software checks, you have to write code to check for these conditions and this usually means two-three database I/O statements instead of an internal ref. integrity check. 2. You can build complete data models based on the SQL, showing all the relationships among tables. You can do this just by looking at the database schema. Analysts, data modelers can easily see the relations without having to look through thousands of lines of code. 3. You do not have to trust programmers to remember to include referential integrity check code (or bypass it because the deadline has already been missed, etc.). 4. Yes, a unique constraint can ensure uniqueness just as good as a primary key can, but a unique index will not do much in a case where people DELETE a parent record while they leave the children. A properly established primary-foreign key relationship prevents this automatically. You can also implement cascading deletes. 5. Sixteen years of professional experience has shown me that applications built on a solid data model including referential integrity in the database tend to be solid applications themselves, with better performance, fewer bugs, fewer user complaints. I am sure others can think of more benefits. This is just a quick and dirty list. Hal Maner M Systems International, Inc. www.msystemsintl.com Michael Hoffman <mrh@panix.com> wrote in message news:8tptbv$3i7$1@news.panix.com... > Hi all, > [Informix 7.3 (migrating -> 9.2), Sun Solaris 5] > > In the current database, nearly every table has a Primary Key set, yet > there are only 2 Referential Keys setup. Most of these primary keys are not > being referenced, other than for data validation and data retrieval. > > What would be the differences (both positive and negative) caused by > dropping these restraints and replacing them with Unique Indexes? Of course, > I'll leave the single Primary key that is being referenced active until we > modify the software to handle the constraint. > > Is it the general concensus that Referential Integrity should be > handled on a software level or via triggers? > > Thanks, > Michael Hoffman >

You can defer constraint checking until the transaction commits. You can filter constraints. This would allow you to have some inconsistancy on the constraint relationship during the course of the transaction as long as the constraint rules were intact when the transaction commits. Michael Hoffman wrote: > Thanks for the info, although I'm more interested in the differences > between the Primary key Constraint and the Unique INDEX (not the unique > constraint). > > Anyone? Anyone? > > In <8tr8h3$5tq2@www.informix.com> article, Sergey E. Volkov mentioned that: > : In general there is no differences between 'unique key' and 'primary key' > : constraint. > : You can to use both. > > : For example you can write: > > : create table x ( id int not null unique, id_1 int not null unique ); > : create table y ( i int not null, i_1 int not null, foreign key(i) references > : x(id), foreign key(i_1) refercences x(id_1) ); > > : as well as > > : create table x ( id int not null primary key ) > : create table y ( i int not null, i_1 int not null, foreign key(i) references > : x ); > > : The table x seems to have two primary key ? > > : But it's ugly solution. In theory, table can't have two or more primary > : keys. > : There is no any overhead for using primary key instead of unique > : constraint - for server it is equal. > > : It's common practice to handle Referential Integrity by Referential > : Constraints if it's possible, in other cases you can to use triggers and > : stored procedures, but keep in mind - usage of stored procedures and trigger > : have much more overhead then usage of Referential Constraints. -- Madison Pruet =========================================== Enterprise Replication Product Developement Dallas, Texas Informix Software ===========================================

In <8tsrnf$a0a1@www.informix.com> article, Hal Maner mentioned that: : I believe the general consensus would be/should be to use referential : integrity in the database via primary and foreign keys. Most modern systems : implement this. Where you do not find this is typically in systems : developed by people not knowledgeable on the relational theory HARUMPH! :-) : and/or people with old file I/O or mainframe experience. Bing! Bing! Bing! And as a developer, unless performance took a hit, I always made sure to include the checks in my software, regardless of the DB checks. Life is just safer that way. : I believe this is better for the following reasons (this is coming from a : person who is primarily a developer, but also a DBA and a sysadmin): : 1. Performance is better (with software checks, you have to write code to : check for these conditions and this usually means two-three database I/O : statements instead of an internal ref. integrity check). : 4. Yes, a unique constraint can ensure uniqueness just as good as a primary : key can, but a unique index will not do much in a case where people DELETE a : parent record while they leave the children. A properly established : primary-foreign key relationship prevents this automatically. You can also : implement cascading deletes. These are the 2 points that I'm most interested in. I don't pretend to live in a perfect world, so I understand the errors that crop up in a database, especially when Indexes go bad (if they didn't, we wouldn't need Update Stats! :-)). If a Primary Key constraint is just another form of Unique Index, then it can potentially go bad as well. I thought I recalled reading in some past c.d.i. article about Update Stats having no effect on the constraints. Is this true, and if so, what is a cure so performance is not hampered? I'm **really** not concerned with the primary-foreign key relationships. I do understand the benefits of using them, but they don't apply, yet, to this database! That is why there are only 2 of them setup currently, both having to do with user security checks. In that case, I still hold that software is a better place to screen so proper error messages and warnings can be trapped or passed to the user. So, for pure indexing purposes, are Primary Key Constraints better or worse than Unique Indexes?

When you specify a 'primary key', a unique index is built by Informix unless a unique index matching the primary key already exists. The automatically created index (until 9.2, which defaults to detached) would be an attached index, meaning that index pages would be interleaved with data pages. By creating the unique index first and THEN specifying the primary key constraint, you can create the index as a detached index, which generally results in better performance. Doug "Michael Hoffman" <mrh@panix.com> wrote in message news:8tulfb$hni$1@news.panix.com... > In <8tsrnf$a0a1@www.informix.com> article, Hal Maner mentioned that: > : I believe the general consensus would be/should be to use referential > : integrity in the database via primary and foreign keys. Most modern systems > : implement this. Where you do not find this is typically in systems > : developed by people not knowledgeable on the relational theory > > HARUMPH! :-) > > : and/or people with old file I/O or mainframe experience. > > Bing! Bing! Bing! And as a developer, unless performance took a hit, I > always made sure to include the checks in my software, regardless of the > DB checks. Life is just safer that way. > > : I believe this is better for the following reasons (this is coming from a > : person who is primarily a developer, but also a DBA and a sysadmin): > : 1. Performance is better (with software checks, you have to write code to > : check for these conditions and this usually means two-three database I/O > : statements instead of an internal ref. integrity check). > > : 4. Yes, a unique constraint can ensure uniqueness just as good as a primary > : key can, but a unique index will not do much in a case where people DELETE a > : parent record while they leave the children. A properly established > : primary-foreign key relationship prevents this automatically. You can also > : implement cascading deletes. > > These are the 2 points that I'm most interested in. > > I don't pretend to live in a perfect world, so I understand the errors that > crop up in a database, especially when Indexes go bad (if they didn't, we > wouldn't need Update Stats! :-)). If a Primary Key constraint is just another > form of Unique Index, then it can potentially go bad as well. I thought I > recalled reading in some past c.d.i. article about Update Stats having no > effect on the constraints. Is this true, and if so, what is a cure so > performance is not hampered? > > I'm **really** not concerned with the primary-foreign key relationships. I > do understand the benefits of using them, but they don't apply, yet, to this > database! That is why there are only 2 of them setup currently, both having > to do with user security checks. In that case, I still hold that software > is a better place to screen so proper error messages and warnings can be > trapped or passed to the user. > > So, for pure indexing purposes, are Primary Key Constraints better or worse > than Unique Indexes? >

I was answering the following general question which was in your original post: >Is it the general concensus that Referential Integrity should be >handled on a software level or via triggers? If you want to know for your specific database, for indexing purposes only, whether a primary key is better or a unique index, then I would say there is not much difference. Besides the naming/creation time of the index, the only other difference I can think of is that the primary key will force you to have a NOT NULL on the column(s) involved, and the unique index will not. I do not recommend to anyone, based on past experience, to start with a "simple" data model (i.e. without referential integrity) and then fortify it with referential integrity later. Start with a simple but strong (i.e. with the proper integrity) data model and evolve that way - it is a lot more difficult to implement referential integrity to a live database which has been in use for some time. Like you say, software should check for all errors and display the proper messages to the user - referential integrity would support this as well since theoretically we should check the success of every SQL statement and trap/display errors accordingly... Good luck with your project. Hal Maner M Systems International, Inc. www.msystemsintl.com Michael Hoffman <mrh@panix.com> wrote in message news:8tulfb$hni$1@news.panix.com... > In <8tsrnf$a0a1@www.informix.com> article, Hal Maner mentioned that: > : I believe the general consensus would be/should be to use referential > : integrity in the database via primary and foreign keys. Most modern systems > : implement this. Where you do not find this is typically in systems > : developed by people not knowledgeable on the relational theory > > HARUMPH! :-) > > : and/or people with old file I/O or mainframe experience. > > Bing! Bing! Bing! And as a developer, unless performance took a hit, I > always made sure to include the checks in my software, regardless of the > DB checks. Life is just safer that way. > > : I believe this is better for the following reasons (this is coming from a > : person who is primarily a developer, but also a DBA and a sysadmin): > : 1. Performance is better (with software checks, you have to write code to > : check for these conditions and this usually means two-three database I/O > : statements instead of an internal ref. integrity check). > > : 4. Yes, a unique constraint can ensure uniqueness just as good as a primary > : key can, but a unique index will not do much in a case where people DELETE a > : parent record while they leave the children. A properly established > : primary-foreign key relationship prevents this automatically. You can also > : implement cascading deletes. > > These are the 2 points that I'm most interested in. > > I don't pretend to live in a perfect world, so I understand the errors that > crop up in a database, especially when Indexes go bad (if they didn't, we > wouldn't need Update Stats! :-)). If a Primary Key constraint is just another > form of Unique Index, then it can potentially go bad as well. I thought I > recalled reading in some past c.d.i. article about Update Stats having no > effect on the constraints. Is this true, and if so, what is a cure so > performance is not hampered? > > I'm **really** not concerned with the primary-foreign key relationships. I > do understand the benefits of using them, but they don't apply, yet, to this > database! That is why there are only 2 of them setup currently, both having > to do with user security checks. In that case, I still hold that software > is a better place to screen so proper error messages and warnings can be > trapped or passed to the user. > > So, for pure indexing purposes, are Primary Key Constraints better or worse > than Unique Indexes? >

Michael Hoffman wrote: > > In <8tsrnf$a0a1@www.informix.com> article, Hal Maner mentioned that: > : I believe the general consensus would be/should be to use referential > : integrity in the database via primary and foreign keys. Most modern systems > : implement this. Where you do not find this is typically in systems > : developed by people not knowledgeable on the relational theory > > HARUMPH! :-) > > : and/or people with old file I/O or mainframe experience. > > Bing! Bing! Bing! And as a developer, unless performance took a hit, I > always made sure to include the checks in my software, regardless of the > DB checks. Life is just safer that way. Regardless of the checks in the software I always include checks in the database. Life is just safest that way! Art S. Kagel > : I believe this is better for the following reasons (this is coming from a > : person who is primarily a developer, but also a DBA and a sysadmin): > : 1. Performance is better (with software checks, you have to write code to > : check for these conditions and this usually means two-three database I/O > : statements instead of an internal ref. integrity check). > > : 4. Yes, a unique constraint can ensure uniqueness just as good as a primary > : key can, but a unique index will not do much in a case where people DELETE a > : parent record while they leave the children. A properly established > : primary-foreign key relationship prevents this automatically. You can also > : implement cascading deletes. > > These are the 2 points that I'm most interested in. > > I don't pretend to live in a perfect world, so I understand the errors that > crop up in a database, especially when Indexes go bad (if they didn't, we > wouldn't need Update Stats! :-)). If a Primary Key constraint is just another > form of Unique Index, then it can potentially go bad as well. I thought I > recalled reading in some past c.d.i. article about Update Stats having no > effect on the constraints. Is this true, and if so, what is a cure so > performance is not hampered? UNIQUE and PRIMARY KEY constraints are enforced in the data base using a UNIQUE index. Period. Operationally there is no difference. It is the documentation of the relationship that is really different. Art S. Kagel > I'm **really** not concerned with the primary-foreign key relationships. I > do understand the benefits of using them, but they don't apply, yet, to this > database! That is why there are only 2 of them setup currently, both having > to do with user security checks. In that case, I still hold that software > is a better place to screen so proper error messages and warnings can be > trapped or passed to the user. > > So, for pure indexing purposes, are Primary Key Constraints better or worse > than Unique Indexes?