h2 sql, create table with multi-column primary key?

Question

How can i create a multi-column primary key within the CREATE TABLE statement using the h2 database? From my investigations, the code to do so in mySQL and Apache Derby databases is:

CREATE TABLE SAMP.SCHED(
    CLASS_CODE CHAR(7) NOT NULL, 
    DAY SMALLINT NOT NULL, 
    STARTING TIME, 
    ENDING TIME,
    PRIMARY KEY (CLASS_CODE, DAY));

But this doesn't work in h2, it results in a 'org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement'

Any help is much appreciated. Thanks

If I understand the documentation (h2database.com/html/grammar.html#constraint) properly, there's nothing wrong with your PRIMARY KEY declaration - is it possible there is something else wrong with the statement? — David Faber
The statement you posted works for me (there is no syntax error when I run in against the H2 database). — Thomas Mueller
Oh dear, you're right, I left out the last bracket in my code so there was an unclosed bracket. Sorry guys, that is a really dumb mistake. Apologies for the trouble. Such a pity that IDE's don't parse SQL strings and check for stuff like that. — keithphw
I think SQL statements shouldn't have to be string, instead, the SQL statement should be specified in the programming language itself. That would void such problems plus it would make code injection impossible. This is why I started the H2 sub-project JaQu. — Thomas Mueller

icyrock.com icyrock.com · Accepted Answer · 2012-03-11T03:26:16

From here:

http://www.razorsql.com/features/h2_add_primary_key.html

this should work:

ALTER TABLE SAMP.SCHED ADD PRIMARY KEY (CLASS_CODE, DAY)

h2 sql, create table with multi-column primary key?

1 Answers