Derby
  1. Derby
  2. DERBY-789

Usability issue: "Constraints have the same set of columns"

    Details

    • Type: Improvement Improvement
    • Status: Open
    • Priority: Minor Minor
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: SQL
    • Urgency:
      Low

      Description

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

        Issue Links

          Activity

          Hide
          Knut Anders Hatlen added a comment -

          I think the relevant section in SQL:2003 is 11.7 <unique constraint definition>.

          It talks about both PRIMARY KEY and UNIQUE as uniqueness constraints. Further, syntax rule 3b says:

          > The set of columns in the <unique column list> shall be distinct from the unique columns of any other
          > unique constraint descriptor that is included in the base table descriptor of T.

          So it seems allowing PRIMARY KEY and UNIQUE on the same set of columns would be a non-standard extension. It may still be useful, as it makes it easier to port applications to Derby, but we cannot expect any advice from the standard as to what's the correct behaviour for DROP CONSTRAINT on such constraints (I would argue, though, that the MySQL/PrimaryImpliesUnique.diff behaviour is more intuitive than the PostgreSQL behaviour.)

          Show
          Knut Anders Hatlen added a comment - I think the relevant section in SQL:2003 is 11.7 <unique constraint definition>. It talks about both PRIMARY KEY and UNIQUE as uniqueness constraints. Further, syntax rule 3b says: > The set of columns in the <unique column list> shall be distinct from the unique columns of any other > unique constraint descriptor that is included in the base table descriptor of T. So it seems allowing PRIMARY KEY and UNIQUE on the same set of columns would be a non-standard extension. It may still be useful, as it makes it easier to port applications to Derby, but we cannot expect any advice from the standard as to what's the correct behaviour for DROP CONSTRAINT on such constraints (I would argue, though, that the MySQL/PrimaryImpliesUnique.diff behaviour is more intuitive than the PostgreSQL behaviour.)
          Hide
          Knut Anders Hatlen added a comment -

          PostgreSQL 8.4 "forgets" the redundant constraint. That is, for the following statement

          CREATE TABLE T (X INT, CONSTRAINT PK PRIMARY KEY (X), CONSTRAINT U UNIQUE (X))

          it creates the primary key constraint PK, but not the unique constraint U. Attempts to drop the constraint U fail, because there is no such constraint. Dropping the constraint PK is enough to allow you to insert duplicates.

          MySQL 5.1 does not forget the redundant constraint. So there you are not able to insert duplicates unless you drop both the primary key constraint and the unique constraint.

          Derby with the PrimaryImpliesUnique patch behaves the same way as MySQL.

          Show
          Knut Anders Hatlen added a comment - PostgreSQL 8.4 "forgets" the redundant constraint. That is, for the following statement CREATE TABLE T (X INT, CONSTRAINT PK PRIMARY KEY (X), CONSTRAINT U UNIQUE (X)) it creates the primary key constraint PK, but not the unique constraint U. Attempts to drop the constraint U fail, because there is no such constraint. Dropping the constraint PK is enough to allow you to insert duplicates. MySQL 5.1 does not forget the redundant constraint. So there you are not able to insert duplicates unless you drop both the primary key constraint and the unique constraint. Derby with the PrimaryImpliesUnique patch behaves the same way as MySQL.
          Hide
          Dag H. Wanvik added a comment -

          So the intention here is that the redundant constraint be "forgotten" as far as I understand. What do other database do with repect to alter? If a primary constraint is dropped, would some databases let the unique constraint still apply? Is there uniformity in behavior here?

          Show
          Dag H. Wanvik added a comment - So the intention here is that the redundant constraint be "forgotten" as far as I understand. What do other database do with repect to alter? If a primary constraint is dropped, would some databases let the unique constraint still apply? Is there uniformity in behavior here?
          Hide
          Knut Anders Hatlen added a comment -

          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 );
          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.)

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

          Hi Bryan,

          Allowing both PRIMARY KEY and UNIQUE on the same set of columns sounds uncontroversial to me, unless, perhaps, if there's something in the SQL standard that says it should not be allowed.

          You may find some of the discussion in DERBY-991 and DERBY-3300 useful for this issue too.

          Show
          Knut Anders Hatlen added a comment - Hi Bryan, Allowing both PRIMARY KEY and UNIQUE on the same set of columns sounds uncontroversial to me, unless, perhaps, if there's something in the SQL standard that says it should not be allowed. You may find some of the discussion in DERBY-991 and DERBY-3300 useful for this issue too.
          Hide
          Bryan Pendleton added a comment -

          I became interested in this issue after seeing several reports that newer
          versions of certain common libraries (Hibernate version 3 was mentioned)
          are more likely to generate DDL which specifies both PRIMARY KEY and
          UNIQUE for the same columns.

          Attached is a proposal to alter this behavior.

          The patch changes the constraint analysis in TableElementList.validate.

          With the new behavior, if the user has specified both PRIMARY KEY and UNIQUE
          for the same set of columns, then the UNIQUE is quietly ignored, and only the
          PRIMARY KEY is processed.

          The patch also contains a single new test case.

          With the patch, all the existing tests still pass. However, the new behavior
          may be somewhat controversial; I suspect it would be good to have additional
          tests that explore the behavior:

          • PRIMARY KEY and UNIQUE on disjoint, but similar, sets of columns
          • multiple UNIQUE constraints on disjoing, but similar, sets of columns
          • columns specified in various orders in the constraints
          • addition and deletion of PRIMARY KEY and UNIQUE constraints
            using ALTER TABLE

          I'll try to think up some more tests to write, but suggestions for additional tests
          would be most appreciated.

          Comments about the desirability of the new behavior are also welcomed.

          Show
          Bryan Pendleton added a comment - I became interested in this issue after seeing several reports that newer versions of certain common libraries (Hibernate version 3 was mentioned) are more likely to generate DDL which specifies both PRIMARY KEY and UNIQUE for the same columns. Attached is a proposal to alter this behavior. The patch changes the constraint analysis in TableElementList.validate. With the new behavior, if the user has specified both PRIMARY KEY and UNIQUE for the same set of columns, then the UNIQUE is quietly ignored, and only the PRIMARY KEY is processed. The patch also contains a single new test case. With the patch, all the existing tests still pass. However, the new behavior may be somewhat controversial; I suspect it would be good to have additional tests that explore the behavior: PRIMARY KEY and UNIQUE on disjoint, but similar, sets of columns multiple UNIQUE constraints on disjoing, but similar, sets of columns columns specified in various orders in the constraints addition and deletion of PRIMARY KEY and UNIQUE constraints using ALTER TABLE I'll try to think up some more tests to write, but suggestions for additional tests would be most appreciated. Comments about the desirability of the new behavior are also welcomed.

            People

            • Assignee:
              Bryan Pendleton
              Reporter:
              Øystein Grøvlen
            • Votes:
              2 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:

                Development