Re: Primary Keys And NULL Values

On Mon, 23 Feb 1998 20:53:50 GMT, leonard_at_lf-clark.prestel.co.uk (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!
 Don't think so.
>
>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.
>

--

Regards

Matthias Gresz    :-)

GreMa_at_T-online.de