Re: Duplicate serial in Informix 7.2.3

The schema is something like this: create table myTableA ( fieldA smallint not null , fieldB serial not null , ... fieldN smallint not null, unique (fieldB) ); alter table myTableA add constraint (foreign key (fieldB) references myTableB ); So I have a unique index associated to the serial, right? This is a very strange behaviour... could it be related to the fact that we're using a jurassic version of the database? On 25 Jul, 21:19, Roy Mercer <roy.mer...@gmail.com> wrote: > Please show us the schema of the table and indexes for the table that > has the serial column. > > The serial column is not unique by default. You have to enforce this > with a unique index or primary key constraint. > > If you create the table using dbaccess and the ring menu, this is done > for you. Other wise you have to create the unique index. > > You will always get a unique value if you let the table assign it, but > is someone comes along and forces an insert and you do not have a > unique index, you could get duplicates. > > RTFM > Roy > > On Jul 25, 1:18 pm, pedro <pedro.e.sa...@gmail.com> wrote: > > > Hi, > > > I've developed an webservice application which accesses an informix > > database. > > When the service is invoked, the application executes some business > > logic, opens a transaction inserts values in 3 tables (let's call them > > A, B and C), and commits it. Table A's primary key column is of type > > serial and my application sets no value to it, so the number is > > automatically generated. This is also a foreign key to table B. > > > My problem is that sometimes a duplicate serial is generated, when two > > consecutive transactions insert values in table A. I assume that this > > happens when the second transaction makes the insert before the first > > one is commited, but I'm not really sure about this. I've tried > > several isolation levels (repeatable read, read commited and > > serializable) and observed the same behaviour. We are using Informix > > 7.2.3 running under solaris 2.6. > > > Anyone can help me? > > > Thanks in advance. > > Pedro

On Jul 26, 9:16 am, pedro <pedro.e.sa...@gmail.com> wrote: > The schema is something like this: > > create table myTableA > ( > fieldA smallint not null , > fieldB serial not null , > ... > fieldN smallint not null, > unique (fieldB) > );> > alter table myTableA add constraint (foreign key (fieldB) > references myTableB );> > So I have a unique index associated to the serial, right? > This is a very strange behaviour... could it be related to the fact > that we're using a jurassic version of the database? > <SNIP> No Pedro. A FOREIGN KEY constraint creates a 'normal' index not a UNIQUE one since you can certainly logically have multiple child records for a single parent record that makes sense. You MUST have a UNIQUE or PRIMARY KEY constraint on the column (or a manually created UNIQUE index) to guarantee uniqueness. As Gumby points out SERIAL is one of the oldest Informix features and its semantics have not changed in over 20 years through many versions of seven very different servers (SE, Turbo, OnLine, DS 6.01, IDS 7.xx, XPS 8.xx, IDS 9/10/11). And Gumbe, you CAN create this kind of SERIAL table in dbaccess, just not through the menus. You can do it with a SQL DDL script at the commandline or in the Query form. Art S. Kagel

On 26 Jul, 14:58, "Art S. Kagel" <art.ka...@gmail.com> wrote: > On Jul 26, 9:16 am, pedro <pedro.e.sa...@gmail.com> wrote: > > > The schema is something like this: > > > create table myTableA > > ( > > fieldA smallint not null , > > fieldB serial not null , > > ... > > fieldN smallint not null, > > unique (fieldB) > > );> > > alter table myTableA add constraint (foreign key (fieldB) > > references myTableB );> > > So I have a unique index associated to the serial, right? > > This is a very strange behaviour... could it be related to the fact > > that we're using a jurassic version of the database? > > <SNIP> > > No Pedro. A FOREIGN KEY constraint creates a 'normal' index not a > UNIQUE one since you can certainly logically have multiple child > records for a single parent record that makes sense. You MUST have a > UNIQUE or PRIMARY KEY constraint on the column (or a manually created > UNIQUE index) to guarantee uniqueness. > > As Gumby points out SERIAL is one of the oldest Informix features and > its semantics have not changed in over 20 years through many versions > of seven very different servers (SE, Turbo, OnLine, DS 6.01, IDS 7.xx, > XPS 8.xx, IDS 9/10/11). > > And Gumbe, you CAN create this kind of SERIAL table in dbaccess, just > not through the menus. You can do it with a SQL DDL script at the > commandline or in the Query form. > > Art S. Kagel Sorry, the dbschema is not correct. This is the correct one: create table myTableA ( fieldA smallint not null , fieldB serial not null , ... fieldN smallint not null, unique (fieldB) ); alter table myTableA add constraint (foreign key (fieldA) references myTableB ); fieldA is the foreign key. However, doen't the line "unique(fieldB)" create a unique index for fieldB (which is the serial)?

pedro wrote: > On 26 Jul, 14:58, "Art S. Kagel" <art.ka...@gmail.com> wrote: >> On Jul 26, 9:16 am, pedro <pedro.e.sa...@gmail.com> wrote: >> >>> The schema is something like this: >>> create table myTableA >>> ( >>> fieldA smallint not null , >>> fieldB serial not null , >>> ... >>> fieldN smallint not null, >>> unique (fieldB) ^^^^^^ Note the UNIQUE here - on the SERIAL column. >>> ); >>> alter table myTableA add constraint (foreign key (fieldB) >>> references myTableB );>>> So I have a unique index associated to the serial, right? >>> This is a very strange behaviour... could it be related to the fact >>> that we're using a jurassic version of the database? >> <SNIP> >> >> No Pedro. A FOREIGN KEY constraint creates a 'normal' index not a >> UNIQUE one since you can certainly logically have multiple child >> records for a single parent record that makes sense. You MUST have a >> UNIQUE or PRIMARY KEY constraint on the column (or a manually created >> UNIQUE index) to guarantee uniqueness. >> >> As Gumby points out SERIAL is one of the oldest Informix features and >> its semantics have not changed in over 20 years through many versions >> of seven very different servers (SE, Turbo, OnLine, DS 6.01, IDS 7.xx, >> XPS 8.xx, IDS 9/10/11). >> >> And Gumbe, you CAN create this kind of SERIAL table in dbaccess, just >> not through the menus. You can do it with a SQL DDL script at the >> commandline or in the Query form. >> >> Art S. Kagel > > Sorry, the dbschema is not correct. This is the correct one: > > create table myTableA > ( > fieldA smallint not null , > fieldB serial not null , > ... > fieldN smallint not null, > unique (fieldB) > );> > alter table myTableA add constraint (foreign key (fieldA) > references myTableB );> > fieldA is the foreign key. However, doen't the line "unique(fieldB)" > create a unique index for fieldB (which is the serial)? Why only a UNIQUE constraint on FieldB and not a PRIMARY KEY constraint? But it doesn't matter; there is supposed to be a unique constraint on the SERIAL column, so there should not be any duplication. As to your geriatric version - yes, it could be a part of the trouble. You are aware that 7.24 is the earliest Y2K-safe version of IDS, aren't you? -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2007.0226 -- http://dbi.perl.org/