Re: ORA-1400 - mandatory (NOT NULL) column is missing or NULL during insert

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 14 Apr 1998 14:24:15 GMT
Message-ID: <3534711e.4607445@192.86.155.100>

A copy of this was sent to "Oleg Mineev" <insez_at_mail.wplus.net> (if that email address didn't require changing) On Tue, 14 Apr 1998 10:22:32 +0400, you wrote:

>How I can get the failed constraint name ?
>
>Thx.
>
>Oleg
>
>

In v7.x you can do something like the following:

SQL> create table x ( x int constraint x_not_null not null ); Table created.

SQL> insert into x values (NULL);
insert into x values (NULL)
*
ERROR at line 1:
ORA-01400: mandatory (NOT NULL) column is missing or NULL during insert

SQL> drop table x;
Table dropped.

SQL> create table x ( x int constraint x_not_null check ( x is not null ) );

Table created.

SQL> insert into x values ( NULL );
insert into x values ( NULL )
*
ERROR at line 1:
ORA-02290: check constraint (TKYTE.X_NOT_NULL) violated

See, the second create table used a check constraint instead of a NOT NULL constraint to enforce the constraint and it's name gets propagate up.

In 8.x, it becomes much simplier:

SQL> create table x ( x int constraint x_not_null not null ); Table created.

SQL> insert into x values ( null );
insert into x values ( null )

            *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("TKYTE"."X"."X")

While the constraint name doesn't get propagated, the fully qualified column name does now...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Tue Apr 14 1998 - 09:24:15 CDT