RE: Null data in not null column

From: TJ Kiernan <tkiernan_at_pti-nps.com>
Date: Tue, 15 Apr 2014 16:07:29 +0000
Message-ID: <196DB2D4BDE5804EAF3158CCC1C698BC09B62C02_at_lopez.pti-nps.com>



It's possible that the constraint was added after the data was already in the table with novalidate. Check the value for VALIDATED in the source database on DBA_CONSTRAINTS.

tkiernan_at_NPSALPHA> create table t1 (   2 c1 varchar2(5))
  3 ;

Table created.
Elapsed: 00:00:00.05
tkiernan_at_NPSALPHA> insert into t1 values (null);

1 row created.

Elapsed: 00:00:00.01
tkiernan_at_NPSALPHA> insert into t1 values values ('ABC'); insert into t1 values values ('ABC')

                      *

ERROR at line 1:
ORA-00936: missing expression

Elapsed: 00:00:00.00
tkiernan_at_NPSALPHA> insert into t1 values ('ABC');

1 row created.

Elapsed: 00:00:00.00
tkiernan_at_NPSALPHA> insert into t1 values ('DEF');

1 row created.

Elapsed: 00:00:00.00
tkiernan_at_NPSALPHA> insert into t1 values ('GHI');

1 row created.

Elapsed: 00:00:00.00
tkiernan_at_NPSALPHA> select * from t1;

C1


ABC
DEF
GHI Elapsed: 00:00:00.01
alter table t1 modify c1 not null --****Can't apply the constraint with null data **** *
ERROR at line 1:
ORA-02296: cannot enable (TKIERNAN.) - null values found

Elapsed: 00:00:00.02
tkiernan_at_NPSALPHA> alter table t1 modify c1 not null novalidate; --****Unless the constraint doesn't check the existing data ****

Table altered.

Elapsed: 00:00:00.08
tkiernan_at_NPSALPHA> select * from t1; --*** There's still a NULL value. I can't insert new NULL values or update existing records to NULL, but the existing records may stay as they are.

C1


ABC
DEF
GHI Elapsed: 00:00:00.02
tkiernan_at_NPSALPHA> select owner, constraint_type, status, validated from dba_constraints where owner='TKIERNAN' and table_name = 'T1';

OWNER                          C STATUS   VALIDATED
------------------------------ - -------- -------------
TKIERNAN                       C ENABLED  NOT VALIDATED

Elapsed: 00:00:00.78
tkiernan_at_NPSALPHA>

HTH,
T. J.
 

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ingrid Voigt Sent: Tuesday, April 15, 2014 10:54 AM
To: oracle-l_at_freelists.org
Subject: Null data in not null column

Hi,
 
one of our customers' databases has a table with a not null column containing empty data. Do you know how this can be set up?

I need to copy the table (via exp/imp) and hit either ORA-01400 (when defining not null before insert) or ORA-02296 (when I insert first and later modify the column).  

More details:

Windows 2008 R2
Oracle 11.2.0.3 + Patch 27 (January 2014)

Table definition (more columns left out):

CREATE TABLE USR_USER
(

  OID           NUMBER(15)                      NOT NULL,
  ANZEIGE_NAME  VARCHAR2(40 BYTE)               DEFAULT ''                    NOT NULL,
)
TABLESPACE USERS; And the data:

> select oid, anzeige_name, '.'||anzeige_name||'.' from usr_user;

       OID ANZEIGE_NAME                             LENGTH(ANZEIGE_NAME)
---------- ---------------------------------------- --------------------
       223
       224

(more rows left out)

These are not strings consisting of spaces, but really empty ones, i.e. nulls.

Insert into the table without specifying anzeige_name also hits ORA-01400, the condition is active. The corresponding check constraint is enabled and validated.

I'd appreciate help in reproducing this.

Thank you.
Ingrid Voigt
--
http://www.freelists.org/webpage/oracle-l

i0zX+n{+i^ Received on Tue Apr 15 2014 - 18:07:29 CEST

Original text of this message