Hi Maris,
When you declare a constraint, Derby creates a backing index for that
constraint. If you do not explicitly name the constraint, then Derby
will make up a name. The constraint name which Derby makes up is the
same name that Derby gives to the backing index. If you don't name your
constraints, then you can use DatabaseMetaData.getIndexInfo() to find
the system-generated constraint names. However, if you do name your
constraints (as you did below with a unique constraint named
RECEIPT_REC_REF), then the backing index name will not match the
constraint name. For primary key constraints, you can use
DatabaseMetaData.getPrimaryKeys() to find the correct name for primary
keys (either user supplied or system generated). I don't see parallel
DatabaseMetaData methods for unique and foreign key constraints.
I am attaching two files which demonstrate this behavior:
DBMetaData.java - This is a compact set of VTIs which present some
DatabaseMetaData methods as table functions.
dbmetadata.sql - This script declares the table functions and uses them
to present getIndexInfo() and getPrimaryKey() information alongside
queries against the Derby catalogs.
Hope this helps,
-Rick
Maris Orbidans wrote:
Yes, please run attached class.
Using metadata it finds those two indexes and fails to drop last one.
Table: RECEIPT Index: SQL080509095407610
Table: RECEIPT Index: SQL080509095407630
java.sql.SQLSyntaxErrorException: ALTER TABLE failed. There is no
constraint 'APP.SQL080509095407630' on table '"APP"."RECEIPT"'.
If you use method dropUsingSysconstraints(con) instead dropUsingMetadata
then everything works.
SQL080509095407610
RECEIPT_REC_REF
Maris
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: 08 May 2008 14:32
To: Derby Discussion
Subject: Re: how to get names of column constraints ?
Maris Orbidans <[EMAIL PROTECTED]> writes:
I know I can read constraint names from SYS.SYSCONSTRAINTS but can I
get
it from DatabaseMetaData?
I am trying to get ha-jdbc synchronization working with my derby
database. The problem is that ha-jdbc reads INDEX_NAMEs from
DatabaseMetaData (with getIndexInfo) and assumes that column
constraint
has the same name and tries to drop it.
Not sure what your exact problem is, can you post a standalone repro?
I tried the following which worked:
st.execute("create table mytab (id int primary key, " +
"name varchar(50))");
:
s = con.createStatement();
DatabaseMetaData dbm = con.getMetaData();
rs = dbm.getIndexInfo(null, null, "MYTAB", false, false);
while (rs.next()) {
System.out.println("Table: " + rs.getString(3) + " Index: " +
rs.getString(6));
s.executeUpdate("alter table " + rs.getString(3) + " drop
constraint "
+ rs.getString(6));
}
Dag
_____________________________________________________________________
This e-mail has been scanned for viruses by MessageLabs.
**********************************************************************
Confidentiality : This e-mail and any attachments are intended for the
addressee only and may be confidential. If they come to you in error you must
take no action based on them, nor must you copy or show them to anyone. Please
advise the sender by replying to this e-mail immediately and then delete the
original from your computer.
Opinion : Any opinions expressed in this e-mail are entirely those of the
author and unless specifically stated to the contrary, are not necessarily
those of the author’s employer.
Security Warning : Internet e-mail is not necessarily a secure communications medium and can be subject to data corruption. We advise that you consider this fact when e-mailing us.
Viruses : We have taken steps to ensure that this e-mail and any attachments
are free from known viruses but in keeping with good computing practice, you
should ensure that they are virus free.
Inspired Gaming (UK) Limited
Registered in England No 3565640
Registered Office 3 The Maltings Wetmore Road, Burton On Trent, Staffordshire
DE14 1SE
_______________________________________________________________
This message has been checked for all known viruses by the MessageLabs Virus
Control Centre.
import java.sql.*;
/**
* <p>
* Table functions for inspecting database metadata.
* </p>
*/
public class DBMetaData
{
public static ResultSet getPrimaryKeys
( String catalog, String schema, String table )
throws SQLException
{
return getDBMD().getPrimaryKeys( catalog, schema, table );
}
public static ResultSet getIndexInfo
( String catalog, String schema, String table, int unique, int
approximate )
throws SQLException
{
boolean isUnique = (unique != 0 );
boolean isApproximate = (approximate != 0);
return getDBMD().getIndexInfo( catalog, schema, table, isUnique,
isApproximate );
}
private static DatabaseMetaData getDBMD()
throws SQLException
{
return getConnection().getMetaData();
}
private static Connection getConnection()
throws SQLException
{
return DriverManager.getConnection("jdbc:default:connection");
}
}
connect 'jdbc:derby:derby10.5;create=true';
drop table t;
drop table s;
drop function getPrimaryKeys;
drop function getIndexInfo;
create function getPrimaryKeys
(
catalogName varchar( 128 ),
schemaName varchar( 128 ),
tableName varchar( 128 )
)
returns TABLE
(
table_cat varchar( 128 ),
table_schema varchar( 128 ),
table_name varchar( 128 ),
column_name varchar( 128 ),
key_seq smallint,
pk_name varchar( 128 )
)
language java
parameter style DERBY_JDBC_RESULT_SET
reads sql data
external name 'DBMetaData.getPrimaryKeys'
;
create function getIndexInfo
(
catalogName varchar( 128 ),
schemaName varchar( 128 ),
tableName varchar( 128 ),
isUnique int,
isApproximate int
)
returns TABLE
(
table_cat varchar( 128 ),
table_schema varchar( 128 ),
table_name varchar( 128 ),
non_unique int,
index_qualifier varchar( 128 ),
index_name varchar( 128 ),
index_type smallint,
ordinal_position smallint,
column_name varchar( 128 ),
asc_or_desc varchar( 10 ),
cardinality int,
pages int,
filter_condition varchar( 100 )
)
language java
parameter style DERBY_JDBC_RESULT_SET
reads sql data
external name 'DBMetaData.getIndexInfo'
;
create table t( a int primary key, b int unique not null );
create table s( a int constraint a_primary primary key, b int constraint
b_unique unique not null );
select pk_name
from table( getPrimaryKeys( null, 'APP', 'T' ) ) s
;
select index_name
from table( getIndexInfo( null, 'APP', 'T', 1, 0 ) ) s
;
select c.constraintname
from sys.sysconstraints c, sys.systables t
where c.tableid=t.tableid and t.tablename='T'
;
select pk_name
from table( getPrimaryKeys( null, 'APP', 'S' ) ) s
;
select index_name
from table( getIndexInfo( null, 'APP', 'S', 1, 0 ) ) s
;
select c.constraintname
from sys.sysconstraints c, sys.systables t
where c.tableid=t.tableid and t.tablename='S'
;
