Обсуждение: 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