You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Maris Orbidans <ma...@ingg.com> on 2008/05/07 18:58:27 UTC
how to get names of column constraints ?
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.
DEBUG net.sf.hajdbc.sync.SynchronizationSupport - ALTER TABLE
APP.RECEIPT DROP CONSTRAINT SQL080507133401610
ERROR net.sf.hajdbc.sql.AbstractDatabaseCluster - Error for batch
element #0: ALTER TABLE failed. There is no constraint
'APP.SQL080507133401610' on table '"APP"."RECEIPT"'.
Maris
**********************************************************************
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.
Re: how to get names of column constraints ?
Posted by "Dag H. Wanvik" <Da...@Sun.COM>.
Maris Orbidans <ma...@ingg.com> writes:
> Yes, please run attached class.
Thanks, Maris, I can reproduce your problem. I think the issue here is
that in Derby, an index may support more than one constraint, so there
is not necessarily a one-to-one relationship here.
So I am not convinced that the assumption than HA-JDBC makes here is
warranted. It would seem that JDBC should have supported a
getConstraintInfo call also...
In the case of the primary key, there is no named constraint, so
Derbys underlying index name ("SQL<big number>") is used as a
constraint name (and you can thus drop it). In the named constraint
case, the underlying index name is not exposed and the drop fails. I
agree this seems a bit un-orthogonal.
Does anyone have more insight on this?
Dag
Re: how to get names of column constraints ?
Posted by Rick Hillegas <Ri...@Sun.COM>.
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: 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 <ma...@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 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.
>
RE: how to get names of column constraints ?
Posted by Maris Orbidans <ma...@ingg.com>.
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: 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 <ma...@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 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.
Re: how to get names of column constraints ?
Posted by "Dag H. Wanvik" <Da...@Sun.COM>.
Maris Orbidans <ma...@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