Re: Primary Keys And NULL Values

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 Mon Feb 23 1998 - 00:00:00 CST