db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (JIRA)" <j...@apache.org>
Subject [jira] Created: (DERBY-3630) Odd assymetry in interaction of unique and not null constraints
Date Thu, 17 Apr 2008 21:03:21 GMT
Odd assymetry in interaction of unique and not null constraints
---------------------------------------------------------------

                 Key: DERBY-3630
                 URL: https://issues.apache.org/jira/browse/DERBY-3630
             Project: Derby
          Issue Type: Improvement
    Affects Versions: 10.4.1.3
            Reporter: Rick Hillegas
            Priority: Minor


If you constrain a column to be both "not null" and "unique", then Derby creates an old-style
unique index on the column. If you later remove the "not null" constraint, Derby converts
the index to be a new-style unique-with-duplicate-nulls index.

However, if you constrain a column to be "unique" and then later add a "not null" constraint,
then Derby does not convert the unique-with-duplicate-nulls index into an old-style unique
index.

It seems that the order in which you add these constraints affects the metadata. I don't know
if these differences affect the optimizer's decisions.

Here is a script which shows this behavior:

drop table v;
0 rows inserted/updated/deleted
ij> create table v
(
   a int,
   unique ( a )
);
0 rows inserted/updated/deleted
ij> drop table w;
0 rows inserted/updated/deleted
ij> create table w
(
   m int not null,
   unique ( m )
);
0 rows inserted/updated/deleted
ij> select cast( t.tableName as varchar( 10 )), cast (c.conglomeratename as varchar( 30
)), c.descriptor
from sys.sysconglomerates c, sys.systables t
where c.tableid = t.tableid
and ( t.tablename = 'V'  or t.tablename='W')
and c.isconstraint
;
1         |2                             |DESCRIPTOR     
---------------------------------------------------------
V         |SQL080417133332230            |UNIQUE WITH DU&
W         |SQL080417133332330            |UNIQUE BTREE (&

2 rows selected
ij> alter table v
  alter column a not null
;
0 rows inserted/updated/deleted
ij> alter table w
  alter column m null
;
0 rows inserted/updated/deleted
ij> select cast( t.tableName as varchar( 10 )), cast (c.conglomeratename as varchar( 30
)), c.descriptor
from sys.sysconglomerates c, sys.systables t
where c.tableid = t.tableid
and ( t.tablename = 'V'  or t.tablename='W')
and c.isconstraint
;
1         |2                             |DESCRIPTOR     
---------------------------------------------------------
V         |SQL080417133332230            |UNIQUE WITH DU&
W         |SQL080417133332330            |UNIQUE WITH DU&

2 rows selected


-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message