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] Commented: (DERBY-2075) XY044 error when prior table does have a unique index
Date Tue, 10 Apr 2007 19:09:32 GMT

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

Brian Bonner commented on DERBY-2075:
-------------------------------------

Rick, can you point me to the SQL Standard document to which you are referring here:  SQL
Standard, part 2, section 11.8?  Thanks.

> 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