Non-check constraint def for a static list ?

On Tue, Sep 15, 2009 at 08:01:19AM -0700, Gauthier, Dave wrote:

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)

If this is to do with your similar questions from earlier; at what point
does the data become valid and why don't you just delay putting the data
into the "real" table at then.

--
Sam http://samason.me.uk/

CHECK( foo IN ('bar1', 'bar2'));

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@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

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