You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Daniel John Debrunner (JIRA)" <de...@db.apache.org> on 2006/02/15 15:28:10 UTC
[jira] Created: (DERBY-991) Defining the same primary key twice on
a table actually attempts to create two constraints.
Defining the same primary key twice on a table actually attempts to create two constraints.
--------------------------------------------------------------------------------------------
Key: DERBY-991
URL: http://issues.apache.org/jira/browse/DERBY-991
Project: Derby
Type: Bug
Versions: 10.2.0.0
Reporter: Daniel John Debrunner
Priority: Minor
Defining the same primary key twice on a table actually attempts to create two constraints.
ij> create table t ( i int, b int, primary key (i,b), primary key (i,b));
ERROR 42Z93: Constraints 'SQL060215062628851' and 'SQL060215062628850' have the
same set of columns, which is not allowed.
ij> create table t ( i int primary key primary key primary key);
ERROR 42Z93: Constraints 'SQL060214082337951' and 'SQL060214082337950' have the
same set of columns, which is not allowed.
Other combinations of two primary keys (that I could think of) return the correct error.
ij> create table t ( i int, b int, primary key (i,b), primary key (b));
ERROR 42X90: More than one primary key constraint specified for table 'T'.
ij> create table t ( i int primary key, b int primary key);
ERROR 42X90: More than one primary key constraint specified for table 'T'.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-991) Defining the same primary key twice
on a table actually attempts to create two constraints.
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-991?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12806141#action_12806141 ]
Knut Anders Hatlen commented on DERBY-991:
------------------------------------------
It works in PostgreSQL too.
> Defining the same primary key twice on a table actually attempts to create two constraints.
> --------------------------------------------------------------------------------------------
>
> Key: DERBY-991
> URL: https://issues.apache.org/jira/browse/DERBY-991
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.6
> Reporter: Daniel John Debrunner
> Priority: Minor
> Attachments: w.sql
>
>
> Defining the same primary key twice on a table actually attempts to create two constraints.
> ij> create table t ( i int, b int, primary key (i,b), primary key (i,b));
> ERROR 42Z93: Constraints 'SQL060215062628851' and 'SQL060215062628850' have the
> same set of columns, which is not allowed.
> ij> create table t ( i int primary key primary key primary key);
> ERROR 42Z93: Constraints 'SQL060214082337951' and 'SQL060214082337950' have the
> same set of columns, which is not allowed.
> Other combinations of two primary keys (that I could think of) return the correct error.
> ij> create table t ( i int, b int, primary key (i,b), primary key (b));
> ERROR 42X90: More than one primary key constraint specified for table 'T'.
> ij> create table t ( i int primary key, b int primary key);
> ERROR 42X90: More than one primary key constraint specified for table 'T'.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-991) Defining the same primary key twice
on a table actually attempts to create two constraints.
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-991?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12806089#action_12806089 ]
Dag H. Wanvik commented on DERBY-991:
-------------------------------------
This issue calls for a more correct error message, as far as I can see. Primary key(i) implies unique(i), so the unique specification is, at best, redundant. It could also be a sign of a user error and if so, swallowing it silently may not be a good thing. In my view, Hibernate should not generate this code..
What do other database than MySQL do in this case, I wonder?
> Defining the same primary key twice on a table actually attempts to create two constraints.
> --------------------------------------------------------------------------------------------
>
> Key: DERBY-991
> URL: https://issues.apache.org/jira/browse/DERBY-991
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.6
> Reporter: Daniel John Debrunner
> Priority: Minor
> Attachments: w.sql
>
>
> Defining the same primary key twice on a table actually attempts to create two constraints.
> ij> create table t ( i int, b int, primary key (i,b), primary key (i,b));
> ERROR 42Z93: Constraints 'SQL060215062628851' and 'SQL060215062628850' have the
> same set of columns, which is not allowed.
> ij> create table t ( i int primary key primary key primary key);
> ERROR 42Z93: Constraints 'SQL060214082337951' and 'SQL060214082337950' have the
> same set of columns, which is not allowed.
> Other combinations of two primary keys (that I could think of) return the correct error.
> ij> create table t ( i int, b int, primary key (i,b), primary key (b));
> ERROR 42X90: More than one primary key constraint specified for table 'T'.
> ij> create table t ( i int primary key, b int primary key);
> ERROR 42X90: More than one primary key constraint specified for table 'T'.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-991) Defining the same primary key twice on
a table actually attempts to create two constraints.
Posted by "Mike Matrigali (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-991?page=all ]
Mike Matrigali updated DERBY-991:
---------------------------------
Component: SQL
> Defining the same primary key twice on a table actually attempts to create two constraints.
> --------------------------------------------------------------------------------------------
>
> Key: DERBY-991
> URL: http://issues.apache.org/jira/browse/DERBY-991
> Project: Derby
> Type: Bug
> Components: SQL
> Versions: 10.2.0.0
> Reporter: Daniel John Debrunner
> Priority: Minor
>
> Defining the same primary key twice on a table actually attempts to create two constraints.
> ij> create table t ( i int, b int, primary key (i,b), primary key (i,b));
> ERROR 42Z93: Constraints 'SQL060215062628851' and 'SQL060215062628850' have the
> same set of columns, which is not allowed.
> ij> create table t ( i int primary key primary key primary key);
> ERROR 42Z93: Constraints 'SQL060214082337951' and 'SQL060214082337950' have the
> same set of columns, which is not allowed.
> Other combinations of two primary keys (that I could think of) return the correct error.
> ij> create table t ( i int, b int, primary key (i,b), primary key (b));
> ERROR 42X90: More than one primary key constraint specified for table 'T'.
> ij> create table t ( i int primary key, b int primary key);
> ERROR 42X90: More than one primary key constraint specified for table 'T'.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-991) Defining the same primary key twice
on a table actually attempts to create two constraints.
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-991?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12806150#action_12806150 ]
Knut Anders Hatlen commented on DERBY-991:
------------------------------------------
When I said that it worked in PostgreSQL, I was referring to the statement that combined PRIMARY KEY and UNIQUE found in Rob's comment.
The statements in Dan's original report fail in PostgreSQL too:
kah=# create table t ( i int, b int, primary key (i,b), primary key (i,b));
ERROR: multiple primary keys for table "t" are not allowed
kah=# create table t ( i int primary key primary key primary key);
ERROR: multiple primary keys for table "t" are not allowed
kah=# create table t ( i int, b int, primary key (i,b), primary key (b));
ERROR: multiple primary keys for table "t" are not allowed
kah=# create table t ( i int primary key, b int primary key);
ERROR: multiple primary keys for table "t" are not allowed
So I think there are two separate issues being discussed here:
1) What Dan originally reported, which is that Derby fails with the wrong error message for some occurrences of multiple primary keys (but it is correct to fail).
2) That columns cannot be specified as both PRIMARY KEY and UNIQUE at the same time.
Rob, please file a separate JIRA issue to track (2) if you want to see that issue addressed. Thanks.
> Defining the same primary key twice on a table actually attempts to create two constraints.
> --------------------------------------------------------------------------------------------
>
> Key: DERBY-991
> URL: https://issues.apache.org/jira/browse/DERBY-991
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.6
> Reporter: Daniel John Debrunner
> Priority: Minor
> Attachments: w.sql
>
>
> Defining the same primary key twice on a table actually attempts to create two constraints.
> ij> create table t ( i int, b int, primary key (i,b), primary key (i,b));
> ERROR 42Z93: Constraints 'SQL060215062628851' and 'SQL060215062628850' have the
> same set of columns, which is not allowed.
> ij> create table t ( i int primary key primary key primary key);
> ERROR 42Z93: Constraints 'SQL060214082337951' and 'SQL060214082337950' have the
> same set of columns, which is not allowed.
> Other combinations of two primary keys (that I could think of) return the correct error.
> ij> create table t ( i int, b int, primary key (i,b), primary key (b));
> ERROR 42X90: More than one primary key constraint specified for table 'T'.
> ij> create table t ( i int primary key, b int primary key);
> ERROR 42X90: More than one primary key constraint specified for table 'T'.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-991) Defining the same primary key twice on
a table actually attempts to create two constraints.
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-991?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas updated DERBY-991:
--------------------------------
Issue & fix info: [Newcomer, Repro attached]
Urgency: Normal
Triaged for 10.5.3: assigned normal urgency, marked as "newcomer" and "repro attached".
> Defining the same primary key twice on a table actually attempts to create two constraints.
> --------------------------------------------------------------------------------------------
>
> Key: DERBY-991
> URL: https://issues.apache.org/jira/browse/DERBY-991
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.6
> Reporter: Daniel John Debrunner
> Priority: Minor
> Attachments: w.sql
>
>
> Defining the same primary key twice on a table actually attempts to create two constraints.
> ij> create table t ( i int, b int, primary key (i,b), primary key (i,b));
> ERROR 42Z93: Constraints 'SQL060215062628851' and 'SQL060215062628850' have the
> same set of columns, which is not allowed.
> ij> create table t ( i int primary key primary key primary key);
> ERROR 42Z93: Constraints 'SQL060214082337951' and 'SQL060214082337950' have the
> same set of columns, which is not allowed.
> Other combinations of two primary keys (that I could think of) return the correct error.
> ij> create table t ( i int, b int, primary key (i,b), primary key (b));
> ERROR 42X90: More than one primary key constraint specified for table 'T'.
> ij> create table t ( i int primary key, b int primary key);
> ERROR 42X90: More than one primary key constraint specified for table 'T'.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-991) Defining the same primary key twice
on a table actually attempts to create two constraints.
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-991?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12806146#action_12806146 ]
Knut Anders Hatlen commented on DERBY-991:
------------------------------------------
See also some related information in DERBY-3300. If we attempt to create an index that's identical to an existing index, a warning is raised. That would address Dag's concerns about swallowing it silently. According to the comments in DERBY-3300, Derby supports multiple logical indexes backed by a single physical index, which could be useful for this issue too.
> Defining the same primary key twice on a table actually attempts to create two constraints.
> --------------------------------------------------------------------------------------------
>
> Key: DERBY-991
> URL: https://issues.apache.org/jira/browse/DERBY-991
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.6
> Reporter: Daniel John Debrunner
> Priority: Minor
> Attachments: w.sql
>
>
> Defining the same primary key twice on a table actually attempts to create two constraints.
> ij> create table t ( i int, b int, primary key (i,b), primary key (i,b));
> ERROR 42Z93: Constraints 'SQL060215062628851' and 'SQL060215062628850' have the
> same set of columns, which is not allowed.
> ij> create table t ( i int primary key primary key primary key);
> ERROR 42Z93: Constraints 'SQL060214082337951' and 'SQL060214082337950' have the
> same set of columns, which is not allowed.
> Other combinations of two primary keys (that I could think of) return the correct error.
> ij> create table t ( i int, b int, primary key (i,b), primary key (b));
> ERROR 42X90: More than one primary key constraint specified for table 'T'.
> ij> create table t ( i int primary key, b int primary key);
> ERROR 42X90: More than one primary key constraint specified for table 'T'.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-991) Defining the same primary key twice
on a table actually attempts to create two constraints.
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-991?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12840305#action_12840305 ]
Knut Anders Hatlen commented on DERBY-991:
------------------------------------------
No need to file a new JIRA issue for (2), as it's already tracked in DERBY-789.
> Defining the same primary key twice on a table actually attempts to create two constraints.
> --------------------------------------------------------------------------------------------
>
> Key: DERBY-991
> URL: https://issues.apache.org/jira/browse/DERBY-991
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.6
> Reporter: Daniel John Debrunner
> Priority: Minor
> Attachments: w.sql
>
>
> Defining the same primary key twice on a table actually attempts to create two constraints.
> ij> create table t ( i int, b int, primary key (i,b), primary key (i,b));
> ERROR 42Z93: Constraints 'SQL060215062628851' and 'SQL060215062628850' have the
> same set of columns, which is not allowed.
> ij> create table t ( i int primary key primary key primary key);
> ERROR 42Z93: Constraints 'SQL060214082337951' and 'SQL060214082337950' have the
> same set of columns, which is not allowed.
> Other combinations of two primary keys (that I could think of) return the correct error.
> ij> create table t ( i int, b int, primary key (i,b), primary key (b));
> ERROR 42X90: More than one primary key constraint specified for table 'T'.
> ij> create table t ( i int primary key, b int primary key);
> ERROR 42X90: More than one primary key constraint specified for table 'T'.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-991) Defining the same primary key twice on
a table actually attempts to create two constraints.
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-991?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas updated DERBY-991:
--------------------------------
Attachment: w.sql
Attaching repro, w.sql.
> Defining the same primary key twice on a table actually attempts to create two constraints.
> --------------------------------------------------------------------------------------------
>
> Key: DERBY-991
> URL: https://issues.apache.org/jira/browse/DERBY-991
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.6
> Reporter: Daniel John Debrunner
> Priority: Minor
> Attachments: w.sql
>
>
> Defining the same primary key twice on a table actually attempts to create two constraints.
> ij> create table t ( i int, b int, primary key (i,b), primary key (i,b));
> ERROR 42Z93: Constraints 'SQL060215062628851' and 'SQL060215062628850' have the
> same set of columns, which is not allowed.
> ij> create table t ( i int primary key primary key primary key);
> ERROR 42Z93: Constraints 'SQL060214082337951' and 'SQL060214082337950' have the
> same set of columns, which is not allowed.
> Other combinations of two primary keys (that I could think of) return the correct error.
> ij> create table t ( i int, b int, primary key (i,b), primary key (b));
> ERROR 42X90: More than one primary key constraint specified for table 'T'.
> ij> create table t ( i int primary key, b int primary key);
> ERROR 42X90: More than one primary key constraint specified for table 'T'.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-991) Defining the same primary key twice
on a table actually attempts to create two constraints.
Posted by "Rob Light (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-991?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12805941#action_12805941 ]
Rob Light commented on DERBY-991:
---------------------------------
Another use case where this breaks:
ij> create table t ( i int, b int, primary key (i), unique (i));
ERROR 42Z93: Constraints 'SQL100128100104821' and 'SQL100128100104820' have the same set of columns, which is not allowed.
This is using the 10.3.0 release. Will this be fixed in the next release?
FWIW, this DDL was generated by Hibernate and works under MySQL.
Thanks.
> Defining the same primary key twice on a table actually attempts to create two constraints.
> --------------------------------------------------------------------------------------------
>
> Key: DERBY-991
> URL: https://issues.apache.org/jira/browse/DERBY-991
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.2.1.6
> Reporter: Daniel John Debrunner
> Priority: Minor
> Attachments: w.sql
>
>
> Defining the same primary key twice on a table actually attempts to create two constraints.
> ij> create table t ( i int, b int, primary key (i,b), primary key (i,b));
> ERROR 42Z93: Constraints 'SQL060215062628851' and 'SQL060215062628850' have the
> same set of columns, which is not allowed.
> ij> create table t ( i int primary key primary key primary key);
> ERROR 42Z93: Constraints 'SQL060214082337951' and 'SQL060214082337950' have the
> same set of columns, which is not allowed.
> Other combinations of two primary keys (that I could think of) return the correct error.
> ij> create table t ( i int, b int, primary key (i,b), primary key (b));
> ERROR 42X90: More than one primary key constraint specified for table 'T'.
> ij> create table t ( i int primary key, b int primary key);
> ERROR 42X90: More than one primary key constraint specified for table 'T'.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.