Re: Add column unique

[Noel Grandin]

read the documentation again.
http://www.h2database.com/html/grammar.html#alter_table_add_constraint

On 2012-06-29 09:57, Multanis wrote:

Hi !

I cannot figure out how I can add an unique column using H2.
According to documentation, this should work:

alter table testit add myuniquecol varchar(50) unique;

But I keep on getting error:
[Error Code: 42000, SQL State: 42000]  Syntax error in SQL statement "ALTER TABLE TESTIT ADD MYUNIQUECOL VARCHAR(50) UNIQUE[*] "; SQL statement:
alter table testit add myuniquecol varchar(50) unique [42000-166]

Creating a table with similar syntax is working fine:
create table testit (id bigint primary key, uni varchar(50) unique);

Can anyone help me on the correct syntax to use (or is this a bug ?) ?

Thanks !

PS. I don't want to use separate add constraint, because my sql is generated by liquibase
--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To view this discussion on the web visit https://groups.google.com/d/msg/h2-database/-/0ZdknA0PALgJ.
To post to this group, send email to h2-da...@googlegroups.com.
To unsubscribe from this group, send email to h2-database...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.

[Multanis]

As I said, I don't want to use the add constraint syntax, but rather the alter column one:
http://www.h2database.com/html/grammar.html#alter_table_add
http://www.h2database.com/html/grammar.html#column_definition

According to my understanding, my syntax is correct...

Thx for your answer.

To unsubscribe from this group, send email to h2-database+unsubscribe@googlegroups.com.

[Noel Grandin]

Sorry that syntax only works in the CREATE TABLE statement, not in ALTER TABLE.

To view this discussion on the web visit https://groups.google.com/d/msg/h2-database/-/abbXuYsgrLIJ.

To post to this group, send email to h2-da...@googlegroups.com.

To unsubscribe from this group, send email to h2-database...@googlegroups.com.

[Nicolas Daniels]

Hmm ok, that's sad, but I can live with it ;-)

(Maybe the documentation should be updated so)

Thanks anyway !

[Thomas Mueller]

Hi,

I think you are right, the syntax doesn't work as documented. I'm
afraid we will have to either (a) implement it, or (b) change the
documentation. Currently I think (b) is OK.

What you could do is create a constraint. The result is not different
than if you would use "unique" when creating the table: the following
two cases return the same script.

drop table test;
create table test(id int, name varchar unique);
script nodata nosettings;

drop table test;
create table test(id int);
alter table test add column name varchar;
alter table test add constraint constraint_2 unique(name);
script nodata nosettings;

Another approach is to create a unique index.

Regards,
Thomas

[Nicolas Daniels]

Hi,

Thanks for your reply.

I'm using the add constraint approach and it works fine.
But as I'm using liquibase to generate this sql (database automatic
update framework), I had to fix their H2 SQL generator. If this fix is
not planned, I should probably forward this issue (and the fix) to the
Liquibase team.

Regards,

Nicolas