Oninit cdi / Archive
Talk to an expert →

Re: How to: CHECK with ROW TYPE ?

Posted in 2000

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

Richard harnden — — source: Informix-list mailing list archive (1991-1998)

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; >>> Christian Haul <haul@informatik.tu-darmstadt.de> 12/15 9:04 am >>> 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 Richard harnden
Jonathan Leffler — — source: Informix-list mailing list archive (1991-1998)

Richard harnden wrote: > 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; Another option would be to use BOOLEAN columns with NOT NULL constraints in place of CHAR(1); you can only have 'T' (true) and 'F' (false) in such columns, so an explicit constraint would not be necessary. You might want to do mapping of T/F to Y/N during input and output, of course. > >>> Christian Haul <haul@informatik.tu-darmstadt.de> 12/15 9:04 am >>> > 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 > );> >[...major snippage...] -- Yours, Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN "I don't suffer from insanity; I enjoy every minute of it!"

Glossary