Re: Primary Keys And NULL Values

From: Michael Serbanescu <mserban_at_postoffice.worldnet.att.net>
Date: 1998/02/24
Message-ID: <6ctuho$qkt@bgtnsc02.worldnet.att.net>#1/1

Two possible solutions to your problem that I can think of:

1. Change your PRIMARY KEY constraints into UNIQUE constraints, which allow NULL values, and
2. Define DEFAULT values for the columns in the PRIMARY KEY constraints (I have not tested this one and I don't know if it's acceptable for your application).

Hope this helps.

Michael Serbanescu

---

Brian P. Mac Lean 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