It's an Informix 9.3 bug.

We had it in a slightly different way.
check on field_a, where field_a is not "not null"

check (field_a IN ( 'A' ,'B' ,'C' ))

It will be translated (check this with dbschema) as:
check (field_a IN (,'A' ,'B' ,'C' ))
Informix tries to add NULL, but unfortunately makes a mistake.

If you add the NULL yourself your problem will be solved:
check ((field_a IS NULL ) OR (field_a IN ('A' ,'B' ,'C' )))

Peter.



-----Oorspronkelijk bericht-----
Van: Jonathan Leffler [mailto:jleffler@earthlink.net]
Verzonden: zaterdag 20 september 2003 18:44
Aan: informix-list@iiug.org
Onderwerp: Re: informix alter table with syntax error in unix


atay wrote:

> Urgent help needed.
> I am using informix db on unix and I wanted to do something as follow
> (this script is a db2 script but I need an equvilant one for informix)
>
> create table event_table(
> appview varchar(80),
> appevent varchar(80)
> )
>
> alter table event_table add constraint primary key (appview, appevent);
>
> I keep getting syntax error at location starting at ",appevent".

The best suggestion I can make is that you add NOT NULL constraints to
the columns and try again. Judging from the SQL Syntax manual, you're
using the right notation. I fear you are running foul of bad error
reporting.

If it turns out that my diagnosis is correct, please get back to me.
I already have a bug reported in the system saying "Don't generate
-201 syntax error when a better message is possible", and I'll make
sure this one gets tracked too - if I'm right.

--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/


-------------------------------------
The information included in this message is personal and/or confidential and
intended exclusively for the addressees as stated. This message and/or the
accompanying documents may contain confidential information and should be
handled accordingly. If you are not the intended reader of this message, we
urgently request that you notify Centric immediately and that you delete
this e-mail and any copies of it from your system and destroy any printouts
immediately.
It is forbidden to distribute, reproduce, use or disclose the information in
this e-mail to third parties without obtaining prior permission from
Centric. We expressly point out that there are risks associated with the use
of e-mail. Centric and the companies within the group shall not accept any
liability whatsoever for damage resulting from the use of e-mail. Legally
binding obligations can only arise for Centric by means of a written
instrument, signed by an authorized representative of Centric.
-------------------------------------


sending to informix-list

>
> If you add the NULL yourself your problem will be solved:
> check ((field_a IS NULL ) OR (field_a IN ('A' ,'B' ,'C' )))
>

But remember that using an OR operator may significantly decrease query
performance!

Iztok Bofulin wrote:
>> If you add the NULL yourself your problem will be solved:
>> check ((field_a IS NULL ) OR (field_a IN ('A' ,'B' ,'C' )))
>>
>
> But remember that using an OR operator may significantly decrease
> query performance!

Not on a constraint especially in this circumstance. It's just an inline
expression that is applied to test the value of a field. There is no
touching the tables or performing queries when this expression is executed,
so it will run at full CPU speed in a few dozen clock cycles as expected.