Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <513752542.14911267561527230.JavaMail.jira@brutus.apache.org>
Date: Tue, 2 Mar 2010 20:25:27 +0000 (UTC)
From: "Knut Anders Hatlen (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Commented: (DERBY-789) Usability issue: "Constraints have
 the  same  set of columns"
In-Reply-To: <1442413933.1136301901916.JavaMail.jira@ajax.apache.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable


    [ https://issues.apache.org/jira/browse/DERBY-789?page=3Dcom.atlassian.=
jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=3D12840=
312#action_12840312 ]=20

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 ac=
tual behaviour.=20

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 fai=
ls 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 CREA=
TE 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: =C3=98ystein Gr=C3=B8vlen
>            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.=20
> I think this is a usability issue.   At least, one should not present nam=
es to the user, that has been generated internally.  Instead, it would be h=
elpful if the names of the columns involved was mentioned. I see two ways t=
o solve this:
> 1. Return error that says that  duplicate contraints on the following col=
umns 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 c=
heck if other constraints are using the same index.)

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