Derby
  1. Derby
  2. DERBY-3630

Odd assymetry in interaction of unique and not null constraints

    Details

    • Type: Improvement Improvement
    • Status: Open
    • Priority: Minor Minor
    • Resolution: Unresolved
    • Affects Version/s: 10.4.1.3
    • Fix Version/s: None
    • Component/s: SQL
    • Urgency:
      Normal

      Description

      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

        Activity

        Hide
        Mike Matrigali added a comment -

        I think it would be better if in any case when it is known that all columns are not-null that old style index is used.
        This index will always either perform the same or better than the new index in cases where only non-null data is present.
        Basically these two factors allow the index to not execute extra special case checking code. Any constrained column
        that is nullable must be the new style index.

        You may want to add to your test case to make sure that resulting index actually does what is expected both the expected error case for existing rows in the table and the subsequent expected
        error case for inserting subsequent rows. In the above case W should fail if there are any null's already in table, and
        should fail if trying to insert any null's. It may work, I just know the intent of the final code was the new index was just
        going to handle the unique, null case.

        Show
        Mike Matrigali added a comment - I think it would be better if in any case when it is known that all columns are not-null that old style index is used. This index will always either perform the same or better than the new index in cases where only non-null data is present. Basically these two factors allow the index to not execute extra special case checking code. Any constrained column that is nullable must be the new style index. You may want to add to your test case to make sure that resulting index actually does what is expected both the expected error case for existing rows in the table and the subsequent expected error case for inserting subsequent rows. In the above case W should fail if there are any null's already in table, and should fail if trying to insert any null's. It may work, I just know the intent of the final code was the new index was just going to handle the unique, null case.

          People

          • Assignee:
            Unassigned
            Reporter:
            Rick Hillegas
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:

              Development