14

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

keithphw
  • 380
  • 1
  • 4
  • 14
  • 1
    If I understand the documentation (http://www.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 Mar 11 '12 at 03:28
  • 1
    The statement you posted works for me (there is no syntax error when I run in against the H2 database). – Thomas Mueller Mar 11 '12 at 09:16
  • 1
    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 Mar 11 '12 at 13:08
  • 2
    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 Mar 11 '12 at 15:32

1 Answers1

13

From here:

this should work:

ALTER TABLE SAMP.SCHED ADD PRIMARY KEY (CLASS_CODE, DAY)
icyrock.com
  • 27,952
  • 4
  • 66
  • 85
  • Nice! Thanks for the quick reply icyrock, that works. Still, so weird that there's no obvious way way to do it in the CREATE TABLE statement. – keithphw Mar 11 '12 at 04:07
  • 11
    The `CREATE TABLE(..., PRIMARY KEY(CLASS_CODE, DAY))` works well with the H2 database. Something else must have been wrong when you ran the statement. – Thomas Mueller Mar 11 '12 at 09:19
  • 1
    You're right, thanks Thomas. Apologies for my idiocy. I just realised that you're the engineer behind H2. It's a fantastic project, and the tutorial is great (http://www.h2database.com/html/tutorial.html#web_applications), none of the other database projects have such a comprehensive guide or an easy to use double-clickable jar file. – keithphw Mar 11 '12 at 13:12