MySQL Bugs: #15627: DatabaseMetaData getImportedKeysnot returning RESTRICT

Bug #15627	DatabaseMetaData getImportedKeysnot returning RESTRICT
Submitted:	9 Dec 2005 10:52	Modified:	20 Dec 2005 20:41
Reporter:	Adam Raybone	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	3.1.12	OS:	Windows (MS Server 2003)
Assigned to:		CPU Architecture:	Any

Description:
When using JDBC’s DatabaseMetaData class to retrieve information about the foreign keys for a particular table I am getting some strange results.  DatabseMetaData has some constant field values for describing the update and delete rules for a foreign key.  The integer value of these fields are as follows

importedKeyNoAction = 3,
importedKeyRestrict =1.

However when I create two foreign keys on with ON UPDATE and ON DELETE = NO ACTION  and one with ON UPDATE and ON DELETE = RESTRICT the result set object returns the value 3 (importedKeyNoAction) fro UPDATE_RULE and DELETE_RULE for both foreign keys. As shown below.

PKTABLE_CAT : test
PKTABLE_SCHEM : null
PKTABLE_NAME : address
PKCOLUMN_NAME : AddressID
FKTABLE_CAT : test
FKTABLE_SCHEM : null
FKTABLE_NAME : Person_Address
FKCOLUMN_NAME : AddressID
KEY_SEQ : 1
UPDATE_RULE : 3
DELETE_RULE : 3
FK_NAME : FK_PA_AddressID
PK_NAME : null

PKTABLE_CAT : test
PKTABLE_SCHEM : null
PKTABLE_NAME : person
PKCOLUMN_NAME : PersonID
FKTABLE_CAT : test
FKTABLE_SCHEM : null
FKTABLE_NAME : Person_Address
FKCOLUMN_NAME : PersonID
KEY_SEQ : 1
UPDATE_RULE : 3
DELETE_RULE : 3
FK_NAME : FK_PA_PersonID
PK_NAME : null 

In addition the value for primary key name = null, this happens if I create the primary key in the create table statement or add it using the alter table statement.

We are using the following system setup

Driver Name : MySQL Connector J Version 3.1.8    
Java Version : Sun Micro Systems JDK 1.5.0_05
Operating System : Microsoft Windows Server 2003
MySQL Version : MySQL Version 4.1

Any information about what I am doing wrong or if this is a bug would be most appreciated,
Thanks
Adam

How to repeat:
The SQL to create the database and the code to reproduce the output are shown below

SQL Statments
===========
CREATE TABLE Person(
PersonID int auto_increment,
PRIMARY KEY (PersonID)
);

CREATE TABLE Address(
AddressID int auto_increment,
PRIMARY KEY (AddressID)
);

CREATE TABLE Person_Address(
PersonID2 int,
AddressID2 int
);

ALTER TABLE Person_Address ADD CONSTRAINT FK_Person FOREIGN KEY (PersonID2) REFERENCES Person(PersonID) ON UPDATE NO ACTION ON DELETE NO ACTION;
ALTER TABLE Person_Address ADD CONSTRAINT FK_Address FOREIGN KEY (AddressID2) REFERENCES Address(AddressID) ON UPDATE RESTRICT ON DELETE RESTRICT;

Java Class
========
import java.sql.*;

public class ForeignKeyTest {
    
    public ForeignKeyTest() {
            
       Connection objConn = null;
       String strDBName ="jdbc:mysql://localhost:3306/test";
       String strUserName = "root";
       String strPassword = "enter";
       String strDriverName = "com.mysql.jdbc.Driver";
       
       try
       {
            Class.forName(strDriverName);
            //get Connection
            objConn = DriverManager.getConnection(strDBName,strUserName,strPassword);
            //get Meta Data
            DatabaseMetaData objDBMetaData = objConn.getMetaData();
            //getIndexInfo from Meta Data
            ResultSet objResSet = objDBMetaData.getImportedKeys(null,null,"Person_Address");
            //get the ResultSetMetaData
            ResultSetMetaData objRSMD = objResSet.getMetaData();
            //get the total columns in ResultSetMetaData
            int intTotalColumns = objRSMD.getColumnCount();
            
            //loop through printing off the result set objects
            while(objResSet.next())
            {
                for(int i =1; i < intTotalColumns; i++)
                    System.out.println(objRSMD.getColumnName(i)+" : "+objResSet.getString(i));
                
                System.out.println("");
            }
            
            System.out.println("DatabaseMetaData Constant Value For importedKeyNoAction = "
                    +objDBMetaData.importedKeyNoAction);
            System.out.println("\nDatabaseMetaData Constant Value For importedKeyRestrict = "
                    +objDBMetaData.importedKeyRestrict);
        }
       
        catch(Exception e){
            e.printStackTrace();
        }finally{
            try
            {
                if(objConn!=null)  objConn.close();
            
            }catch(Exception e){e.printStackTrace();}
        }
    }
    //main method
    public static void main(String args[]){
            ForeignKeyTest iit = new ForeignKeyTest();
    }
}

Thank you for taking the time to report a problem.  Unfortunately
you are not using a current version of the product your reported a
problem with -- the problem might already be fixed. Please download
a new version from http://www.mysql.com/downloads/

If you are able to reproduce the bug with one of the latest versions,
please change the version on this bug report to the version you
tested and change the status back to "Open".  Again, thank you for
your continued support of MySQL.

Additional info:

Please try with version 3.1.12, 3.1.8 is quite old.

Hi Mark,
I downloaded MySQL 5.0 and connector J 3.1.12 and run the test again, I am still getting the value 3 (no action) for update and delete rules when examining a foreign key with ON UPADATE RESTRICT ON DELETE RESTRICT

Thanks for the bug report. I was able to reproduce the bug:

DatabaseMetaData Constant Value For importedKeyNoAction = 3
DatabaseMetaData Constant Value For importedKeyRestrict = 1

I tested on 3.1.12. My test case is attached.

Test case

Attachment: Bug15627.class (application/octet-stream, text), 1.85 KiB.

The driver is only reporting the metadata the server reports.

The server says you've got "NO ACTION" for those foreign keys (from "SHOW CREATE TABLE" on your testcase schema):

CREATE TABLE `Person_Address` (
  `PersonID2` int(11) default NULL,
  `AddressID2` int(11) default NULL,
  KEY `FK_Person` (`PersonID2`),
  KEY `FK_Address` (`AddressID2`),
  CONSTRAINT `FK_Address` FOREIGN KEY (`AddressID2`) REFERENCES `Address` (`AddressID`),
  CONSTRAINT `FK_Person` FOREIGN KEY (`PersonID2`) REFERENCES `Person` (`PersonID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1

However,  note that we don't consider this a bug per-se (from the manual at http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html):

"RESTRICT: Rejects the delete or update operation for the parent table. NO ACTION  and RESTRICT are the same as omitting the ON DELETE or ON UPDATE  clause. (Some database systems have deferred checks, and NO ACTION is a deferred check. In MySQL, foreign key constraints are checked immediately, so NO ACTION and RESTRICT  are the same.)"