No primary keys ~ Answered: amber (solid confidence) — Jonathan Leffler (an Informix engineer) authoritatively refutes Earle Long's repeated wrong claim that Informix auto-creates a serial index when no PK is defined, giving version-by-version history; asker never confirms but the technical answer is solid before the thread drifts into DW/index philosophy.

Advisory only.

--=====_97896035211942=_ Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: quoted-printable What does Informix do if there are no primary key or foriegn key= attributes defined in the database schema ? Will Informix try to build a= primary key Index based on as many fields as it thinks it needs for record= uniqueness ? Is this process expensive in terms of compute time ? = Thanks, = Thomas Hansford = hansford@salemleasi= ng.com = Sr. Software Engineer Manpower Professional Services --=====_97896035211942=_ Content-Type: text/html; charset="us-ascii" <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META content="text/html; charset=iso-8859-1" http-equiv=Content-Type> <META content="MSHTML 5.00.2614.3500" name=GENERATOR></HEAD> <BODY bgColor=#ffffff style="FONT-FAMILY: Arial" text=#000000> <DIV><FONT size=2>&nbsp;What does Informix do if there are no primary key or foriegn key attributes defined in the database schema ? Will Informix try to build a primary key Index based on as many fields as it thinks it needs for record uniqueness ? Is this process expensive in terms of compute time ? </FONT></DIV> <DIV>&nbsp;</DIV> <DIV>&nbsp;</DIV> <DIV>&nbsp;</DIV> <DIV>&nbsp;</DIV> <DIV>&nbsp;</DIV> <DIV>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Thanks,</DIV> <DIV>&nbsp;</DIV> <DIV>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Thomas Hansford</DIV> <DIV>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <A href="mailto:hansford@salemleasing.com">hansford@salemleasing.com</A></DIV> <DIV>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </DIV></BODY></HTML> <BR> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META content="text/html; charset=iso-8859-1" http-equiv=Content-Type> <META content="MSHTML 5.00.2614.3500" name=GENERATOR></HEAD> <BODY bgColor=#ffffff style="FONT-FAMILY: Arial" text=#000000> <DIV>Sr.&nbsp; Software Engineer</DIV> <DIV>Manpower Professional Services</DIV></BODY></HTML> --=====_97896035211942=_--

My understanding is that if you do not define a unique primary key to a database table then informix will create one of type serial which does not show in the tables list of columns. Yours -- Earle A Long (Senior Informix DBA) SINGLEPOINT UK LTD "Wayne Hansford" <hansford@salemleasing.com> wrote in message news:93cg93$jih$1@news.xmission.com... > > --=====_97896035211942=_ > Content-Type: text/plain; charset="ISO-8859-1" > Content-Transfer-Encoding: quoted-printable > > What does Informix do if there are no primary key or foriegn key= > attributes defined in the database schema ? Will Informix try to build a= > primary key Index based on as many fields as it thinks it needs for record= > uniqueness ? Is this process expensive in terms of compute time ? > > > > > > = > Thanks, > > = > Thomas Hansford > = > hansford@salemleasi= > ng.com > = > > Sr. Software Engineer > Manpower Professional Services > > > --=====_97896035211942=_ > Content-Type: text/html; charset="us-ascii" > > <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> > <HTML><HEAD> > <META content="text/html; charset=iso-8859-1" http-equiv=Content-Type> > <META content="MSHTML 5.00.2614.3500" name=GENERATOR></HEAD> > <BODY bgColor=#ffffff style="FONT-FAMILY: Arial" text=#000000> > <DIV><FONT size=2>&nbsp;What does Informix do if there are no primary key or > foriegn key attributes defined in the database schema ? Will Informix try to > build a primary key Index based on as many fields as it thinks it needs for > record uniqueness ? Is this process expensive in terms of compute time ? > </FONT></DIV> > <DIV>&nbsp;</DIV> > <DIV>&nbsp;</DIV> > <DIV>&nbsp;</DIV> > <DIV>&nbsp;</DIV> > <DIV>&nbsp;</DIV> > <DIV>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp ;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;& nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp ;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;& nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp ;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;& nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp ;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; > Thanks,</DIV> > <DIV>&nbsp;</DIV> > <DIV>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp ;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;& nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp ;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;& nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp ;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;& nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp ;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; > Thomas Hansford</DIV> > <DIV>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp ;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;& nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp ;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;& nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp ;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;& nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp ;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; > <A href="mailto:hansford@salemleasing.com">hansford@salemleasing.com</A></DIV> > <DIV>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp ;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;& nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp ;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;& nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp ;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;& nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; > </DIV></BODY></HTML> > > <BR> > <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> > <HTML><HEAD> > <META content="text/html; charset=iso-8859-1" http-equiv=Content-Type> > <META content="MSHTML 5.00.2614.3500" name=GENERATOR></HEAD> > <BODY bgColor=#ffffff style="FONT-FAMILY: Arial" text=#000000> > <DIV>Sr.&nbsp; Software Engineer</DIV> > <DIV>Manpower Professional Services</DIV></BODY></HTML> > > > --=====_97896035211942=_-- >

Earle A Long wrote: > My understanding is that if you do not define a unique primary key to a > database table then informix will create one of type serial which does not > show in the tables list of columns. No, this is not what happens, but there is just enough semblance of truth in the comment that some explanation is in order rather than a simple denial. I think Earle is thinking of the ROWID which, in SE, is similar to SERIAL in some respects. However, the ROWID is there in non-fragmented tables regardless of whether there is a primary key specified. The ROWID is a pseudo-column which is not actually stored in the data; it is a record number in SE and a slot address in OnLine. It can be used to find a unique record, regardless of duplicates or not. However, you are strongly counselled not to use ROWID, and it is critical that you never, ever store a ROWID in a permanent table in the database. If there is no unique constraint specified on a table, Informix allows duplicate rows to be inserted into that table. It is difficult to do anything with just one of the duplicate rows using pure SQL (SQL that does not reference ROWID values). In my view, every table, regardless of size, should have a primary key constraint. > "Wayne Hansford" <hansford@salemleasing.com> wrote: > > What does Informix do if there are no primary key or foriegn key > > attributes defined in the database schema ? Will Informix try to build a > > primary key Index based on as many fields as it thinks it needs for record > > uniqueness ? Is this process expensive in terms of compute time ? -- Yours, Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN "I don't suffer from insanity; I enjoy every minute of it!"

> What does Informix do if there are no primary key or foriegn key > attributes defined in the database schema ? Will Informix try to build a > primary key Index based on as many fields as it thinks it needs for record > uniqueness ? Is this process expensive in terms of compute time ? I can't speak for older servers, but recent (7/8/9) versions of Online do not behave like that. A table need not contain any primary or unique key, and Informix won't build one for you if you don't specify one. -cs Sent via Deja.com http://www.deja.com/

Complementing the excellent Jonathan Leffler response, you can't even use referencial constraint (foreign keys) between your tables if you do not have primary keys on them. That's why it's strongly recommendated that all tables in your database model must have a defined primary key. Regards. -- Paulo Roberto Marelli de Amorim TS&0 Consulting Brasil In article <3A5A185C.794B6D61@informix.com>, Jonathan Leffler <jleffler@informix.com> wrote: > Earle A Long wrote: > > My understanding is that if you do not define a unique primary key to a > > database table then informix will create one of type serial which does not > > show in the tables list of columns. > > No, this is not what happens, but there is just enough semblance of > truth in the comment that some explanation is in order rather than a > simple denial. > > I think Earle is thinking of the ROWID which, in SE, is similar to > SERIAL in some respects. However, the ROWID is there in non- fragmented > tables regardless of whether there is a primary key specified. The > ROWID is a pseudo-column which is not actually stored in the data; it is > a record number in SE and a slot address in OnLine. It can be used to > find a unique record, regardless of duplicates or not. However, you are > strongly counselled not to use ROWID, and it is critical that you never, > ever store a ROWID in a permanent table in the database. > > If there is no unique constraint specified on a table, Informix allows > duplicate rows to be inserted into that table. It is difficult to do > anything with just one of the duplicate rows using pure SQL (SQL that > does not reference ROWID values). In my view, every table, regardless > of size, should have a primary key constraint. > > > "Wayne Hansford" <hansford@salemleasing.com> wrote: > > > What does Informix do if there are no primary key or foriegn key > > > attributes defined in the database schema ? Will Informix try to build a > > > primary key Index based on as many fields as it thinks it needs for record > > > uniqueness ? Is this process expensive in terms of compute time ? > > -- > Yours, > Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> > Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN > "I don't suffer from insanity; I enjoy every minute of it!" > Sent via Deja.com http://www.deja.com/

> you can't even > use referencial constraint (foreign keys) between your tables if you do > not have primary keys on them. Child tables need no primary key, only parent tables do. > That's why it's strongly recommendated that all tables in your database > model must have a defined primary key. I don't think this is valid generic advice. One should consider whether one needs something before using it, because it does have a cost in both disk space and performance. Primary keys are often vital in "operational" systems, i.e. OLTP or ODS (operational data store) applications. Some DSS applications do not need them, or at least not on all tables. -cs Sent via Deja.com http://www.deja.com/

No Jonathan I'm not thinking of rowid I'm thinking of an additional index. Like I said if you do not specify a primary index then informix will create one of type serial. This is true all standard engines I've worked on. It may or may not be true with Online. When I get chance I'll check it unless somebody knows for certain like maybe our illustrious lords and masters at Informix inc.. Yours -- Earle A Long (Senior Informix DBA) SINGLEPOINT UK LTD "Jonathan Leffler" <jleffler@informix.com> wrote in message news:3A5A185C.794B6D61@informix.com... > Earle A Long wrote: > > My understanding is that if you do not define a unique primary key to a > > database table then informix will create one of type serial which does not > > show in the tables list of columns. > > No, this is not what happens, but there is just enough semblance of > truth in the comment that some explanation is in order rather than a > simple denial. > > I think Earle is thinking of the ROWID which, in SE, is similar to > SERIAL in some respects. However, the ROWID is there in non-fragmented > tables regardless of whether there is a primary key specified. The > ROWID is a pseudo-column which is not actually stored in the data; it is > a record number in SE and a slot address in OnLine. It can be used to > find a unique record, regardless of duplicates or not. However, you are > strongly counselled not to use ROWID, and it is critical that you never, > ever store a ROWID in a permanent table in the database. > > If there is no unique constraint specified on a table, Informix allows > duplicate rows to be inserted into that table. It is difficult to do > anything with just one of the duplicate rows using pure SQL (SQL that > does not reference ROWID values). In my view, every table, regardless > of size, should have a primary key constraint. > > > "Wayne Hansford" <hansford@salemleasing.com> wrote: > > > What does Informix do if there are no primary key or foriegn key > > > attributes defined in the database schema ? Will Informix try to build a > > > primary key Index based on as many fields as it thinks it needs for record > > > uniqueness ? Is this process expensive in terms of compute time ? > > -- > Yours, > Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> > Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN > "I don't suffer from insanity; I enjoy every minute of it!" >

Earle, I can vague remember, that when SE created a table it also create a dummy ISAM-index with no fields in it. If you looked at the resulting ISAM-File using a call to "isindexinfo" (in the C-ISAM library) you found out, that there was already an index. I think the reason was, that "isbuild" required an mandatory index which was called the primary key (in the meaning of C-ISAM, not SQL). But I think it was an index with k_nparts = 0 to avoid waste of space. Therefore SE could only create 7 indexes altough ISAM was capable of 8. Paul "Earle A Long" <earle@btclick.com> schrieb im Newsbeitrag news:b3G66.53$IX3.2502@NewsReader... > No Jonathan I'm not thinking of rowid I'm thinking of an additional index. > Like I said if you do not specify a primary index then informix will create > one of type serial. This is true all standard engines I've worked on. It may > or may not be true with Online. When I get chance I'll check it unless > somebody knows for certain like maybe our illustrious lords and masters at > Informix inc.. > > Yours > -- > Earle A Long (Senior Informix DBA) > SINGLEPOINT UK LTD > > "Jonathan Leffler" <jleffler@informix.com> wrote in message > news:3A5A185C.794B6D61@informix.com... > > Earle A Long wrote: > > > My understanding is that if you do not define a unique primary key to a > > > database table then informix will create one of type serial which does > not > > > show in the tables list of columns. > > > > No, this is not what happens, but there is just enough semblance of > > truth in the comment that some explanation is in order rather than a > > simple denial. > > > > I think Earle is thinking of the ROWID which, in SE, is similar to > > SERIAL in some respects. However, the ROWID is there in non-fragmented > > tables regardless of whether there is a primary key specified. The > > ROWID is a pseudo-column which is not actually stored in the data; it is > > a record number in SE and a slot address in OnLine. It can be used to > > find a unique record, regardless of duplicates or not. However, you are > > strongly counselled not to use ROWID, and it is critical that you never, > > ever store a ROWID in a permanent table in the database. > > > > If there is no unique constraint specified on a table, Informix allows > > duplicate rows to be inserted into that table. It is difficult to do > > anything with just one of the duplicate rows using pure SQL (SQL that > > does not reference ROWID values). In my view, every table, regardless > > of size, should have a primary key constraint. > > > > > "Wayne Hansford" <hansford@salemleasing.com> wrote: > > > > What does Informix do if there are no primary key or foriegn key > > > > attributes defined in the database schema ? Will Informix try to > build a > > > > primary key Index based on as many fields as it thinks it needs for > record > > > > uniqueness ? Is this process expensive in terms of compute time ? > > > > -- > > Yours, > > Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> > > Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN > > "I don't suffer from insanity; I enjoy every minute of it!" > > > >

>> What does Informix do if there are no primary key or foreign key attributes defined in the database schema? << Go back to basics. The real problem is that you do not have a table at all -- no key, no table. You have a multi-set. Access to the data will be horrible unless you add an index. But that is nothing compared to the potential logical problems you will ave made for yourself. These things should be constructed only as "holding tanks" for data, used to scrub the data, then move it to a "proper table" that you actually use for the applications. --CELKO-- Joe Celko, SQL Guru & DBA at Trilogy When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc) which can be cut and pasted into Query Analyzer is appreciated. Sent via Deja.com http://www.deja.com/

Paul, Yes I too remember that but informix also creates an additional one of type serial if you do not specify a unique primary key as bcheck will show. What I cant' check at the moment is whether Online also does this. Not that it really matters since as any database designer knows all tables should have a unique primary index <smile> Yours -- Earle A Long (Senior Informix DBA) SINGLEPOINT LTD "Paul Herger" <herger@netway.at> wrote in message news:93fbrn$65c$1@news.netway.at... > Earle, > > I can vague remember, that when SE created a table it also create a dummy > ISAM-index with no fields in it. If you looked at the resulting ISAM-File > using a call to "isindexinfo" (in the C-ISAM library) you found out, that > there was already an index. > > I think the reason was, that "isbuild" required an mandatory index which was > called the primary key (in the meaning of C-ISAM, not SQL). But I think it > was an index with k_nparts = 0 to avoid waste of space. > > Therefore SE could only create 7 indexes altough ISAM was capable of 8. > > Paul > > "Earle A Long" <earle@btclick.com> schrieb im Newsbeitrag > news:b3G66.53$IX3.2502@NewsReader... > > No Jonathan I'm not thinking of rowid I'm thinking of an additional index. > > Like I said if you do not specify a primary index then informix will > create > > one of type serial. This is true all standard engines I've worked on. It > may > > or may not be true with Online. When I get chance I'll check it unless > > somebody knows for certain like maybe our illustrious lords and masters at > > Informix inc.. > > > > Yours > > -- > > Earle A Long (Senior Informix DBA) > > SINGLEPOINT UK LTD > > > > "Jonathan Leffler" <jleffler@informix.com> wrote in message > > news:3A5A185C.794B6D61@informix.com... > > > Earle A Long wrote: > > > > My understanding is that if you do not define a unique primary key to > a > > > > database table then informix will create one of type serial which does > > not > > > > show in the tables list of columns. > > > > > > No, this is not what happens, but there is just enough semblance of > > > truth in the comment that some explanation is in order rather than a > > > simple denial. > > > > > > I think Earle is thinking of the ROWID which, in SE, is similar to > > > SERIAL in some respects. However, the ROWID is there in non-fragmented > > > tables regardless of whether there is a primary key specified. The > > > ROWID is a pseudo-column which is not actually stored in the data; it is > > > a record number in SE and a slot address in OnLine. It can be used to > > > find a unique record, regardless of duplicates or not. However, you are > > > strongly counselled not to use ROWID, and it is critical that you never, > > > ever store a ROWID in a permanent table in the database. > > > > > > If there is no unique constraint specified on a table, Informix allows > > > duplicate rows to be inserted into that table. It is difficult to do > > > anything with just one of the duplicate rows using pure SQL (SQL that > > > does not reference ROWID values). In my view, every table, regardless > > > of size, should have a primary key constraint. > > > > > > > "Wayne Hansford" <hansford@salemleasing.com> wrote: > > > > > What does Informix do if there are no primary key or foriegn key > > > > > attributes defined in the database schema ? Will Informix try to > > build a > > > > > primary key Index based on as many fields as it thinks it needs for > > record > > > > > uniqueness ? Is this process expensive in terms of compute time ? > > > > > > -- > > > Yours, > > > Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> > > > Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN > > > "I don't suffer from insanity; I enjoy every minute of it!" > > > > > > > > >

>> Child tables need no primary key, only parent tables do. << First of all, the terms I think you meant to use are "referenced" and "referencing" tables. There is no such thing as a child and parent table in SQL; those terms are from the days of pointer chains in hierarchical and network databases. Secondly, wrong! ALL tables, to be tables, have to have a key. That is a basic definition. This is very valid generic advice. --CELKO-- Joe Celko, SQL Guru & DBA at Trilogy When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc) which can be cut and pasted into Query Analyzer is appreciated. Sent via Deja.com http://www.deja.com/

> First of all, the terms I think you meant to use are "referenced" > and "referencing" tables. There is no such thing as a child and > parent table in SQL; those terms are from the days of pointer chains > in hierarchical and network databases. > > Secondly, wrong! ALL tables, to be tables, have to have a key. That > is a basic definition. This is very valid generic advice. Joe, Thank you for that bit of SQL indoctrination. Now I'd just like to point out that I was not talking about SQL nor anything specifically defined within that religion. I was responding to questions specifically about Informix databases, in which, let me assure you, one can define a "table" that has no "key." Please excuse me for confusing anybody that was thrown completely off track by my heretical use of the terms "child" and "parent" to express the roles in a referential relationship. Finally, in defense of my statement that not all "tables" in an Informix database might want to have a primary key (constraint explicitly) defined on them, consider the case of a fact table in a data warehouse. Typically, this sucker is loaded up with foreign key constraints associated with the surrounding dimension tables, but need not have a primary key explicitly created for it within the RDBMS (whether or not it has one conceptually is a different matter). I've even heard rumors that some folks don't use ANY indexes in their datamart/data warehouse, which I guess means they didn't explicitly define even the primary key indexes on the dimension tables. Go figure. -cs Sent via Deja.com http://www.deja.com/

Earle A Long wrote: > No Jonathan I'm not thinking of rowid I'm thinking of an additional index. OK; then I respectfully suggest you are wrong. > Like I said if you do not specify a primary index then informix will create > one of type serial. This is true all standard engines I've worked on. It may > or may not be true with Online. When I get chance I'll check it unless > somebody knows for certain like maybe our illustrious lords and masters at > Informix inc.. OK; it may be true of all the versions of Standard Engine that you've worked on, but it has not (I regret to contradict you) been the case in any of the versions I've worked on, which range from 1.10.00 through 2.10.03x (when it wasn't really called Standard Engine) via 4.00, 4.1x, 5.0x, 5.1x, to 7.24. That leaves a few versions (6.00, 7.0x, 7.1x, 7.2{0,1,2,3}) with wriggle room, but I would be rather surprised if the feature was added in any of those versions only to be dropped in the 7.24 version. The 7.25 version of SE, using 7.25 C-ISAM, is not supposed to have any such feature in it, but I don't actually have it installed on any of my machines so I could be wrong (but I'd have to enter a bug against it if I were; it was not in the specification). Oh, and the pre-SQL Informix 3.30 product didn't do it either. And no version of OnLine that I've worked with (from Turbo 1.10.03x through OnLine 4.00, 4.1x, 5.0x, 5.10, 6.00, 7.1x, 7.2x, 8.3x, 9.0x, 9.1x or 9.2x) does this either. I'm not sure whether Illustra ever did this -- or Ardent, RedBrick or Cloudscape; I am not pontificating on those DBMS. Were any of those the "illustrious lords and masters" to whom you were referring. I'm not sure who else currently working at Informix you'd be thinking of. As someone else pointed out, the C-ISAM files used with SE have a peculiar index 0 which provides ROWID access -- and which is why the average C-ISAM file created by a generic C-ISAM program is typically not suited for use with SE. This would show up as an index in bcheck or secheck. I am still trying to be charitable and think this is misleading you, Earle. However, if you choose to think otherwise (either that I'm being uncharitable or that this is not what you are thinking of), then I cannot stop you doing so and I won't argue the point much further. I've made my views known. > "Jonathan Leffler" <jleffler@informix.com> wrote: > > Earle A Long wrote: > > > My understanding is that if you do not define a unique primary key to a > > > database table then informix will create one of type serial which does not > > > > show in the tables list of columns. > > > > No, this is not what happens, but there is just enough semblance of > > truth in the comment that some explanation is in order rather than a > > simple denial. > > > > I think Earle is thinking of the ROWID which, in SE, is similar to > > SERIAL in some respects. However, the ROWID is there in non-fragmented > > tables regardless of whether there is a primary key specified. The > > ROWID is a pseudo-column which is not actually stored in the data; it is > > a record number in SE and a slot address in OnLine. It can be used to > > find a unique record, regardless of duplicates or not. However, you are > > strongly counselled not to use ROWID, and it is critical that you never, > > ever store a ROWID in a permanent table in the database. > > > > If there is no unique constraint specified on a table, Informix allows > > duplicate rows to be inserted into that table. It is difficult to do > > anything with just one of the duplicate rows using pure SQL (SQL that > > does not reference ROWID values). In my view, every table, regardless > > of size, should have a primary key constraint. > > > > > "Wayne Hansford" <hansford@salemleasing.com> wrote: > > > > What does Informix do if there are no primary key or foriegn key > > > > attributes defined in the database schema ? Will Informix try to build > a > > > > primary key Index based on as many fields as it thinks it needs for > record > > > > uniqueness ? Is this process expensive in terms of compute time ? -- Jonathan Leffler (jleffler@earthlink.net, jleffler@informix.com) Guardian of DBD::Informix 1.00.PC1 -- see http://www.cpan.org/ #include <disclaimer.h>

Whether or not your tables use PRIMARY KEY constraints, UNIQUE constraints, UNIQUE indexes or none at all and you just (YUCK!) maintain uniqueness through force of will and fantastic programming, EVERY ROW IN EVERY TABLE MUST HAVE A COMBINATION OF COLUMNS WHICH CAN BE USED TO UNIQUELY IDENTIFY A SINGLE ROW! I guarantee your DW Fact tables and dimension tables do indeed have a unique key even if they have no constraints or other unique indexes on them. Otherwise they are not very useful, how would the dimension table rows reference the fact row they identify without them? Art S. Kagel cedarsiding@my-deja.com wrote: > > > First of all, the terms I think you meant to use are "referenced" > > and "referencing" tables. There is no such thing as a child and > > parent table in SQL; those terms are from the days of pointer chains > > in hierarchical and network databases. > > > > Secondly, wrong! ALL tables, to be tables, have to have a key. That > > is a basic definition. This is very valid generic advice. > > Joe, > > Thank you for that bit of SQL indoctrination. > > Now I'd just like to point out that I was not talking > about SQL nor anything specifically defined within > that religion. I was responding to questions specifically > about Informix databases, in which, let me assure you, > one can define a "table" that has no "key." > > Please excuse me for confusing anybody that was thrown > completely off track by my heretical use of the terms > "child" and "parent" to express the roles in a > referential relationship. > > Finally, in defense of my statement that not all "tables" > in an Informix database might want to have a primary key > (constraint explicitly) defined on them, consider the case > of a fact table in a data warehouse. Typically, this > sucker is loaded up with foreign key constraints associated > with the surrounding dimension tables, but need not have > a primary key explicitly created for it within the RDBMS > (whether or not it has one conceptually is a different > matter). > > I've even heard rumors that some folks don't use ANY indexes > in their datamart/data warehouse, which I guess means they > didn't explicitly define even the primary key indexes on > the dimension tables. Go figure. > > -cs > > Sent via Deja.com > http://www.deja.com/

In article <3A5CE72D.2F98D822@bloomberg.net>, kagel@bloomberg.net wrote: > Whether or not your tables use PRIMARY KEY constraints, UNIQUE constraints, > UNIQUE indexes or none at all and you just (YUCK!) maintain uniqueness > through force of will and fantastic programming, EVERY ROW IN EVERY TABLE > MUST HAVE A COMBINATION OF COLUMNS WHICH CAN BE USED TO UNIQUELY IDENTIFY > A SINGLE ROW! I guarantee your DW Fact tables and dimension tables do > indeed have a unique key even if they have no constraints or other unique > indexes on them. Otherwise they are not very useful, how would the > dimension table rows reference the fact row they identify without them? 1. Please note, cs said the explicit creation of a primary key and mentioned that there might be an implied one. From the original post on, I have felt that the discussion was about the repurcussions of not defining a primary key to the database, not about having the table not have a implementation specific primary key. 2. The fact tables reference the dimenstion tables. 3. I can think of a situation where the fact's would not neccesarily need to have a unique id. It might cause some pain for other reasons... but it I think it is plausible. 4. As I mentioned in a previous post, sometimes it is neccesary to not have any indexes on a table, raw tables are a good example, indexes are not allowed. They allow significant performance enhancements for a cost. There are some good ways to ensure data integrity in this environment, but that is getting off the topic. Indexes sometimes lead to index joins. Index joins can be _BAD_. Will > > Art S. Kagel > > cedarsiding@my-deja.com wrote: > > > > > First of all, the terms I think you meant to use are "referenced" > > > and "referencing" tables. There is no such thing as a child and > > > parent table in SQL; those terms are from the days of pointer chains > > > in hierarchical and network databases. > > > > > > Secondly, wrong! ALL tables, to be tables, have to have a key. That > > > is a basic definition. This is very valid generic advice. > > > > Joe, > > > > Thank you for that bit of SQL indoctrination. > > > > Now I'd just like to point out that I was not talking > > about SQL nor anything specifically defined within > > that religion. I was responding to questions specifically > > about Informix databases, in which, let me assure you, > > one can define a "table" that has no "key." > > > > Please excuse me for confusing anybody that was thrown > > completely off track by my heretical use of the terms > > "child" and "parent" to express the roles in a > > referential relationship. > > > > Finally, in defense of my statement that not all "tables" > > in an Informix database might want to have a primary key > > (constraint explicitly) defined on them, consider the case > > of a fact table in a data warehouse. Typically, this > > sucker is loaded up with foreign key constraints associated > > with the surrounding dimension tables, but need not have > > a primary key explicitly created for it within the RDBMS > > (whether or not it has one conceptually is a different > > matter). > > > > I've even heard rumors that some folks don't use ANY indexes > > in their datamart/data warehouse, which I guess means they > > didn't explicitly define even the primary key indexes on > > the dimension tables. Go figure. > > > > -cs > > > > Sent via Deja.com > > http://www.deja.com/ > Sent via Deja.com http://www.deja.com/

Huh ? What's the use of a dimension table with rows that only 'identify' one row in a fact table ? Shirley it's the other way around ? It is useful to have unique constraints on large fact tables, not least because it helps the end user tools navigate the schema. However, it's often NOT useful to have to have an index in order to enforce these constraints. And primary keys are really only useful during data load and merge (and even then, their value is diminshed with a good data partitioning strategy). Indexes that just enforce constraints can often be an uncessary cost in this area also (it can take as much effort just to rebuild the index as it does to just check the new data against the old, but the latter doesn't need any disk space (except for sort)). In fact (no pun intended), typical b-tree style indexes can be a real pain in the proverbial on very large fact tables. The indexes can often take up more space than the data itself. Much better to enforce the constraint during data load without an index, and then only perhaps index the dimension tables for quick dimension filtering. Other techniques can then be used to access the relevant rows from the fact table, such as parallel scans etc. Bit mapped join indexes on the dimension tables that reference the corresponding fact table rows are also very very very very useful, but typically, other indexes on a lrage fact table are not. I know of a couple of large (2TB+) sites that avoid indexes at all costs > From: "Art S. Kagel" <kagel@bloomberg.net> > Organization: Bloomberg LP > Reply-To: kagel@bloomberg.net > Newsgroups: comp.databases.informix > Date: Wed, 10 Jan 2001 17:50:22 -0500 > To: cedarsiding@my-deja.com > Subject: Re: No primary keys > > Whether or not your tables use PRIMARY KEY constraints, UNIQUE constraints, > UNIQUE indexes or none at all and you just (YUCK!) maintain uniqueness > through force of will and fantastic programming, EVERY ROW IN EVERY TABLE > MUST HAVE A COMBINATION OF COLUMNS WHICH CAN BE USED TO UNIQUELY IDENTIFY > A SINGLE ROW! I guarantee your DW Fact tables and dimension tables do > indeed have a unique key even if they have no constraints or other unique > indexes on them. Otherwise they are not very useful, how would the > dimension table rows reference the fact row they identify without them? > > Art S. Kagel > > cedarsiding@my-deja.com wrote: >> >>> First of all, the terms I think you meant to use are "referenced" >>> and "referencing" tables. There is no such thing as a child and >>> parent table in SQL; those terms are from the days of pointer chains >>> in hierarchical and network databases. >>> >>> Secondly, wrong! ALL tables, to be tables, have to have a key. That >>> is a basic definition. This is very valid generic advice. >> >> Joe, >> >> Thank you for that bit of SQL indoctrination. >> >> Now I'd just like to point out that I was not talking >> about SQL nor anything specifically defined within >> that religion. I was responding to questions specifically >> about Informix databases, in which, let me assure you, >> one can define a "table" that has no "key." >> >> Please excuse me for confusing anybody that was thrown >> completely off track by my heretical use of the terms >> "child" and "parent" to express the roles in a >> referential relationship. >> >> Finally, in defense of my statement that not all "tables" >> in an Informix database might want to have a primary key >> (constraint explicitly) defined on them, consider the case >> of a fact table in a data warehouse. Typically, this >> sucker is loaded up with foreign key constraints associated >> with the surrounding dimension tables, but need not have >> a primary key explicitly created for it within the RDBMS >> (whether or not it has one conceptually is a different >> matter). >> >> I've even heard rumors that some folks don't use ANY indexes >> in their datamart/data warehouse, which I guess means they >> didn't explicitly define even the primary key indexes on >> the dimension tables. Go figure. >> >> -cs >> >> Sent via Deja.com >> http://www.deja.com/

And you definately don't need to be able to uniquely identify rows. Buy two lattes from Starbucks on the same day on the same credit card for the same price, and in a DW you would never need to find just one or the other of these rows. > From: Mark Townsend <markbtownsend@home.com> > Organization: Excite@Home - The Leader in Broadband http://home.com/faster > Newsgroups: comp.databases.informix > Date: Fri, 12 Jan 2001 03:13:48 GMT > Subject: Re: No primary keys > > Huh ? What's the use of a dimension table with rows that only 'identify' one > row in a fact table ? Shirley it's the other way around ? > > It is useful to have unique constraints on large fact tables, not least > because it helps the end user tools navigate the schema. However, it's often > NOT useful to have to have an index in order to enforce these constraints. > And primary keys are really only useful during data load and merge (and even > then, their value is diminshed with a good data partitioning strategy). > Indexes that just enforce constraints can often be an uncessary cost in this > area also (it can take as much effort just to rebuild the index as it does > to just check the new data against the old, but the latter doesn't need any > disk space (except for sort)). > > In fact (no pun intended), typical b-tree style indexes can be a real pain > in the proverbial on very large fact tables. The indexes can often take up > more space than the data itself. Much better to enforce the constraint > during data load without an index, and then only perhaps index the dimension > tables for quick dimension filtering. Other techniques can then be used to > access the relevant rows from the fact table, such as parallel scans etc. > Bit mapped join indexes on the dimension tables that reference the > corresponding fact table rows are also very very very very useful, but > typically, other indexes on a lrage fact table are not. I know of a couple > of large (2TB+) sites that avoid indexes at all costs > >> From: "Art S. Kagel" <kagel@bloomberg.net> >> Organization: Bloomberg LP >> Reply-To: kagel@bloomberg.net >> Newsgroups: comp.databases.informix >> Date: Wed, 10 Jan 2001 17:50:22 -0500 >> To: cedarsiding@my-deja.com >> Subject: Re: No primary keys >> >> Whether or not your tables use PRIMARY KEY constraints, UNIQUE constraints, >> UNIQUE indexes or none at all and you just (YUCK!) maintain uniqueness >> through force of will and fantastic programming, EVERY ROW IN EVERY TABLE >> MUST HAVE A COMBINATION OF COLUMNS WHICH CAN BE USED TO UNIQUELY IDENTIFY >> A SINGLE ROW! I guarantee your DW Fact tables and dimension tables do >> indeed have a unique key even if they have no constraints or other unique >> indexes on them. Otherwise they are not very useful, how would the >> dimension table rows reference the fact row they identify without them? >> >> Art S. Kagel >> >> cedarsiding@my-deja.com wrote: >>> >>>> First of all, the terms I think you meant to use are "referenced" >>>> and "referencing" tables. There is no such thing as a child and >>>> parent table in SQL; those terms are from the days of pointer chains >>>> in hierarchical and network databases. >>>> >>>> Secondly, wrong! ALL tables, to be tables, have to have a key. That >>>> is a basic definition. This is very valid generic advice. >>> >>> Joe, >>> >>> Thank you for that bit of SQL indoctrination. >>> >>> Now I'd just like to point out that I was not talking >>> about SQL nor anything specifically defined within >>> that religion. I was responding to questions specifically >>> about Informix databases, in which, let me assure you, >>> one can define a "table" that has no "key." >>> >>> Please excuse me for confusing anybody that was thrown >>> completely off track by my heretical use of the terms >>> "child" and "parent" to express the roles in a >>> referential relationship. >>> >>> Finally, in defense of my statement that not all "tables" >>> in an Informix database might want to have a primary key >>> (constraint explicitly) defined on them, consider the case >>> of a fact table in a data warehouse. Typically, this >>> sucker is loaded up with foreign key constraints associated >>> with the surrounding dimension tables, but need not have >>> a primary key explicitly created for it within the RDBMS >>> (whether or not it has one conceptually is a different >>> matter). >>> >>> I've even heard rumors that some folks don't use ANY indexes >>> in their datamart/data warehouse, which I guess means they >>> didn't explicitly define even the primary key indexes on >>> the dimension tables. Go figure. >>> >>> -cs >>> >>> Sent via Deja.com >>> http://www.deja.com/ >

Mark Townsend wrote in message ... >And you definately don't need to be able to uniquely identify rows. Buy two >lattes from Starbucks on the same day on the same credit card for the same >price, and in a DW you would never need to find just one or the other of >these rows. > But surely the total sales would be collated into one row? Why store two in a DW or a DM?

Maybe, but not necessarily if you are running a loyalty programme based on nbr of visits > From: "Andrew Hamm" <ahamm@sanderson.net.au> > Organization: CWO Customer - reports relating to abuse should be sent to > abuse@cwo.net.au > Newsgroups: comp.databases.informix > Date: Fri, 12 Jan 2001 15:03:31 +1100 > Subject: Re: No primary keys > > Mark Townsend wrote in message ... >> And you definately don't need to be able to uniquely identify rows. Buy two >> lattes from Starbucks on the same day on the same credit card for the same >> price, and in a DW you would never need to find just one or the other of >> these rows. >> > But surely the total sales would be collated into one row? Why store two in > a DW or a DM? > > >

Mark Townsend wrote in message ... >Maybe, but not necessarily if you are running a loyalty programme based on >nbr of visits > sounds like a single count attribute for the single row...