Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Primary Keys And NULL Values

Re: Primary Keys And NULL Values

From: Brian P. Mac Lean <brian.maclean_at_ips-sendero.com>
Date: 1998/02/24
Message-ID: <34F34A55.A0C65232@ips-sendero.com>#1/1

I thought I made my point clear but....

I am not trying to insert NULL's. I'm just trying to provide a better error message than the generic error "ORA-01400: mandatory (NOT NULL) column is missing or NULL during insert" if someone else does. This error can be for any column that someone has defined the NOT NULL constraint improperly ("field1 number not null" or "field2 number constraint field2_nn not null") and/or the primary key.

Wouldn't it be nice if we could get the more specific error "ORA-02290: check constraint (SCHEMA.CONSTRAINT_NAME) violated" instead.

Leonard F. Clark wrote:
>
> Brian,
>
> I may be missing something here but why do you want to post a null to
> a primary key column? The whole point of a pk is that it uniquely
> identifies each row (entity); by definition, a null means that you do
> not know (or care about) the value.
>
> If you must have a null in a column that is otherwise unique, I would
> suggest that the pk be a sequence or something similar and the
> nullable field be indexed with a non-unique index.
>
> In short, as I understand it, you can't do it because, by definition a
> primary key is non-nullable. Maybe I'm wrong!
>
> Len
>
> On Mon, 23 Feb 1998 11:58:55 -0700, "Brian P. Mac Lean"
> <brian.maclean_at_ips-sendero.com> wrote:
>
> >Is there any way to avoid the generic error ORA-01400 when an insert value of NULL is done on a
> >primary key. I have tried everything I can think of. Examples:
> >
> >SQL_at_CURLY:SENDERO:7.3.2:SYSTEM:8> create table fields(
> > 2 field1 number
> > 3 constraint fields_pk primary key);
> >
> >Table created.
> >
> >SQL_at_CURLY:SENDERO:7.3.2:SYSTEM:8> insert into fields values(null);
> >insert into fields values(null)
> > *
> >ERROR at line 1:
> >ORA-01400: mandatory (NOT NULL) column is missing or NULL during insert
> >
> >SQL_at_CURLY:SENDERO:7.3.2:SYSTEM:8> create table fields2(
> > 2 field1 number
> > 3 constraint fields2_pk primary key
> > 4 not null);
> >
> >Table created.
> >
> >SQL_at_CURLY:SENDERO:7.3.2:SYSTEM:8> insert into fields2 values(null);
> >insert into fields2 values(null)
> > *
> >ERROR at line 1:
> >ORA-01400: mandatory (NOT NULL) column is missing or NULL during insert
> >
> >SQL_at_CURLY:SENDERO:7.3.2:SYSTEM:8> create table fields3(
> > 2 field1 number
> > 3 constraint fields3_pk primary key
> > 4 constraint fields3_field1_nn not null);
> >
> >Table created.
> >
> >SQL_at_CURLY:SENDERO:7.3.2:SYSTEM:8> insert into fields3 values(null)
> >insert into fields3 values(null)
> > *
> >ERROR at line 1:
> >ORA-01400: mandatory (NOT NULL) column is missing or NULL during insert
> >
> >SQL_at_CURLY:SENDERO:7.3.2:SYSTEM:8> create table fields4(
> > 2 field1 number
> > 3 constraint fields4_pk primary key
> > 4 constraint fields4_field1_nn
> > 5 check(field1 is not null));
> >
> >Table created.
> >
> >SQL_at_CURLY:SENDERO:7.3.2:SYSTEM:8> insert into fields4 values(null);
> >insert into fields4 values(null)
> >*
> >ERROR at line 1:
> >ORA-01400: mandatory (NOT NULL) column is missing or NULL during insert
> >
> >Brian P. Mac Lean
> >Senior DBA/Oracle Master
> >IPS-Sendero Corporation
> >7272 E. Indian School Rd., Suite 300
> >Scottsdale, AZ 85251-3966
> >MAILTO:brian.maclean_at_sendero.fiserv.com
> >MAILTO:brian.maclean_at_ips-sendero.com
> >
> >
> >
> >"We trained hard, but it seemed that every time we were beginning to form into teams
> >we would be reorganized. I was to learn later in life that we tend to meet any new
> >situation by reorganizing, and what a wonderful method it can be for creating the
> >illusion of progress while producing confusion, inefficiency, and demoralization."
> > Petronius Arbiter 210 B.C.
Received on Tue Feb 24 1998 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US