RE: Null data in not null column
- From: TJ Kiernan <tkiernan@xxxxxxxxxxx>
- To: "'giantpanda@xxxxxxx'" <giantpanda@xxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 15 Apr 2014 16:07:29 +0000
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@NPSALPHA> create table t1 (
2 c1 varchar2(5))
3 ;
Table created.
Elapsed: 00:00:00.05
tkiernan@NPSALPHA> insert into t1 values (null);
1 row created.
Elapsed: 00:00:00.01
tkiernan@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@NPSALPHA> insert into t1 values ('ABC');
1 row created.
Elapsed: 00:00:00.00
tkiernan@NPSALPHA> insert into t1 values ('DEF');
1 row created.
Elapsed: 00:00:00.00
tkiernan@NPSALPHA> insert into t1 values ('GHI');
1 row created.
Elapsed: 00:00:00.00
tkiernan@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@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@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@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@NPSALPHA>
HTH,
T. J.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Ingrid Voigt
Sent: Tuesday, April 15, 2014 10:54 AM
To: oracle-l@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
