Skip to main content

Operation 'DROP CONSTRAINT' cannot be performed on object 'SQL0909090214156

7 replies [Last post]
rajeshrajesh_123
Offline
Joined: 2009-09-09
Points: 0

Hello,

I understand asking on Java DB derby in this forum is inappropriate, but I could not find any other place.

I am using the Sun Java System Application Server and Java DB that came with J2EE RI.

I had created 2 tables and added relationship within them.

Now, when both the tables have to be deleted, they refuse to go away.
ij> DROP TABLE BANK;

The error message is :

ERROR X0Y25: Operation 'DROP CONSTRAINT' cannot be performed on object 'SQL09090
9021415680' because CONSTRAINT 'ORGANIZATION_BANK' is dependent on that object.

Both the tables flash similar error messages. I deleted the contents.

May I know what is the command used for deleting the constraints and tables?

Regards,
Rajesh

Reply viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.
Martin Gainty

oracle implements
DROP TABLE tablename CASCADE CONSTRAINTS purge

http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/stat...
Martin Gainty
______________________________________________
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité

Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni.

From: mgainty@hotmail.com
To: users@glassfish.dev.java.net
Subject: RE: Operation 'DROP CONSTRAINT' cannot be performed on object 'SQL0909090214156
Date: Wed, 9 Sep 2009 22:00:46 -0400

a constraint is 'hooked' to the tables primary or foreign key column

generally DELETE TABLE TableName CASCADE CONSTRAINTS
will delete Table, contents as well as applicable constraints

Greetings
Martin Gainty
______________________________________________
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité

Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni.

> Date: Wed, 9 Sep 2009 13:11:07 -0700
> From: glassfish@javadesktop.org
> To: users@glassfish.dev.java.net
> Subject: Re: Operation 'DROP CONSTRAINT' cannot be performed on object 'SQL0909090214156
>
> I don't know if this is viable for you, but you could always just delete and reinstall your JavaDB installation. It will only take a few seconds.
>
> If you can't do that, I believe you're on the right path but I don't know JavaDB command syntax well enough to help.
> [Message sent by forum member 'matterbury' (matterbury@abinitio.com)]
>
> http://forums.java.net/jive/thread.jspa?messageID=363440
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@glassfish.dev.java.net
> For additional commands, e-mail: users-help@glassfish.dev.java.net
>

Hotmail® is up to 70% faster. Now good news travels really fast. Try it now.
_________________________________________________________________
Windows Live: Keep your friends up to date with what you do online.
http://windowslive.com/Campaign/SocialNetworking?ocid=PID23285::T:WLMTAG...
[att1.html]

Martin Gainty

a constraint is 'hooked' to the tables primary or foreign key column

generally DELETE TABLE TableName CASCADE CONSTRAINTS
will delete Table, contents as well as applicable constraints

Greetings
Martin Gainty
______________________________________________
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité

Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni.

> Date: Wed, 9 Sep 2009 13:11:07 -0700
> From: glassfish@javadesktop.org
> To: users@glassfish.dev.java.net
> Subject: Re: Operation 'DROP CONSTRAINT' cannot be performed on object 'SQL0909090214156
>
> I don't know if this is viable for you, but you could always just delete and reinstall your JavaDB installation. It will only take a few seconds.
>
> If you can't do that, I believe you're on the right path but I don't know JavaDB command syntax well enough to help.
> [Message sent by forum member 'matterbury' (matterbury@abinitio.com)]
>
> http://forums.java.net/jive/thread.jspa?messageID=363440
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@glassfish.dev.java.net
> For additional commands, e-mail: users-help@glassfish.dev.java.net
>

_________________________________________________________________
Hotmail® is up to 70% faster. Now good news travels really fast.
http://windowslive.com/online/hotmail?ocid=PID23391::T:WLMTAGL:ON:WL:en-...
[att1.html]

rajeshrajesh_123
Offline
Joined: 2009-09-09
Points: 0

ij> ALTER TABLE ORGANIZATION DROP CONSTRAINT ORGANIZATION_BANK;
0 rows inserted/updated/deleted

I tried to do the above. But still the deletion of the table was not possible.
So tried the following. It gives an error.

ij> ALTER TABLE ORGANIZATION DROP CONSTRAINT BMNYRGNZTNSRGNZTND;
ERROR 42X86: ALTER TABLE failed. There is no constraint 'APP.BMNYRGNZTNSRGNZTND'
on table 'APP.ORGANIZATION'.

How to delete the constraints and the tables?

Thanks,
Rajesh

matterbury
Offline
Joined: 2008-05-01
Points: 0

I don't know if this is viable for you, but you could always just delete and reinstall your JavaDB installation. It will only take a few seconds.

If you can't do that, I believe you're on the right path but I don't know JavaDB command syntax well enough to help.

rajeshrajesh_123
Offline
Joined: 2009-09-09
Points: 0

Hi matterbury,

Thanks for your help.

I managed to delete the tables and its associated constraints. I don't know the mechanism behind it. It just happened.

I had written a OneToMany (Bank) and ManyToOne (Organization) bidirectional relationship, and configured the oracle.toplinkessentials as create Tables=true. It had internally generated constraints, besides the tables Bank and Organization.

During deployment, it had created one more table Bank_Organization. I don't know why it created, because I thought only a ManyToMany required an intermediate table. Not deleting this table seemed to be a problem. When I deleted this table, Bank and Organization were deletable.

Thanks again!

Regards,
Rajesh

Marina Vatkina

Could it be that the bidirectional relationship was not really bidirectional,
but was treated as 2 one-way relationships?

thanks,
-marina

glassfish@javadesktop.org wrote:
> Hi matterbury,
>
> Thanks for your help.
>
> I managed to delete the tables and its associated constraints. I don't know the mechanism behind it. It just happened.
>
> I had written a OneToMany (Bank) and ManyToOne (Organization) bidirectional relationship, and configured the oracle.toplinkessentials as create Tables=true. It had internally generated constraints, besides the tables Bank and Organization.
>
> During deployment, it had created one more table Bank_Organization. I don't know why it created, because I thought only a ManyToMany required an intermediate table. Not deleting this table seemed to be a problem. When I deleted this table, Bank and Organization were deletable.
>
> Thanks again!
>
> Regards,
> Rajesh
> [Message sent by forum member 'rajeshrajesh_123' (rajeshrajesh_123@rediffmail.com)]
>
> http://forums.java.net/jive/thread.jspa?messageID=363481
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@glassfish.dev.java.net
> For additional commands, e-mail: users-help@glassfish.dev.java.net
>

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@glassfish.dev.java.net
For additional commands, e-mail: users-help@glassfish.dev.java.net

suniln2020
Offline
Joined: 2013-09-23
Points: 0

Remove all the data in the tables created during realation mapping like One to Many , many to many or even during @TableGenerator , after that Drop should work