Re: Non-check constraint def for a static list ?

On Tue, Sep 15, 2009 at 08:01:19AM -0700, Gauthier, Dave wrote:
> Hi:
>
> How could I define a constraint on a colum who's value I want to
> restrict to a static list of strings? For example, a column colled
> "gender" which cannot be NULL and must be either 'M' or 'F'. I can
> do it with a "check" constraint, but I can't defer those constraints
> (the reason I'm asking for a non-check soln)

You can make it a foreign key to another table consisting of a single
column which is its primary key. In biology and other fields,
"gender" can take many values.

If you wish to make the table immutable, revoking all write
permissions, and as a backstop, adding RULEs that say DO INSTEAD
NOTHING for data-changing operations, which would then require that
someone changing it have DDL permission, a much higher bar.

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate