MySQL Bugs: #1731: Foreign Keys column sequence is not consistent

Bug #1731	Foreign Keys column sequence is not consistent
Submitted:	1 Nov 2003 8:54	Modified:	19 Nov 2003 9:55
Reporter:	Xavier Maysonnave	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	3.0.9	OS:	Any (All)
Assigned to:	Mark Matthews	CPU Architecture:	Any

Description:
Primary key and Index key column starts their numbering position from 1 to n

Foreign key column position starts from 0 to n

How to repeat:
define an InnoDB table with a defined foreign key.

drop table if exists COUNTRY;

CREATE TABLE COUNTRY
(
    COUNTRY VARCHAR (14) NOT NULL,
    CURRENCY VARCHAR (10) NOT NULL,
    PRIMARY KEY(COUNTRY)
) Type=InnoDB;
                
drop table if exists CUSTOMER;

CREATE TABLE CUSTOMER
(
    CUST_NO INTEGER NOT NULL,
    CUSTOMER VARCHAR (25) NOT NULL,
    CONTACT_FIRST VARCHAR (15),
    CONTACT_LAST VARCHAR (20),
    PHONE_NO VARCHAR (20),
    ADDRESS_LINE1 VARCHAR (30),
    ADDRESS_LINE2 VARCHAR (30),
    CITY VARCHAR (25),
    STATE_PROVINCE VARCHAR (15),
    COUNTRY VARCHAR (15),
    POSTAL_CODE VARCHAR (12),
    ON_HOLD CHAR (1),
    PRIMARY KEY(CUST_NO),
    UNIQUE (CUSTOMER),
    UNIQUE (COUNTRY, CITY),
    UNIQUE (COUNTRY)
) Type=InnoDB;

ALTER TABLE CUSTOMER
    ADD CONSTRAINT CUSTOMER_FK_1 FOREIGN KEY (COUNTRY)
    REFERENCES COUNTRY (COUNTRY);

when you query the meta data, the primary key column COUNTRY is position 1 and the foreign key is position 0.

Suggested fix:
Class :

com.mysql.jdbc.DatabaseMetaData

Method :

private void getResultsImpl(String catalog, String table, String keysComment, List tuples, String fkTableName, boolean isExport)

Line 6646 : 

int keySeqIndex = 0;

 could be changed to 

int keySeqIndex = 1;

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Thank you for your bug report. This issue is now fixed.