Обсуждение: CHECK col A not NULL if col B='x'

Hello,

Is it possible to set up a table CHECK, which ensures that column A is 
NOT NULL if column B = 'x' ?

-- 


Regards/Gruß,

Tarlika Elisabeth Schmitz

El Mar 28 Sep 2004 11:02, T E Schmitz escribió:
> Hello,
>
> Is it possible to set up a table CHECK, which ensures that column A is
> NOT NULL if column B = 'x' ?

CONSTRAINT constraint_name ] CHECK (expression)

CHECK (expression)
The CHECK clause specifies an expression producing a Boolean result which new
or updated rows must satisfy for an insert or update operation to succeed. A
check constraint specified as a column constraint should reference that
column's value only, while an expression appearing in a table constraint may
reference multiple columns.

So I would say that it should be:

CONSTRAINT somename CHECK (B <> 'x' OR A IS NOT NULL)

(use a logical table to build the correct logical expression)

-- 11:05:01 up 16 days,  1:23,  4 users,  load average: 1.26, 0.70, 1.04
-----------------------------------------------------------------
Martín Marqués        | select 'mmarques' || '@' || 'unl.edu.ar'
Centro de Telematica  |  DBA, Programador, Administrador            Universidad Nacional                 del Litoral
-----------------------------------------------------------------

On Tue, Sep 28, 2004 at 03:02:02PM +0100, T E Schmitz wrote:
> Hello,
> 
> Is it possible to set up a table CHECK, which ensures that column A is 
> NOT NULL if column B = 'x' ?

Sure.

fduch@~=# CREATE TABLE test (
fduch@~(# a integer check (case when b = 'x' then a is not null else true end),
fduch@~(# b text);
CREATE TABLE
fduch@~=# INSERT INTO test VALUES (null, '123');
INSERT 107538 1
fduch@~=# INSERT INTO test VALUES (null, 'x');
ERROR:  new row for relation "test" violates check constraint "test_a"
fduch@~=# INSERT INTO test VALUES (1, 'x');
INSERT 107539 1

-- 
Fduch M. Pravking

Hola Martin!

Martin Marques wrote:

> El Mar 28 Sep 2004 11:02, T E Schmitz escribió:
> 
>>Is it possible to set up a table CHECK, which ensures that column A is
>>NOT NULL if column B = 'x' ?
> 
> CONSTRAINT somename CHECK (B <> 'x' OR A IS NOT NULL)

This is brilliant. Only detected this today. Don't know how I managed to 
overlook the CHECK constraints ;-)

-- 


Regards/Gruß,

Tarlika Elisabeth Schmitz

Hello again,


Martin Marques wrote:

> El Mar 28 Sep 2004 11:02, T E Schmitz escribió:
> 
>>Is it possible to set up a table CHECK, which ensures that column A is
>>NOT NULL if column B = 'x' ?
> 
> 
> CONSTRAINT somename CHECK (B <> 'x' OR A IS NOT NULL)

I noticed a table constraint can be added via ALTER TABLE. Is it correct 
that a column constraint cannot be added via the ALTER TABLE other than 
by dropping and adding the column? (I am using psql 7.4.2.)

-- 


Regards/Gruß,

Tarlika Elisabeth Schmitz

T E Schmitz <mailreg@numerixtechnology.de> writes:
> I noticed a table constraint can be added via ALTER TABLE. Is it correct 
> that a column constraint cannot be added via the ALTER TABLE other than 
> by dropping and adding the column? (I am using psql 7.4.2.)

There is no difference between table and column constraints in PG.  Just
write it as a table constraint in ALTER TABLE.
        regards, tom lane