Re: Does anyone know (How Informix choose a constraint name)?
The first number in the constraint name is the value of SysTables.TabID
for the table. The other number corresponds to the constraint id
(SysConstraints.ConstrID), unless I am guessing wrong -- I only did a
little bit of testing.
If you want to control the constraint names, you create them yourself:
CREATE TABLE aaa (..., UNIQUE (col1) CONSTRAINT u1_aaa);
If you use any of the names generated by Informix, you are liable to run
into name collisions because Informix thinks you won't use it naming
convention! I'd guess that you have a constraint already in existence
with the name u303_172 on some table which does not have TabID of 303.
I prefer to see all constraints named by the creator. Your unique
constraint is probably the primary key and should be declared as such.
CREATE TABLE aaa (col1 CHAR(2) NOT NULL PRIMARY KEY CONSTRAINT pk_aaa, ...);
Unless you are using a 4.1x engine, primary keys are supported for sure; I
can't remember off-hand whether they were available in 4.1x, but I think
not, though unique constraints were added at that level (again, if I
remember correctly; this is from 1990 or earlier).
Yours,
Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>
}From: zsun@bx00dbss.med.osd.mil
}Date: Wed, 6 Mar 1996 19:12:23 -0500 (EST)
}X-Informix-List-Id: <list.8917>
}
}One of our database has a table aaa, and if we run the following in dbaccess:
}
} drop table aaa;
} create table aaa
} ( col1 char(2) not null,
} col2 char(25),
} col3 char(1),
} unique (col1));
}
}we get the 625 error( Constraint name u303_172 already exists.) Somehow Informix
}wants to use "u303_172" as the constraint name, but "u303_172" already belongs
}to table bbb. We know if we specify a constraint name in our script then we
}will not have this problem, in fact if we run the exact same script the second
}time, it works too, Informix creates a constraint "u304_173", Both table aaa
}and bbb are owned by Informix.
}So now we have these questions: how does Informix choose a constraint name if we
}don't specify one? Why can't it figure out the name it wants has already been
}used and why it works the second time? Or is there something wrong with our
}database (it sure does not look like, we have done all kinds of tbcheck we can
}think of, but anything is possible) and if so, what do we need to do to fix it?
}I searched through the Informix bug list and did not find anything similar.
}
}Has anyone else seen this before? Or does anyone have any idea? (We are using
}OnLine 5.02UC3 on SCO.)
}
}Thanks in advance
}Zhenling Sun
}zsun@bx00dbss.med.osd.mil