[
https://issues.apache.org/jira/browse/DERBY-3630?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Mamta A. Satoor updated DERBY-3630:
-----------------------------------
Urgency: Normal
Labels: derby_triage10_10 (was: )
> 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
> Components: SQL
> Affects Versions: 10.4.1.3
> Reporter: Rick Hillegas
> Priority: Minor
> Labels: derby_triage10_10
>
> 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.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
