JDBC Meta Data getImportedKeys returns restrict not no action
- From: Adam_R <AdamR@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 9 Dec 2005 01:26:02 -0800
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:sqlserver://localhost:"+
"1433;databaseName=ForeignKeyTest";
String strUserName = "tcuser";
String strPassword = "tcuser";
String strDriverName = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
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,"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
.
- Follow-Ups:
- Re: JDBC Meta Data getImportedKeys returns restrict not no action
- From: Sue Purkis
- Re: JDBC Meta Data getImportedKeys returns restrict not no action
- Prev by Date: JDBC DatabaseMetaData getIndexInfo() returning null values
- Next by Date: Re: JDBC driver for SQL Server 2000 and Java 5?
- Previous by thread: JDBC DatabaseMetaData getIndexInfo() returning null values
- Next by thread: Re: JDBC Meta Data getImportedKeys returns restrict not no action
- Index(es):
Loading