db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-2075) XY044 error when prior table does have a unique index
Date Tue, 10 Apr 2007 18:56:32 GMT

    [ https://issues.apache.org/jira/browse/DERBY-2075?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12487880
] 

Rick Hillegas commented on DERBY-2075:
--------------------------------------

I have verified this behavior on the mainline (10.3). The error message correctly states that
Derby can't find a unique constraint on the referenced columns. Unfortunately, a unique index
is not the same thing as a unique constraint. A unique index does not satisfy the official
rules--only a unique or primary key constraint will do. This is described in the SQL Standard,
part 2, section 11.8 <referential constraint definition>, Syntax Rule (3a). Here is
an alternative script which gives the desired  behavior. This script substitutes a unique
constraint for the unique index and eliminates the redundant index which caused the earlier
warning:

CREATE TABLE PIDWHSCA
(
     SRC_ID CHAR(3) NOT NULL
   , CAS_UPC_NO CHAR(13) NOT NULL
   , LIN_NO CHAR(6) NOT NULL
   , PRIMARY KEY ( SRC_ID, CAS_UPC_NO )
   , UNIQUE ( SRC_ID, LIN_NO )
);
                  
CREATE TABLE PIDWSHPL
(
     SRC_ID CHAR(3) NOT NULL
   , LIN_NO CHAR(6) NOT NULL
   , CPN_LIN_NO CHAR(6) NOT NULL
   , UNT_QY DECIMAL(3) NOT NULL WITH DEFAULT 0
   , PRIMARY KEY ( SRC_ID, LIN_NO, CPN_LIN_NO )
);

CREATE INDEX PIDWHSCA4IX
    ON PIDWHSCA(CAS_UPC_NO ASC);

ALTER TABLE PIDWSHPL
    ADD FOREIGN KEY( SRC_ID, LIN_NO )
    REFERENCES PIDWHSCA( SRC_ID, LIN_NO );


> XY044 error when prior table does have a unique index
> -----------------------------------------------------
>
>                 Key: DERBY-2075
>                 URL: https://issues.apache.org/jira/browse/DERBY-2075
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.1.2.1
>         Environment: ------------------ Java Information ------------------
> Java Version:    1.5.0_04
> Java Vendor:     Sun Microsystems Inc.
> Java home:       C:\j2sdk1.5.0_04\jre
> Java classpath:  C:\Documents and Settings\bbonner\workspace\test2\bin;C:\eclipse\plugins\org.apache.derby.core_10.1.2\derby.jar;C:\eclipse\plugins\org.apache.derby.core_10.1.2\derbyclient.jar;C:\eclipse\plugins\org.apache.derby.core_10.1.2\derbytools.jar;C:\eclipse\plugins\org.apache.derby.core_10.1.2\derbynet.jar
> OS name:         Windows XP
> OS architecture: x86
> OS version:      5.1
> Java user name:  bbonner
> Java user home:  C:\Documents and Settings\bbonner
> Java user dir:   C:\Documents and Settings\bbonner\workspace\test2
> java.specification.name: Java Platform API Specification
> java.specification.version: 1.5
> --------- Derby Information --------
> JRE - JDBC: J2SE 5.0 - JDBC 3.0
> [C:\eclipse\plugins\org.apache.derby.core_10.1.2\derby.jar] 10.1.2.1 - (330608)
> [C:\eclipse\plugins\org.apache.derby.core_10.1.2\derbyclient.jar] 10.1.2.1 - (330608)
> [C:\eclipse\plugins\org.apache.derby.core_10.1.2\derbytools.jar] 10.1.2.1 - (330608)
> [C:\eclipse\plugins\org.apache.derby.core_10.1.2\derbynet.jar] 10.1.2.1 - (330608)
>            Reporter: Brian Bonner
>
> I execute the following DDL:
> create schema PID;
> CREATE TABLE PID.PIDWHSCA
>    ( SRC_ID                CHAR(3)            NOT NULL
>    , CAS_UPC_NO            CHAR(13)           NOT NULL
>    , LIN_NO                CHAR(6)            NOT NULL
>    , PRIMARY KEY ( SRC_ID
>                   , CAS_UPC_NO
>                   )
>                   );
>                   
>  CREATE TABLE PID.PIDWSHPL
>    ( SRC_ID                CHAR(3)            NOT NULL
>    , LIN_NO                CHAR(6)            NOT NULL
>    , CPN_LIN_NO            CHAR(6)            NOT NULL
>    , UNT_QY                DECIMAL(3)          NOT NULL WITH DEFAULT 0
>     , PRIMARY KEY ( SRC_ID
>                   , LIN_NO
>                   , CPN_LIN_NO
>                   )
>     )
> ---    IN PIDDB.PID040TS
>                                                                        ;
>                   
>                   
> CREATE  UNIQUE INDEX PID.PIDWHSCA1IX
>     ON PID.PIDWHSCA
>     (SRC_ID ASC, CAS_UPC_NO ASC)
>                                                                        ;
>   CREATE  UNIQUE INDEX PID.PIDWHSCA2IX
>     ON PID.PIDWHSCA
>     (SRC_ID ASC, LIN_NO ASC)
>                                                                        ;
>   CREATE   INDEX PID.PIDWHSCA4IX
>     ON PID.PIDWHSCA
>     (CAS_UPC_NO ASC)
>                                                                        ;
> ALTER  TABLE PID.PIDWSHPL  ADD FOREIGN KEY
>     (SRC_ID, LIN_NO)
>     REFERENCES PID.PIDWHSCA
>     (SRC_ID, LIN_NO)
>                                                                        ;
> Results in:
> ij> create schema PID;
> 0 rows inserted/updated/deleted
> ij> CREATE TABLE PID.PIDWHSCA
>    ( SRC_ID                CHAR(3)            NOT NULL
>    , CAS_UPC_NO            CHAR(13)           NOT NULL
>    , LIN_NO                CHAR(6)            NOT NULL
>    , PRIMARY KEY ( SRC_ID
>                   , CAS_UPC_NO
>                   )
>                   );
> 0 rows inserted/updated/deleted
> ij> CREATE TABLE PID.PIDWSHPL
>    ( SRC_ID                CHAR(3)            NOT NULL
>    , LIN_NO                CHAR(6)            NOT NULL
>    , CPN_LIN_NO            CHAR(6)            NOT NULL
>    , UNT_QY                DECIMAL(3)          NOT NULL WITH DEFAULT 0
>     , PRIMARY KEY ( SRC_ID
>                   , LIN_NO
>                   , CPN_LIN_NO
>                   )
>     )
> ---    IN PIDDB.PID040TS
>                                                                        ;
> 0 rows inserted/updated/deleted
> ij> CREATE  UNIQUE INDEX PID.PIDWHSCA1IX
>     ON PID.PIDWHSCA
>     (SRC_ID ASC, CAS_UPC_NO ASC)
>                                                                        ;
> 0 rows inserted/updated/deleted
> WARNING 01504: The new index is a duplicate of an existing index: SQL061113030046710.
: 
> ij> CREATE  UNIQUE INDEX PID.PIDWHSCA2IX
>     ON PID.PIDWHSCA
>     (SRC_ID ASC, LIN_NO ASC)
>                                                                        ;
> 0 rows inserted/updated/deleted
> ij> CREATE   INDEX PID.PIDWHSCA4IX
>     ON PID.PIDWHSCA
>     (CAS_UPC_NO ASC)
>                                                                        ;
> 0 rows inserted/updated/deleted
> ij> ALTER  TABLE PID.PIDWSHPL  ADD FOREIGN KEY
>     (SRC_ID, LIN_NO)
>     REFERENCES PID.PIDWHSCA
>     (SRC_ID, LIN_NO)
>                                                                        ;
> ERROR X0Y44: Constraint 'SQL061113030047960' is invalid: there is no unique or primary
key constraint on table 'PID.PIDWHSCA' that matches the number and types of the columns in
the foreign key.
> ij> 
> This doesn't seem correct.    Clearly the unique index *IS* created on the parent table.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message