Unique Constraint Violation
Robin Cawsey got random unique-constraint violations on a three-column primary key although the keys looked unique. David Williams suggested violations tables (start violations table, set constraints filtering) to inspect the rejected rows. Andrew Hamm asked whether NULLs were ever inserted, since a primary key forbids them, and explained WITHOUT ERROR. David noted 9.4 also allows WITH ERROR. No root cause identified.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hi, I have come across a rather nasty error that only appears to surface at random times. I have created a table with four columns and three of the columns I have chosen to generate the Primary Key for the table (Due to the nature of the data I have not been able to use a serial field). I also have an application that inserts data into the table, (like you would). However know and again I have noticed that inserts seem to fail and the informix DB complains that the a unique constraint has been violated on the Primary key, however I have check the contents of the key and have found no reason why it should complain since the overall contents of the primary key is unique each time. Any help towards a reason why would be very welcome. Thanks Robin
"Robin Cawsey" <robin.cawsey@btinternet.com> wrote in message news:5787f5f4.0407010615.722918cf@posting.google.com... > Hi, > > serial field). I also have an application that inserts data into the > table, (like you would). However know and again I have noticed that > inserts seem to fail and the informix DB complains that the a unique > constraint has been violated on the Primary key, however I have check > the contents of the key and have found no reason why it should > complain since the overall contents of the primary key is unique each > time. > > Any help towards a reason why would be very welcome. > http://www.google.co.uk/groups?q=violations+table&hl=en&lr=&ie=UTF-8&selm=a0tm6m%24m9acq%241%40ID-79573.news.dfncis.de&rnum=1 start violations table for X; set constraints, indexes for X filtering without error; this creates two tables called X_vio and X_dia - known as the violations table and diagnostics table. Any invalid mods to X (inserts, deletes or updates) will be written to the violations and diagnostics tables, where you can inspect the bad rows at your leisure. The "set ... WITHOUT ERROR" means that any invalid mods to the table will not yield an error to the process executing the modifications. Therefore the load process will happily upload everything without seeing any errors. and http://www.google.co.uk/groups?hl=en&lr=&ie=UTF-8&threadm=teIx6.1690%24hV3.75630%40weber.videotron.net&rnum=3&prev=/groups%3Fq%3Dviolations%2Btable%26hl%3Den%26lr%3D%26ie%3DUTF-8%26selm%3DteIx6.1690%2524hV3.75630%2540weber.videotron.net%26rnum%3D3 set your constraints to filtering mode... Presumably the "without error" bit is optional? violations tables seem the way to go! > Thanks > > Robin
Robin Cawsey wrote: > > I have come across a rather nasty error that only appears to > surface at random times. I have created a table with four columns and > three of the columns I have chosen to generate the Primary Key for the > table (Due to the nature of the data I have not been able to use a > serial field). I also have an application that inserts data into the > table, (like you would). However know and again I have noticed that > inserts seem to fail and the informix DB complains that the a unique > constraint has been violated on the Primary key, however I have check > the contents of the key and have found no reason why it should > complain since the overall contents of the primary key is unique each > time. Do you ever put NULLs into the fields - even temporarily before a final update before the commit? A primary key implicitly demands NOT NULL from each field in the PK. Excellent tip from David about violations tables. The WITHOUT ERROR clause means that the application does not get given an error number indication, so it's up to the logic of the situation for you to decide if that's good or bad.
"Andrew Hamm" <ahamm@mail.com> wrote in message news:2kjn5jF364jiU1@uni-berlin.de... > Excellent tip from David about violations tables. The WITHOUT ERROR clause > means that the application does not get given an error number indication, so > it's up to the logic of the situation for you to decide if that's good or > bad. > I check the 9.4 manuals today and you can have a "WITH ERROR" clause as well so that would work! >