db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "A B (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-3456) Allow removing not null from collumns particpating in unique constraint.
Date Tue, 04 Mar 2008 23:03:40 GMT

    [ https://issues.apache.org/jira/browse/DERBY-3456?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12575180#action_12575180
] 

A B commented on DERBY-3456:
----------------------------

Thanks for the patch, Anurag.  It seems that with the patch applied, some conglomerate sharing
now occurs where it should not, which can lead to incorrect behavior.  For example, the following
works as expected:

  create table t1 (i int, j int not null);
  alter table t1 add constraint uc unique (i,j);
  insert into t1 values (2, 2);

  -- Should fail due to unique constraint on i, j, and it does.
  insert into t1 values (2, 2);

But if I create a non-unique index before creating the unique constraint, the unique constraint
is no longer enforced:

  create table t1 (i int, j int not null);
  create index ixnu on t1 (i,j);

  -- This new constraint UC will incorrectly share a conglomerate with "IXNU", which is non-unique...
  alter table t1 add constraint uc unique (i,j);

  insert into t1 values (2, 2);

  -- This should fail due to unique constraint on i, j, but it succeeds.
  insert into t1 values (2, 2);

  ij> select * from t1;
  I          |J
  -----------------------
  2          |2
  2          |2

2 rows selected

I think this goes back to my Feb 27th comment: namely, I think the criteria for sharing a
conglomerate has to be updated to account for the fact that we can now have non-unique conglomerates
which enforce uniqueness in the absence of NULLs.  In such a case the uniqueWhenNotNull constraint,
which is implemented using a non-unique conglomerate, canNOT share with an existing non-unique
conglomerate UNLESS the existing conglomerate is itself uniqueWhenNotNull.  At least I think
that's how it's should work; can you verify?

> Allow removing not null from collumns particpating in unique constraint.
> ------------------------------------------------------------------------
>
>                 Key: DERBY-3456
>                 URL: https://issues.apache.org/jira/browse/DERBY-3456
>             Project: Derby
>          Issue Type: Sub-task
>          Components: SQL, Store
>    Affects Versions: 10.4.0.0
>         Environment: all
>            Reporter: Anurag Shekhar
>            Assignee: Anurag Shekhar
>         Attachments: altertable.diff, derby-3456-Tests.diff, derby-3456v1.diff, derby-3456v2.diff,
derby-3456v3.diff, setnulltest.diff, upgradetests.diff, upgradetests_v2.diff
>
>


-- 
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