How to: CHECK with ROW TYPE ?

Posted in 2000

Summary

A user on IDS 9.20 wanted a CHECK constraint on individual fields of a named ROW TYPE column (e.g. ingredients.tomato IN ('Y','N')), but ALTER TABLE ... ADD CONSTRAINT kept failing with error 676 (invalid check constraint column), and constraints can't be attached to the row type itself. The fix, supplied via private mail and posted back: cast each field in the check expression, e.g. CHECK (ingredients.tomato::CHAR IN ('Y','N') AND ...). Each field must be listed individually; there's no wildcard form.

Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.

Topics: Error Codes & Troubleshooting, Data Types & Schema Design, Triggers, Constraints & Referential Integrity

Christian Haul — 14 Dec 2000 18:51:43 GMT — source: Informix-list mailing list archive (1991-1998)

Hi. consider the following table in Informix Dynamic Server 2000 Version 9.20.UC2: CREATE ROW TYPE ingr_type ( tomato char (1), cheese char (1), salami char (1), ham char (1), anchovis char (1) ); CREATE TABLE pizza ( name varchar(20), ingredients ingr_type, pid serial ); How can I specify a constraint, say that ingredient 'tomato' must be either 'Y' or 'N'? ALTER TABLE pizza ADD CONSTRAINT ( CHECK ( address.tomato in ('Y', 'N' ))); ALTER TABLE pizza ADD CONSTRAINT ( CHECK ( address.tomato in ('Y', 'N' ))); both return error 676 (invalid check constraint column) in line 3 near pos. 23 plus a "111: ISAM error: no record found." Even more, I'd like to check all at once, like (CHECK ingredients.* in ('Y', 'N')) Any ideas? Chris.

↪ replying to Christian Haul

Bogdan Neagu — Thu, 14 Dec 2000 14:19:25 -0500 — source: Informix-list mailing list archive (1991-1998)

The constraint is reffering to a table called address, no ? Christian Haul wrote: > Hi. > > consider the following table in Informix Dynamic Server 2000 Version 9.20.UC2: > > CREATE ROW TYPE ingr_type ( > tomato char (1), > cheese char (1), > salami char (1), > ham char (1), > anchovis char (1) > ); > > CREATE TABLE pizza ( > name varchar(20), > ingredients ingr_type, > pid serial > );> > How can I specify a constraint, say that ingredient 'tomato' must be > either 'Y' or 'N'? > > ALTER TABLE pizza > ADD CONSTRAINT > ( CHECK ( address.tomato in ('Y', 'N' )));> > ALTER TABLE pizza > ADD CONSTRAINT > ( CHECK ( address.tomato in ('Y', 'N' )));> > both return error 676 (invalid check constraint column) in line 3 near pos. 23 > plus a "111: ISAM error: no record found." > > Even more, I'd like to check all at once, like (CHECK ingredients.* in ('Y', 'N')) > > Any ideas? > > Chris.

↪ replying to Christian Haul

Obnoxio The Clown — Thu, 14 Dec 2000 19:49:15 GMT — source: Informix-list mailing list archive (1991-1998)

In the year of Our Lord 14 Dec 2000 18:51:43 GMT, Christian Haul <haul@informatik.tu-darmstadt.de> broke a vow of silence to utter: >consider the following table in Informix Dynamic Server 2000 Version 9.20.UC2: > >CREATE ROW TYPE ingr_type ( > tomato char (1) CHECK (tomato IN ("Y", "N") ), Something like that. It is in TFM. > cheese char (1), > salami char (1), > ham char (1), > anchovis char (1) >); > >CREATE TABLE pizza ( > name varchar(20), > ingredients ingr_type, > pid serial >);> > >How can I specify a constraint, say that ingredient 'tomato' must be >either 'Y' or 'N'? > >ALTER TABLE pizza > ADD CONSTRAINT > ( CHECK ( address.tomato in ('Y', 'N' )));> > >ALTER TABLE pizza > ADD CONSTRAINT > ( CHECK ( address.tomato in ('Y', 'N' )));> >both return error 676 (invalid check constraint column) in line 3 near pos. 23 >plus a "111: ISAM error: no record found." > >Even more, I'd like to check all at once, like (CHECK ingredients.* in ('Y', 'N')) > >Any ideas? > > Chris.

↪ replying to Obnoxio The Clown

Christian Haul — 15 Dec 2000 09:04:38 GMT — source: Informix-list mailing list archive (1991-1998)

Obnoxio The Clown <obnoxio@hotmail.com> wrote: > In the year of Our Lord 14 Dec 2000 18:51:43 GMT, Christian Haul > <haul@informatik.tu-darmstadt.de> broke a vow of silence to utter: >>consider the following table in Informix Dynamic Server 2000 Version 9.20.UC2: >> >>CREATE ROW TYPE ingr_type ( >> tomato char (1) > CHECK (tomato IN ("Y", "N") ), > Something like that. It is in TFM. Nope. Does not work here. BTW TFM says you can't attach constraints to row types themselves but have to declare them for every table that uses them. OK, I messed up the code a bit when chosing another example. So here it is again: CREATE ROW TYPE ingr_type ( tomato char (1), cheese char (1), salami char (1), ham char (1), anchovis char (1) ); CREATE TABLE pizza ( name varchar(20), ingredients ingr_type, pid serial ); insert into pizza ( name, ingredients ) values ( 'magarita', ROW ( 'Y', 'Y', 'N', 'N', 'N' )::ingr_type); insert into pizza ( name, ingredients ) values ( 'salami', ROW ( 'Y', 'Y', 'Y', 'N', 'N' )::ingr_type); select * from pizza; -> name magarita -> ingredients ROW('Y','Y','N','N','N') -> pid 4 -> -> name salami -> ingredients ROW('Y','Y','Y','N','N') -> pid 5 -> -> 2 row(s) retrieved. select * from pizza where ingredients.salami='Y'; -> name salami -> ingredients ROW('Y','Y','Y','N','N') -> pid 5 -> -> 1 row(s) retrieved. select * from pizza where ingredients.salami in ('Y','U'); -> name salami -> ingredients ROW('Y','Y','Y','N','N') -> pid 5 -> -> 1 row(s) retrieved. alter table pizza add constraint check ( tomato in ( 'Y', 'N' ) ); -> > > > -> 676: Invalid check constraint column. -> Error in line 3 -> Near character position 22 -> > alter table pizza add constraint check ( ingredients.tomato in ( 'Y', 'N' ) ); -> > > > -> 676: Invalid check constraint column. -> -> 111: ISAM error: no record found. -> Error in line 3 -> Near character position 26 alter row type ingr_type add constraint check ( tomato in ( 'Y', 'N' ) ); -> > > > -> 201: A syntax error has occurred. -> Error in line 1 -> Near character position 7 Ideally, I would like to do a "CHECK ( ingredients.* IN ( 'Y', 'N' ) )". Any ideas ? Chris.

↪ replying to Christian Haul

Obnoxio The Clown — Fri, 15 Dec 2000 10:29:41 GMT — source: Informix-list mailing list archive (1991-1998)

Sorry, I didn't read the question properly. I meant to post an apology immediately, but got interrupted by someone wanting me to do some actual work. :-/ In the year of Our Lord 15 Dec 2000 09:04:38 GMT, Christian Haul <haul@informatik.tu-darmstadt.de> broke a vow of silence to utter: >Obnoxio The Clown <obnoxio@hotmail.com> wrote: >> In the year of Our Lord 14 Dec 2000 18:51:43 GMT, Christian Haul >> <haul@informatik.tu-darmstadt.de> broke a vow of silence to utter: > >>>consider the following table in Informix Dynamic Server 2000 Version 9.20.UC2: >>> >>>CREATE ROW TYPE ingr_type ( >>> tomato char (1) > >> CHECK (tomato IN ("Y", "N") ), > >> Something like that. It is in TFM. > >Nope. Does not work here. BTW TFM says you can't attach constraints to >row types themselves but have to declare them for every table that >uses them. > >OK, I messed up the code a bit when chosing another example. So >here it is again: > >CREATE ROW TYPE ingr_type ( > tomato char (1), > cheese char (1), > salami char (1), > ham char (1), > anchovis char (1) >); > > >CREATE TABLE pizza ( > name varchar(20), > ingredients ingr_type, > pid serial >);> >insert into pizza ( name, ingredients ) values ( 'magarita', ROW ( 'Y', 'Y', 'N', 'N', 'N' )::ingr_type); >insert into pizza ( name, ingredients ) values ( 'salami', ROW ( 'Y', 'Y', 'Y', 'N', 'N' )::ingr_type);> >select * from pizza;> >-> name magarita >-> ingredients ROW('Y','Y','N','N','N') >-> pid 4 >-> >-> name salami >-> ingredients ROW('Y','Y','Y','N','N') >-> pid 5 >-> >-> 2 row(s) retrieved. > >select * from pizza where ingredients.salami='Y';> >-> name salami >-> ingredients ROW('Y','Y','Y','N','N') >-> pid 5 >-> >-> 1 row(s) retrieved. > >select * from pizza where ingredients.salami in ('Y','U');> >-> name salami >-> ingredients ROW('Y','Y','Y','N','N') >-> pid 5 >-> >-> 1 row(s) retrieved. > > >alter table pizza > add constraint > check ( tomato in ( 'Y', 'N' ) );> >-> > > > >-> 676: Invalid check constraint column. >-> Error in line 3 >-> Near character position 22 >-> > > >alter table pizza > add constraint > check ( ingredients.tomato in ( 'Y', 'N' ) );> >-> > > > >-> 676: Invalid check constraint column. >-> >-> 111: ISAM error: no record found. >-> Error in line 3 >-> Near character position 26 > > >alter row type ingr_type > add constraint > check ( tomato in ( 'Y', 'N' ) ); > >-> > > > >-> 201: A syntax error has occurred. >-> Error in line 1 >-> Near character position 7 > > >Ideally, I would like to do a "CHECK ( ingredients.* IN ( 'Y', 'N' ) )". > >Any ideas ? > > Chris.

↪ replying to Christian Haul

Christian Haul — 15 Dec 2000 15:29:32 GMT — source: Informix-list mailing list archive (1991-1998)

Christian Haul <haul@informatik.tu-darmstadt.de> wrote: > Hi. > consider the following table in Informix Dynamic Server 2000 Version 9.20.UC2: > CREATE ROW TYPE ingr_type ( > tomato char (1), > cheese char (1), > salami char (1), > ham char (1), > anchovis char (1) > ); > CREATE TABLE pizza ( > name varchar(20), > ingredients ingr_type, > pid serial > ); I got this reply in private mail, and indeed, this resolves the problem: > I think that you just need to cast the ingredients.<column> into a char > > ie, > > ALTER TABLE pizza > ADD CONSTRAINT CHECK ( ingredients.tomato::CHAR IN ("Y", "N") > AND ingredients.tomato::CHAR IN ("Y", "N") > AND ingredients.cheese::CHAR IN ("Y", "N") > AND ingredients.salami::CHAR IN ("Y", "N") > AND ingredients.ham::CHAR IN ("Y", "N") > AND ingredients.anchovis::CHAR IN ("Y", "N"))> CONSTRAINT ingredients_yn; Thanks. Well, it looks like I'll need to specify every attribute individually :-| Chris.

↪ replying to Obnoxio The Clown

Thomas Parsli — 15 Dec 2000 19:22:39 +0100 — source: Informix-list mailing list archive (1991-1998)

obnoxio@hotmail.com (Obnoxio The Clown) writes: > Sorry, I didn't read the question properly. I meant to post an apology > immediately, but got interrupted by someone wanting me to do some actual work. > :-/ Is this one of your _sick_ jokes? ..."didn't read" ... "apology" ... "actual work" ... Thomas

↪ replying to Thomas Parsli

Obnoxio The Clown — Fri, 15 Dec 2000 18:25:09 GMT — source: Informix-list mailing list archive (1991-1998)

In the year of Our Lord 15 Dec 2000 19:22:39 +0100, Thomas Parsli <thomas.parsli@startsiden.no> broke a vow of silence to utter: >obnoxio@hotmail.com (Obnoxio The Clown) writes: > >> Sorry, I didn't read the question properly. I meant to post an apology >> immediately, but got interrupted by someone wanting me to do some actual work. >> :-/ > >Is this one of your _sick_ jokes? >..."didn't read" ... "apology" ... "actual work" ... No, it's just so rare, I got all confused....

↪ replying to Obnoxio The Clown

Thomas Parsli — 15 Dec 2000 19:30:06 +0100 — source: Informix-list mailing list archive (1991-1998)

obnoxio@hotmail.com (Obnoxio The Clown) writes: > In the year of Our Lord 15 Dec 2000 19:22:39 +0100, Thomas Parsli > <thomas.parsli@startsiden.no> broke a vow of silence to utter: > > >obnoxio@hotmail.com (Obnoxio The Clown) writes: > > > >> Sorry, I didn't read the question properly. I meant to post an apology > >> immediately, but got interrupted by someone wanting me to do some actual work. > >> :-/ > > > >Is this one of your _sick_ jokes? > >..."didn't read" ... "apology" ... "actual work" ... > > No, it's just so rare, I got all confused.... Hey, I'm still confused... Thomas;)

Glossary

ISAM error