Quick SQL question: Correct syntax for creating a table with a primary key in H2?

sql database h2 create-table
Daddy Warbox picture Daddy Warbox · Mar 28, 2009 · Viewed 15.1k times · Source

I'm currently starting a new Java application using the H2 database, but I have some confusion about basic SQL use for creating tables. How do I make a table of entries (strings) each with unique, auto-incrementing, non-null, integer primary keys? One of the most basic things to do, but I'm not sure offhand what the correct way to do it with H2 is.

I blame these for my confusion (specifies more than one way of doing the same thing between different databases; can't figure the right way for H2, though): http://www.w3schools.com/Sql/sql_primarykey.asp http://www.w3schools.com/Sql/sql_autoincrement.asp

Answer

Matt picture Matt · Mar 28, 2009

If I'm reading the H2 documentation correctly, this should work:

CREATE TABLE MyTableName(PKFieldName IDENTITY PRIMARY KEY, StringFieldName VARCHAR(255))

Basically, you just want to declare your key column to be of type IDENTITY.

For IDENTITY type see: http://www.h2database.com/html/datatypes.html#identity_type
For CREATE TABLE syntax see: http://www.h2database.com/html/grammar.html#create_table

Related questions

"Create table if not exists" - how to check the schema, too?

Is there a (more or less) standard way to check not only whether a table named mytable exists, but also whether its schema is similar to what it should be? I'm experimenting with H2 database, and CREATE TABLE IF NOT …

Read More
Can I have H2 autocreate a schema in an in-memory database?

(I've already seen the H2 database In memory - Init schema via Spring/Hibernate question; it is not applicable here.) I'd like to know if there's a setting in H2 that will allow me to auto-create a schema upon connecting …

Read More
Defining a Foreign key constraint in H2 Databases

I am new in coding so I made a tables in SQL server and it worked, so i used the same command in H2 and it said I have a syntax problems with the second table, someone can help? CREATE …

Read More