Are default values supported in Oracle for columns with char semantics?

Question

I was trying to add a column to a table but got a surprising effect with the DEFAULT clause. In a table with existing rows, I added a new column like so:

alter table t add c char(1 char) default 'N' not null;

When I subsequently added a check constraint to the table, it failed:

alter table t add constraint chk check(c in ('N', 'Y'));

Which resulted in

ERROR at line 1:
ORA-02293: cannot validate (T.CHK) - check constraint violated.

Other info:

1. Because I'm setting the units explicitly (i.e., char(1 char) as opposed to just char(1)), I don't expect the value of nls_length_semanatics to be relevant.
2. After adding the column as char(1 char), the newly added "N"s are actually "N " and I'm not sure what the extra whitespace is.
3. Adding the column as char(1 byte) works as expected;
4. Adding the column without the "default 'N' not null" followed by updating all existing rows to 'N', followed by altering the column to 'not null' also works as expected.
5. NLS_CHARACTERSET is AL32UTF8, but I don't expect that to be relevant either.
6. Database is Oracle 11g; 11.2.0.1.0.

Thanks.

Answer 1

I believe that what you're seeing is a bug that relies on a couple different things interacting

• First, the database character set has to be a variable width character set (i.e. AL32UTF8) so that a single character may require up to four bytes of storage.
• Second, the column must be declared with character length semantics
• Third, starting in 11.1, Oracle added an optimization so that if you add a column to the table that is declared NOT NULL and that has a DEFAULT that Oracle could do that simply by updating the data dictionary rather than actually storing the default value in every row of the table.

When both of those things are true, it appears that the value that is returned has a length of 4 and is padded with the CHR(0) character.

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> create table foo( col1 number );

Table created.

SQL> insert into foo values( 1 );

1 row created.

SQL> commit;

Commit complete.

SQL> alter table foo add c char(1 char) default 'N' not null;

Table altered.

SQL> alter table foo add constraint chk_foo check( c in ('Y', 'N') );
alter table foo add constraint chk_foo check( c in ('Y', 'N') )
                               *
ERROR at line 1:
ORA-02293: cannot validate (SCOTT.CHK_FOO) - check constraint violated

SQL> select c, dump(c) from foo;

C    DUMP(C)
---- ------------------------------
N    Typ=1 Len=4: 78,0,0,0

If you actually force the value to be stored in the table, you'll get the expected behavior where there is no CHR(0) padding. So if I insert a new row into the table, it passes.

SQL> insert into foo(col1) values (2);

1 row created.

SQL> select c, dump(c) from foo;

C    DUMP(C)
---- ------------------------------
N    Typ=1 Len=4: 78,0,0,0
N    Typ=1 Len=1: 78

You could also issue an UPDATE to update the rows that aren't actually storing the value in the rows of the table

SQL> update foo
  2     set c = 'N'
  3   where c != 'N';

1 row updated.

SQL> select c, dump(c) from foo;

C    DUMP(C)
---- ------------------------------
N    Typ=1 Len=1: 78
N    Typ=1 Len=1: 78

Answer 2

You tagged oracle11g, but you didn't specify a version.

This works for me on 11.2.0.2 on Linux x86-64.

SQL*Plus: Release 11.2.0.2.0 Production on Mon Mar 26 13:13:52 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management and OLAP options

SQL> create table tt(a number);

Table created.

SQL> insert into tt values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> alter table tt add c char(1 char) default 'N' not null;

Table altered.

SQL> alter table tt add constraint chk check(c in('N','Y'));

Table altered.

SQL> select * from tt;

     A C
---------- -
     1 N

SQL> column dump(c) format a30
SQL> select c, length(c),dump(c) from tt;

C  LENGTH(C) DUMP(C)
- ---------- ------------------------------
N      1 Typ=96 Len=1: 78

So....perhaps you have a bug in your version?

Hope that helps.

Answer 3

The reason for the ORA-02293 error, as you've already mentioned, is because it is inserting 'N ' (with a padded white space) rather than 'N'. So your constraint is violated.

The more interesting question is, why is it adding that space? Well, by definition, a CHAR is fixed width, where a VARCHAR is not. A CHAR will always pad white space to fill the entire memory space allocated for the column. Because you've chosen a width of 1 CHAR, and AL32UTF8 is a varying width character set, that would seem to conflict with the fixed width nature of CHAR. It looks like it gets padded to fill the extra byte(s) not used by the 'N'. Or, at least, I assume that is what is happening.