Re: Primary Keys And NULL Values

If you are trying to enforce an optional foreign key constraint, you might try to have part of the primary key nullable, producing this error number.

The solution to that problem is to define a normal key (fabricate numbers if nothing else will do) and make the offending nullable column a FOREIGN KEY to the "other" table.

That way, if you choose not to supply a value for that column, no referential constraint will be enforced. However, if you supply a value, then that value must exist in the "other" table.

Hope this makes sense, its hard to explain in text ;-)

Andrew Pejta

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