[
https://issues.apache.org/jira/browse/DERBY-789?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12840312#action_12840312
]
Knut Anders Hatlen commented on DERBY-789:
------------------------------------------
I gave the patch a quick spin (sorry, haven't looked at the code yet).
I found one discrepancy between the description of the new behaviour and actual
behaviour.
ij> create table t1(x int primary key, constraint c unique (x));
0 rows inserted/updated/deleted
The description says that UNIQUE is quietly ignored here, but then I would have
expected the following statement to fail:
ij> alter table t1 drop constraint c;
0 rows inserted/updated/deleted
I do like the actual behaviour better than the described behaviour, though. :)
It may look like we are automatically using multiple logical indexes on top of
one physical index here, as mentioned by Dan in DERBY-3300.
(And, as you also indicated yourself might be the case, I found that ALTER
TABLE ... ADD CONSTRAINT UNIQUE was not affected by the patch and still fails
if there's a primary key on the same set of columns. We should probably try to
be consistent and allow it in ALTER TABLE too if we allow it in CREATE TABLE.)
> Usability issue: "Constraints have the same set of columns"
> -------------------------------------------------------------
>
> Key: DERBY-789
> URL: https://issues.apache.org/jira/browse/DERBY-789
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Reporter: Øystein Grøvlen
> Assignee: Bryan Pendleton
> Priority: Minor
> Attachments: PrimaryImpliesUnique.diff
>
>
> Legolas Woodland reported on derby-user that derby return errors like :
> org.apache.derby.client.am.SqlException: Constraints
> 'SQL060103004635123' and 'SQL060103004635121' have the same set of
> columns, which is not allowed.
> He got this when creating a table like this:
> create table WEBSITES (USERID integer not null unique, WEBSITEID
> bigint not null unique, DOMAINNAME varchar(255) not null unique,
> DESCRIPTION varchar(255), PPVIEW double, PPCLICK double, PPWEEK
> double, totalClick bigint, totalView bigint, active smallint, primary
> key (WEBSITEID));
> Omitting the unique specifier made things work.
> I think this is a usability issue. At least, one should not present names
> to the user, that has been generated internally. Instead, it would be
> helpful if the names of the columns involved was mentioned. I see two ways to
> solve this:
> 1. Return error that says that duplicate contraints on the following columns
> are not allowed.
> 2. Allow this and use same index for both constraints. (I guess dropping
> constraints will be more complicated in this case since one will have to
> check if other constraints are using the same index.)
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
