Re: Duplicate serial in Informix 7.2.3

>From: "Keith Simmons" <smiley73@googlemail.com> [SNIP]> > I'll admit I've never set up a serial column outside of dbaccess, so I've > > always had a backing index. > > (What can I say, I'm lazy... ;-) > > > > However... > > [SNIP] > >However, it you then do a manual insert of 5 the serial will take it >with no complaints, unless you have an unique index backing the serial >column. This will still happens at 9.4 and I wouldn't have thought >there would be any change to this action even at 11. > >Keith Right and I said that I've never created a serial column outside of dbaccess so I've always had the backing index. As it has already been pointed out, DBACCESS does this and enforces this behavior. Remember that the serial datatype goes way back to SE. That's roughly 20 years ago... We're getting in to a discussion about the design of SE and its intent. A serial datatype is not the same as a sequence even though they are used for the same purpose. Unlike a sequence, the serial datatype is stored in the table page itself. A sequence could be stored anywhere. (Depending on the database.) Informix's design limits one serial data type to a table. You could have multiple sequences used in a single table or a single sequence for multiple tables. But the discussion isn't about that. The only reason I bring up the sequence is that it was used to solve the same problem albiet it had different pros and cons. Informix's solution was unique and it was faster than a sequence. Now having said that, Informix could have enforced the creation of a backing index at the time of table creation. Why they didn't is beyond me, unless they figured the DBA would do this, or use the DBAccess tool which forces the issue. (In DBAccess, you can't create a serial datatype without it having an identity index (backing index, primary key)) Can Informix fix the problem and automatically generate the backing index when it processes the CREATE TABLE sql? Sure, but I figure its a low priority. If you look at the design, a serial column was designed to give you a unique value. Sequences weren't. Oh and if you look at other databases, I believe you'll find the ability to identify a column as an identity column with autogeneration and some other parameters. This is probably closer to a serial than a sequence number.... and it suffers from some of the same problems. (Although it does have a backing index when created....) Mark's example deals with a completely different issue. Its when you wrap the serial around its largest size. Its a bad example because its true for every identlty column/serial implementation. Once you wrap the size of your identity column's unsigned int's size, you're no longer going to be able to ensure unqiueness on your autogenerated value. Informix's solution was to introduce a new datatype serial8... (2^64 -1 vs. 2^32 -1) To your point, it could be considered a design defect or an implementation defect in that the CREATE TABLE statement doesn't create an identity index for the serial datatype, while it is enforced in DBACCESS. As to getting it fixed, it would be relatively simple to do, however it would be a low priority since the work around is to have the DBA create the index themselves. Which gets in to a completely different argument in the dumbing down of the logical DBA... But hey! What do I know? ;-) _________________________________________________________________ http://liveearth.msn.com

Ian Michael Gumby wrote: > Remember that the serial datatype goes way back to SE. That's roughly 20 > years ago... > We're getting in to a discussion about the design of SE and its intent. SERIAL was available in Informix 3.x long before SE (or even ISQL was available). We're talking about 1983 or at latest 1984. The SERIAL type is old enough to go and buy its own drinks - even in the USA. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2007.0226 -- http://dbi.perl.org/