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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo [at] postgresql.org)

El Mar 28 Sep 2004 11:02, T E Schmitz escribi=F3:
> Hello,
>
> Is it possible to set up a table CHECK, which ensures that column A is
> NOT NULL if column B =3D '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 ma=
y
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=EDn Marqu=E9s | select 'mmarques' || ' [at] ' || 'unl.edu.ar'
Centro de Telematica | DBA, Programador, Administrador
Universidad Nacional
del Litoral
------------------------------------------------------------ -----

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo [at] postgresql.org)

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 [at] ~=# CREATE TABLE test (
fduch [at] ~(# a integer check (case when b = 'x' then a is not null else true end),
fduch [at] ~(# b text);
CREATE TABLE
fduch [at] ~=# INSERT INTO test VALUES (null, '123');
INSERT 107538 1
fduch [at] ~=# INSERT INTO test VALUES (null, 'x');
ERROR: new row for relation "test" violates check constraint "test_a"
fduch [at] ~=# INSERT INTO test VALUES (1, 'x');
INSERT 107539 1

--
Fduch M. Pravking

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo [at] postgresql.org)

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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

T E Schmitz <mailreg [at] 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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org