Re: Constraint question

}From: uunet!kestrel.fsl.noaa.gov!bear (Bear Giles) }Subject: Re: Constraint question }Date: Fri, 11 Jun 1993 20:53:30 GMT }X-Informix-List-Id: <news.3561> } }Jack Parker wrote: }>> This is a question that _should_ easily be answered in the documentation, }>> but it was apparently written by drunken baboons. }>uneccessary. } }When two programmers experienced with databases can't find an answer after }several days of effort of tracking cross-references, there is a problem. }A big problem. } }If I alone can't find something in the documentation, I have a problem. }When several people working together can't find something, the documentation }is broken. } }>> HOW DO YOU _DEFINE_ A CONSTRANT? }>> }>> We've checked every book in our release and while we know how to }>> define a table with constraints, add constraints to an existing table, }>> remove constraints from a table, construct valid names for constraints, }>> etc., _NOWHERE_ does it say how to define one. }> }>When you add a constraint, you ARE defining one. } }Example from CREATE TABLE, from page 3-42 of the ESQL/C manual. } }create table tab1 } (c1 integer not null unique constraint uc1, } c2 integer, } c3 integer not null, } c4 char(10) not null, } unique (c3, c4)); } }elsewhere we've seen references to "constraint code" and "constraint num". } }Both of us parse that first column as: } } define a new column called "c1". c1 } this column contains integers INTEGER } do not let this column contain nulls NOT NULL } do not let this column contain duplicate entries UNIQUE } apply additional constraint CONSTRAINT UC1 Well, the last line indicates the problem. The correct interpretation of the "CONSTRAINT UC1" clause is "and call this unique constraint uc1 so that I can say 'drop constraint uc1' (as part of an alter table statement) instead of having to go to the sysconstraints table and work out what the constraint was called by the system". }We've checked the language syntax (in another book) and it states }that "constraint" must be followed by an identifier, so "uc1" isn't }simply a synonym for "c1." There are also several references to }using arbitrary constraints with a view (using a "where" clause). }It seems that "uc1" is a similar constraint applied at the relation }level, but we can't find any information on how to use it _or_ }indicating that we misinterpreted something. I will drop this note to the documentation teams, and they may clarify it in some future release of the documentation, though your list of what you found seems to cover all bases except point out that the optional CONSTRAINT clause simply gives a user-defined name instead of a system-defined name to the constrain. You don't say which version you are using -- please always say which version, because there are three versions to which you could be referring, and it may be something which has already been fixed. Yours, Jonathan Leffler (johnl@obelix.informix.com) #include <disclaimer.h>