Derby
  1. Derby
  2. DERBY-2167

Slightly misleading error message for UNIQUE constraint without NOT NULL constraint

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Trivial Trivial
    • Resolution: Won't Fix
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: Miscellaneous
    • Labels:
      None

      Description

      With UNIQUE contraint, you get an error message which indicates you also need explicit NOT NULL constraint for PRIMARY KEY constraint, which is not true . NOT NULL is implicit for PRIMARY KEY (or more precise according to the lingo in the SQL standard: PRIMARY KEY is an alternative syntax for UNIQUE NOT NULL)

      ij> create table t1 (i integer unique);
      ERROR 42831: 'I' cannot be a column of a primary key or unique key because it can contain null values.
      ij> create table t2 (i integer unique not null);
      0 rows inserted/updated/deleted
      ij> create table t3 (i integer primary key);
      0 rows inserted/updated/deleted
      ij> create table t4 (i integer primary key not null);
      0 rows inserted/updated/deleted

        Activity

        Hide
        Thomas Nielsen added a comment -

        Since there has been no feedback on this, I'm closing as "won't fix".

        Show
        Thomas Nielsen added a comment - Since there has been no feedback on this, I'm closing as "won't fix".
        Hide
        Thomas Nielsen added a comment -

        When running suites.All on a proposed fix in my sandbox, I see errorcode 42831 returned from a ALTER TABLE statement as well:

        alter table a add constraint ap1 primary key(a1, a2, a3);
        ERROR 42831: 'A1' cannot be a column of a unique key because it can contain null values.

        In this case the original errormsg actually makes sense, while the changed message does not. Either the existing errormsg should prevail, or this should become two spearate errorcodes; one for ALTER TABLE ADD CONSTRAINT ... and one for CREATE TABLE (... UNIQUE) as Bernt originally reported.

        Show
        Thomas Nielsen added a comment - When running suites.All on a proposed fix in my sandbox, I see errorcode 42831 returned from a ALTER TABLE statement as well: alter table a add constraint ap1 primary key(a1, a2, a3); ERROR 42831: 'A1' cannot be a column of a unique key because it can contain null values. In this case the original errormsg actually makes sense, while the changed message does not. Either the existing errormsg should prevail, or this should become two spearate errorcodes; one for ALTER TABLE ADD CONSTRAINT ... and one for CREATE TABLE (... UNIQUE) as Bernt originally reported.

          People

          • Assignee:
            Thomas Nielsen
            Reporter:
            Bernt M. Johnsen
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development