some questions ...

[totti24]

hi all, can i know how to set a column in a table to consist only certain values? i've use the check contraint when creating the table. but once i have created the table, i cant describe it. it says that my pg_relcheck doesn't exist.


i've use the ddl below

create table test
(field1 varchar(1),
field2 varchar(1) DEFAULT 'N' CHECK (field2 = 'A' OR field2 = 'R'));

please advice....

 

[newbiepg]

remove the default or keep it 'A' or 'R'

 

[totti24]

cannot coz that column can only consist of 3 values which are N,A and R. and the default value should be N. any updates will make the field change to either A or R. hope u understand what i'm trying to say here. =)

 

[newbiepg]

then the check should be
default 'N'
CHECK (field2 = 'A' OR field2 = 'R' OR field2 ='N')

however this still means that if someone changes N to A(or R) , he can still change it later to N
We make sure from the front end that the values are not rechanged to N

 

[rycamor]

Well, of course you can't set a table predicate that violates itself. The CHECK constraint is applied against all possible values in that column, including the default value.

So, you need to include the 3-value logic in your CHECK statement. I am not sure just how much logic can be encapsulated in a CHECK statement, but you might try some IF ... THEN logic.

Or, a more typical approach is to use a trigger, which runs a stored procedure. Stored procedures can handle any kind of logic you want, and can easily deal with comparing existing values to new values the user tries to enter. -------------------------------------------

My PostgreSQL FAQ --

http://brainscraps.com/faq/pg_my.html

 

[totti24]

i've created a table based on the logic provided. but i can't seem to describe my table. the error i got is "Relation pg_relcheck does not exist".