[
https://issues.apache.org/jira/browse/DERBY-6269?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14160661#comment-14160661
]
Mamta A. Satoor commented on DERBY-6269:
----------------------------------------
Just wanted to share that I tried the script on DB2 and got a warning there as
well.
db2 => create table t(a int not null unique, b int)
DB20000I The SQL command completed successfully.
db2 => create index t_1 on t( a )
SQL0605W The index was not created because an index
"SYSIBM.SQL141006104447770" with a matching definition already exists.
SQLSTATE=01550
> Misleading warning text if you create an index duplicating a constraint.
> ------------------------------------------------------------------------
>
> Key: DERBY-6269
> URL: https://issues.apache.org/jira/browse/DERBY-6269
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.11.1.1
> Reporter: Rick Hillegas
> Assignee: Mamta A. Satoor
> Labels: derby_triage10_11
>
> If you try to create an index on a column which defines a unique constraint,
> the index is not created. That's fine. But you get a misleading warning
> suggesting that the index was created. We should correct the warning text so
> that it says that an index was not created. We should also consider making
> this situation an error, not a warning.
> This issue is probably related to DERBY-655 and DERBY-1343.
> Here is a script which shows this problem:
> ij version 10.11
> ij> connect 'jdbc:derby:memory:db;create=true';
> ij> create table t( a int unique, b int );
> 0 rows inserted/updated/deleted
> ij> -- raises a warning suggesting that a new index has been created
> create index t_1 on t( a );
> 0 rows inserted/updated/deleted
> WARNING 01504: The new index is a duplicate of an existing index:
> SQL130618085719940.
> ij> -- only the heap and the constraint exist
> select isIndex, conglomerateName, conglomerateID from sys.sysconglomerates c,
> sys.systables t
> where t.tableid = c.tableid and t.tablename = 'T';
> ISIN&|CONGLOMERATENAME
> |CONGLOMERATEID
>
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> false|45b300a8-013f-5801-226b-000003b53310
>
> |0ddd00a9-013f-5801-226b-000003b53310
> true |SQL130618085719940
>
> |f55fc0a6-013f-5801-226b-000003b53310
> 2 rows selected
> ij> -- fails because a new index wasn't really created
> drop index t_1;
> ERROR 42X65: Index 'T_1' does not exist.
> ij> select isIndex, conglomerateName, conglomerateID from
> sys.sysconglomerates c, sys.systables t
> where t.tableid = c.tableid and t.tablename = 'T';
> ISIN&|CONGLOMERATENAME
> |CONGLOMERATEID
>
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> false|45b300a8-013f-5801-226b-000003b53310
>
> |0ddd00a9-013f-5801-226b-000003b53310
> true |SQL130618085719940
>
> |f55fc0a6-013f-5801-226b-000003b53310
> 2 rows selected
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
