Null data in not null column
From: Ingrid Voigt <giantpanda_at_gmx.net>
Date: Tue, 15 Apr 2014 17:53:58 +0200
Message-ID: <trinity-ac092608-ab1c-4262-8224-be0291748366-1397577237924_at_3capp-gmx-bs04>
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?
TABLESPACE USERS; And the data:
(more rows left out)
Date: Tue, 15 Apr 2014 17:53:58 +0200
Message-ID: <trinity-ac092608-ab1c-4262-8224-be0291748366-1397577237924_at_3capp-gmx-bs04>
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-lReceived on Tue Apr 15 2014 - 17:53:58 CEST