Postgres Pro
Downloads
Home   >   mailing lists

Thread: constraint problem

constraint problem

From
Martín Marqués
Date:
I have a table with a login, password and confirmed columns (besides others),
and I'm having so trouble getting this contraint to work.

The account is created with login and password NULL and confirmed set to
false. Once the user gives the app he's login and password (login is unique)
the system updates that row with the new login, password and sets confirmed
to true.

Now to the constraint:

I don't want the login and password columns to have nulls when the account
(row) is confirmed (confirmed column is set to true).

I tried adding this CONSTRAINT to the table definition, but with no luck:

    CONSTRAINT nonuloconfirmado CHECK
        ((login NOT NULL AND password NOT NULL) OR NOT confirmado)

It gives an error on the first NULL.

How can this be solved?

--
select 'mmarques' || '@' || 'unl.edu.ar' AS email;
---------------------------------------------------------
Martín Marqués          |   Programador, DBA
Centro de Telemática    |     Administrador
               Universidad Nacional
                    del Litoral
---------------------------------------------------------

Re: constraint problem

From
Stephan Szabo
Date:
On Mon, 25 Jul 2005, [iso-8859-1] Martín Marqués wrote:

> I have a table with a login, password and confirmed columns (besides others),
> and I'm having so trouble getting this contraint to work.
>
> The account is created with login and password NULL and confirmed set to
> false. Once the user gives the app he's login and password (login is unique)
> the system updates that row with the new login, password and sets confirmed
> to true.
>
> Now to the constraint:
>
> I don't want the login and password columns to have nulls when the account
> (row) is confirmed (confirmed column is set to true).
>
> I tried adding this CONSTRAINT to the table definition, but with no luck:
>
>     CONSTRAINT nonuloconfirmado CHECK
>         ((login NOT NULL AND password NOT NULL) OR NOT confirmado)
>
> It gives an error on the first NULL.
>
> How can this be solved?

I think you'd want
(login IS NOT NULL AND password IS NOT NULL) OR NOT confirmado

Re: constraint problem

From
Michael Fuhr
Date:
On Mon, Jul 25, 2005 at 08:28:32PM -0300, Martín Marqués wrote:
> I tried adding this CONSTRAINT to the table definition, but with no luck:
>
>     CONSTRAINT nonuloconfirmado CHECK
>         ((login NOT NULL AND password NOT NULL) OR NOT confirmado)
>
> It gives an error on the first NULL.

Use IS NOT NULL instead of NOT NULL.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Re: constraint problem

From
Martín Marqués
Date:
El Lun 25 Jul 2005 23:29, Stephan Szabo escribió:
> >
> > Now to the constraint:
> >
> > I don't want the login and password columns to have nulls when the account
> > (row) is confirmed (confirmed column is set to true).
> >
> > I tried adding this CONSTRAINT to the table definition, but with no luck:
> >
> >     CONSTRAINT nonuloconfirmado CHECK
> >         ((login NOT NULL AND password NOT NULL) OR NOT confirmado)
> >
> > It gives an error on the first NULL.
> >
> > How can this be solved?
>
> I think you'd want
> (login IS NOT NULL AND password IS NOT NULL) OR NOT confirmado

<banning my head to the wall>
HOW COULD I HAVE MISSED THAT!!!
</banning my head to the wall>

I feel so stupid today. :-(

--
 07:31:18 up 23 days, 16:16,  1 user,  load average: 1.09, 0.96, 0.74
-------------------------------------------------
Lic. Martín Marqués   | SELECT 'mmarques' ||
Centro de Telemática  |     '@' || 'unl.edu.ar';
Universidad Nacional  | DBA, Programador,
    del Litoral       | Administrador
-------------------------------------------------