db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: how to get names of column constraints ?
Date Fri, 09 May 2008 17:23:14 GMT
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,

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
> Maris
> -----Original Message-----
> From: Dag.Wanvik@Sun.COM [mailto:Dag.Wanvik@Sun.COM] 
> Sent: 08 May 2008 14:32
> To: Derby Discussion
> Subject: Re: how to get names of column constraints ?
> Maris Orbidans <maris.orbidans@ingg.com> 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
> 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

View raw message