Hi all, Finally I cam up with the following solution for H2, because it is not possible to retrieve and in a single query/sub query.
SELECT DISTINCT constraint_name FROM information_schema.constraints WHERE table_name = 'YOUR_TABLE_NAME'; then use the constraint_name and build the query to alter the table as follows ALTER TABLE AM_APP_KEY_DOMAIN_MAPPING DROP CONSTRAINT <key_name_from_previous_query>; In H2 all the constraints are in information_schema.constraints, but in mysql those are in information_schema.REFERENTIAL_CONSTRAINTS Thanks, Madusanka On Tue, May 19, 2015 at 9:45 AM, Madusanka Premaratne <madusan...@wso2.com> wrote: > Hi Thusitha, > I tried the subquery approach before but it does not get the identifier > properly from the sub query. When I run the subquery only, it shows me the > constraint name. But when it comes to the main query, it does not get the > identifier. > > Hi Madhuranga, > Thanks for the reply, but the approach has a few problems. When the table > contains large number of record sets, this operation will take time and if > one query fails I may lose all my data. > > Regards, > Madusanka > > On Mon, May 18, 2015 at 6:18 PM, Madhuranga Bandara <madhura...@wso2.com> > wrote: > >> Hi Madushanka, >> >> This article [1] provides the step by step explanation on how to solve >> this kind of issue. >> Hope this is helpful. >> >> [1] http://www.briskbee.com/posts/changing_unnamed_constraints_in_play_ >> >> >> *Madhuranga Sampath Bandara* >> Software Engineer >> >> WSO2, Inc.;http://wso2.com/ >> <http://www.google.com/url?q=http%3A%2F%2Fwso2.com%2F&sa=D&sntz=1&usg=AFQjCNGJuLRux6KkJwXKVUCYOtEsNCmIAQ> >> lean.enterprise.middleware >> Mobile: +94722241065 >> >> On Mon, May 18, 2015 at 5:52 PM, Madusanka Premaratne < >> madusan...@wso2.com> wrote: >> >>> Hi Devs, >>> I need to perform a SQL table alteration to remove a foreign key which >>> is unnamed. As per my findings it is possible to find the automatically >>> assigned constraint name in mysql by using *information_schema* table >>> [1] >>> >>> Also it is possible in mssql[2] and oracle[3] >>> >>> [1] - >>> http://stackoverflow.com/questions/13856946/drop-unnamed-foreign-key-in-mysql >>> [2] - http://www.intergr8it.net/?p=19 >>> [3] - >>> http://stackoverflow.com/questions/9008445/drop-index-or-constraint-without-knowing-its-name-for-oracle >>> >>> I tried to add a name to the existing constraint by altering the table, >>> but it adds a new constraint with the given name. >>> >>> Is there a way to do the same in H2/ any other way to delete >>> the constraint? >>> >>> Can someone advice me to do this.? >>> >>> Thanks in advance. >>> Madusanka >>> >>> >>> >>> -- >>> *Madusanka Premaratne* | Associate Software Engineer >>> WSO2, Inc | lean. enterprise. middleware. >>> #20, Palm Grove, Colombo 03, Sri Lanka >>> Mobile: +94 71 835 70 73| Work: +94 112 145 345 >>> Email: madusan...@wso2.com | Web: www.wso2.com >>> >>> [image: Facebook] <https://www.facebook.com/maduzanka> [image: Twitter] >>> <https://twitter.com/rmmpremaratne> [image: Google Plus] >>> <https://plus.google.com/u/0/+MadusankaPremaratnemaduz/about/p/pub> [image: >>> Linkedin] <http://lk.linkedin.com/in/madusanka/> [image: Instagram] >>> <http://instagram.com/madusankapremaratne> [image: Skype] >>> <http://@rmmpremaratne> >>> >>> >>> _______________________________________________ >>> Dev mailing list >>> Dev@wso2.org >>> http://wso2.org/cgi-bin/mailman/listinfo/dev >>> >>> >> > > > -- > *Madusanka Premaratne* | Associate Software Engineer > WSO2, Inc | lean. enterprise. middleware. > #20, Palm Grove, Colombo 03, Sri Lanka > Mobile: +94 71 835 70 73| Work: +94 112 145 345 > Email: madusan...@wso2.com | Web: www.wso2.com > > [image: Facebook] <https://www.facebook.com/maduzanka> [image: Twitter] > <https://twitter.com/rmmpremaratne> [image: Google Plus] > <https://plus.google.com/u/0/+MadusankaPremaratnemaduz/about/p/pub> [image: > Linkedin] <http://lk.linkedin.com/in/madusanka/> [image: Instagram] > <http://instagram.com/madusankapremaratne> [image: Skype] > <http://@rmmpremaratne> > > -- *Madusanka Premaratne* | Associate Software Engineer WSO2, Inc | lean. enterprise. middleware. #20, Palm Grove, Colombo 03, Sri Lanka Mobile: +94 71 835 70 73| Work: +94 112 145 345 Email: madusan...@wso2.com | Web: www.wso2.com [image: Facebook] <https://www.facebook.com/maduzanka> [image: Twitter] <https://twitter.com/rmmpremaratne> [image: Google Plus] <https://plus.google.com/u/0/+MadusankaPremaratnemaduz/about/p/pub> [image: Linkedin] <http://lk.linkedin.com/in/madusanka/> [image: Instagram] <http://instagram.com/madusankapremaratne> [image: Skype] <http://@rmmpremaratne>
_______________________________________________ Dev mailing list Dev@wso2.org http://wso2.org/cgi-bin/mailman/listinfo/dev
