Derby
  1. Derby
  2. DERBY-991

Defining the same primary key twice on a table actually attempts to create two constraints.

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Minor Minor
    • Resolution: Unresolved
    • Affects Version/s: 10.2.1.6
    • Fix Version/s: None
    • Component/s: SQL
    • Urgency:
      Normal
    • Issue & fix info:
      Newcomer, Repro attached

      Description

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

      1. w.sql
        0.4 kB
        Rick Hillegas

        Issue Links

          Activity

          Hide
          Rick Hillegas added a comment -

          Attaching repro, w.sql.

          Show
          Rick Hillegas added a comment - Attaching repro, w.sql.
          Hide
          Rick Hillegas added a comment -

          Triaged for 10.5.3: assigned normal urgency, marked as "newcomer" and "repro attached".

          Show
          Rick Hillegas added a comment - Triaged for 10.5.3: assigned normal urgency, marked as "newcomer" and "repro attached".
          Hide
          Rob Light added a comment -

          Another use case where this breaks:

          ij> create table t ( i int, b int, primary key , unique );
          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.

          Show
          Rob Light added a comment - Another use case where this breaks: ij> create table t ( i int, b int, primary key , unique ); 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.
          Hide
          Dag H. Wanvik added a comment -

          This issue calls for a more correct error message, as far as I can see. Primary key implies unique, 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?

          Show
          Dag H. Wanvik added a comment - This issue calls for a more correct error message, as far as I can see. Primary key implies unique , 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?
          Hide
          Knut Anders Hatlen added a comment -

          It works in PostgreSQL too.

          Show
          Knut Anders Hatlen added a comment - It works in PostgreSQL too.
          Hide
          Knut Anders Hatlen added a comment -

          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.

          Show
          Knut Anders Hatlen added a comment - 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.
          Hide
          Knut Anders Hatlen added a comment -

          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.

          Show
          Knut Anders Hatlen added a comment - 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.
          Hide
          Knut Anders Hatlen added a comment -

          No need to file a new JIRA issue for (2), as it's already tracked in DERBY-789.

          Show
          Knut Anders Hatlen added a comment - No need to file a new JIRA issue for (2), as it's already tracked in DERBY-789 .

            People

            • Assignee:
              Unassigned
              Reporter:
              Daniel John Debrunner
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:

                Development