SQL Server JDBC - JDBC Meta Data getImportedKeys returns restrict not no action

Author

JDBC Meta Data getImportedKeys returns restrict not no action

Adam_R

2005-12-09, 7:23 am

We are trying to find out about the imported keys from an SQL 2000 database's
table. However according to the DatabaseMetaData class the constant field
value for importedKeyNoAction = 3 and for importedKeyRestrict =1. when the
foreign key which we create has update and delete rules no action, we expect
a value of 3 however we are continually getting the value 1
(importedKeyRestrict
) as shown below

PKTABLE_CAT : ForeignKeyTest
PKTABLE_SCHEM : dbo
PKTABLE_NAME : table1
PKCOLUMN_NAME : column1
FKTABLE_CAT : ForeignKeyTest
FKTABLE_SCHEM : dbo
FKTABLE_NAME : table2
FKCOLUMN_NAME : column2
KEY_SEQ : 1
UPDATE_RULE : 1
DELETE_RULE : 1
FK_NAME : FK_test
PK_NAME : PK__table1__76CBA758

DatabaseMetaData Constant Value For importedKeyNoAction = 3

DatabaseMetaData Constant Value For importedKeyRestrict = 1

The sql to create the table and the java class to produce the output are below

SQL Statements
===========
CREATE TABLE table1(
column1 int not null,
PRIMARY KEY (column1)
)

CREATE TABLE table2(
column2 int not null,
)

ALTER TABLE table2 ADD CONSTRAINT FK_test FOREIGN KEY (column2) REFERENCES
table1(column1) ON UPDATE NO ACTION ON DELETE NO ACTION

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

public class ForeignKeyTest {

public ForeignKeyTest() {
Connection objConn = null;
String strDBName =" jdbc:microsoft:sqlse
rver://localhost:"+
" 1433;databaseName=Fo
reignKeyTest";
String strUserName = "tcuser";
String strPassword = "tcuser";
String strDriverName = "com.microsoft.jdbc.sqlserver.SQLServerDriver";

try
{
Class. forName(strDriverNam
e);

//get Connection
objConn =
DriverManager. getConnection(strDBN
ame,strUserName,strP
assword);

//get Meta Data
DatabaseMetaData objDBMetaData = objConn.getMetaData();

//getIndexInfo from Meta Data
ResultSet objResSet =
objDBMetaData. getImportedKeys(null
,null,"table2");

//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();
}
}

we are using the following system setup

Driver Name : com.microsoft.jdbc.sqlserver.SQLServerDriver, SP 3, Version
2.2.0040
Java Version : Sun Micro Systems JDK 1.5.0_05
Operating System : Microsoft Windows Server 2003
SQL Server Version : Microsoft SQL Server 2000 SP4

Any information about what is happening here would be gratley appriciated,
Thanks,
Adam

Sue Purkis

2005-12-21, 8:23 pm

Adam,

I see the same behavior as you reported and found that the database is
returning the correct RULE values for UPDATE and DELETE. I looked in the
JDBC spec for the values that can be returned for UPDATE_RULE and
DELETE_RULE and found the following:

1. UPDATE_RULE short => What happens to a foreign key when the primary
key is updated:

o importedNoAction - do not allow update of primary key if it has been
imported

o importedKeyCascade - change imported key to agree with primary key
update

o importedKeySetNull - change imported key to NULL if its primary key
has been updated

o importedKeySetDefaul
t - change imported key to default values if its
primary key has been updated

o importedKeyRestrict - same as importedKeyNoAction (for ODBC 2.x
compatibility)

2. DELETE_RULE short => What happens to the foreign key when primary is
deleted.

o importedKeyNoAction - do not allow delete of primary key if it has
been imported

o importedKeyCascade - delete rows that import a deleted key

o importedKeySetNull - change imported key to NULL if its primary key
has been deleted

o importedKeyRestrict - same as importedKeyNoAction (for ODBC 2.x
compatibility)

o importedKeySetDefaul
t - change imported key to default if its
primary key has been deleted

So it looks like the driver is returning a valid value by returning
importedKeyRestrict since this is in effect the same as the importedNoAction
value being returned by the DB. One solution would be to modify your app so
that you treat importedKeyRestrict and importedKeyNoAction as the same
value.

Sue Purkis

DataDirect Technologies

Adam_R

2005-12-22, 7:23 am

"Sue Purkis" wrote:

> Adam,
>
> I see the same behavior as you reported and found that the database is
> returning the correct RULE values for UPDATE and DELETE. I looked in the
> JDBC spec for the values that can be returned for UPDATE_RULE and
> DELETE_RULE and found the following:
>
>
>
> 1. UPDATE_RULE short => What happens to a foreign key when the primary
> key is updated:
>
> o importedNoAction - do not allow update of primary key if it has been
> imported
>
> o importedKeyCascade - change imported key to agree with primary key
> update
>
> o importedKeySetNull - change imported key to NULL if its primary key
> has been updated
>
> o importedKeySetDefaul
t - change imported key to default values if its
> primary key has been updated
>
> o importedKeyRestrict - same as importedKeyNoAction (for ODBC 2.x
> compatibility)
>
> 2. DELETE_RULE short => What happens to the foreign key when primary is
> deleted.
>
> o importedKeyNoAction - do not allow delete of primary key if it has
> been imported
>
> o importedKeyCascade - delete rows that import a deleted key
>
> o importedKeySetNull - change imported key to NULL if its primary key
> has been deleted
>
> o importedKeyRestrict - same as importedKeyNoAction (for ODBC 2.x
> compatibility)
>
> o importedKeySetDefaul
t - change imported key to default if its
> primary key has been deleted
>
> So it looks like the driver is returning a valid value by returning
> importedKeyRestrict since this is in effect the same as the importedNoAction
> value being returned by the DB. One solution would be to modify your app so
> that you treat importedKeyRestrict and importedKeyNoAction as the same
> value.
>
> Sue Purkis
>
> DataDirect Technologies
>

Sue,
Thanks for replying to the post, we had already altered the applciation to
treat importedKeyRestrict and importedKeyNoAction as the same. I now
unsderstand why restrict and no action return the same value.
Thanks
Adam

Sponsored Links