3

Does h2 support creating table with secondary key?

When I try this create table, it fails with "org.h2.jdbc.JdbcSQLException: Unknown data type: "("; SQL statement:"

CREATE TABLE IF NOT EXISTS testTable
    (col1 BIGINT(20) NOT NULL AUTO_INCREMENT,
    col2 BIGINT(20) NOT NULL,
    col3 BIGINT(20) NOT NULL,
    col4 VARCHAR(100) NOT NULL,
    PRIMARY KEY(col1),
    KEY(col1, col2, col3));

But this one succeeds:

CREATE TABLE IF NOT EXISTS testTable
    (col1 BIGINT(20) NOT NULL AUTO_INCREMENT,
    col2 BIGINT(20) NOT NULL,
    col3 BIGINT(20) NOT NULL,
    col4 VARCHAR(100) NOT NULL,
    PRIMARY KEY(col1));
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
Rishi Kesh Dwivedi
  • 643
  • 2
  • 7
  • 15

1 Answers1

0

KEY is MySQL-specific syntax:

KEY(col1, col2, col3)

What you're looking for is a UNIQUE constraint, e.g.

CREATE TABLE IF NOT EXISTS testTable (
    col1 BIGINT(20) NOT NULL AUTO_INCREMENT,
    col2 BIGINT(20) NOT NULL,
    col3 BIGINT(20) NOT NULL,
    col4 VARCHAR(100) NOT NULL,
    PRIMARY KEY (col1),
    UNIQUE (col1, col2, col3)
);

Note, I usually like to name my constraints, because that will allow for easier finding / dropping them later on:

CREATE TABLE IF NOT EXISTS testTable (
    col1 BIGINT(20) NOT NULL AUTO_INCREMENT,
    col2 BIGINT(20) NOT NULL,
    col3 BIGINT(20) NOT NULL,
    col4 VARCHAR(100) NOT NULL,
    CONSTRAINT primary_key PRIMARY KEY (col1),
    CONSTRAINT secondary_key UNIQUE (col1, col2, col3)
);

In case you're not looking for uniqueness

Interestingly (as pointed out also in the comments), MySQL uses the term KEY for non-unique indexes. In most databases (including MySQL), you would simply create an index for this case:

CREATE INDEX secondary_index ON testTable(col1, col2, col3);
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
  • Not also that you can use `INDEX` instead of the mysql specific `KEY` , if you just want a normal index (without the constraint of the column combination being unique) – nos Apr 10 '16 at 08:50
  • @nos: Good point. I forgot about this interesting MySQL interpretation of what a `KEY` is – Lukas Eder Apr 10 '16 at 10:47