You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Rick Hillegas (JIRA)" <ji...@apache.org> on 2007/04/10 20:58:32 UTC
[jira] Closed: (DERBY-2075) XY044 error when prior table does have
a unique index
[ https://issues.apache.org/jira/browse/DERBY-2075?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas closed DERBY-2075.
--------------------------------
Resolution: Fixed
Closing this issue because it is not a bug.
> 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.