db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Brian Bonner (JIRA)" <j...@apache.org>
Subject [jira] Created: (DERBY-2075) XY044 error when prior table does have a unique index
Date Mon, 13 Nov 2006 20:13:37 GMT
XY044 error when prior table does have a unique index
-----------------------------------------------------

                 Key: DERBY-2075
                 URL: http://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.
-
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

        

Mime
View raw message