dropping unique column constraint without knowing its name

[msk....@gmail.com]

Hi.

I've created a table with unique constraint on one column with liquibase. I forgot to name that constraint, so a random name was choosen by 2=h2 (or liquibase, doesn't matter). Now I need to drop that constraint, but I have no success.

I should obtain a constraint name by quering:

select constraint_name  from information_schema.constraints where table_schema = 'PUBLIC' and TABLE_NAME='foo' and  COLUMN_LIST ='column_name';

.. but I can't use that result this way:

alter table foo drop constraint (select constraint_name ...)

Any tip how to use result of that select as argument for alter table request?

Thx

Dusan

[Noel Grandin]

SET @TMP = select constraint_name from informa....

alter table foo drop constraint @TMP

[msk....@gmail.com]

I tried that as first solution, but:

set @TMP = select constraint_name from information_schema.constraints where table_schema = 'PUBLIC' and TABLE_NAME='xxx' and  COLUMN_LIST ='NAME';

select @TMP; 

"CONSTRAINT_1"

alter table xxx drop constraint @TMP; 

Syntax error in SQL statement "ALTER TABLE xxx DROP CONSTRAINT @[*]TMP "; expected "identifier"; SQL statement:

alter table xxx drop constraint @TMP [42001-174] 42001/42001 (Help)

@TMP was not expanded in "alter table" statement :(

--

Dusan

Dňa pondelok, 17. augusta 2015 12:51:26 UTC+2 msk....@gmail.com napísal(-a):

[Thomas Mueller]

Hi,

You would have to create a "execute" user defined function first. Then:

    SET @TMP = select constraint_name  from informa....
    call execute('alter table foo drop constraint ' || @TMP);

Regards,

Thomas

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.