Обсуждение: constraint problem

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
---------------------------------------------------------

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

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/

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
-------------------------------------------------