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@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)=20
CHECK (expression)
The CHECK clause specifies an expression producing a Boolean result which =
new=20
or updated rows must satisfy for an insert or update operation to succeed. =
A=20
check constraint specified as a column constraint should reference that=20
column's value only, while an expression appearing in a table constraint ma=
y=20
reference multiple columns.=20
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)
--=20
11:05:01 up 16 days, 1:23, 4 users, load average: 1.26, 0.70, 1.04
------------------------------------------------------------ -----
Mart=EDn Marqu=E9s | select 'mmarques' || '@' || '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@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@~=# 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
---------------------------(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@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
> 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