Does h2 support creating table with secondary key?

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));

Answers


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);

Need Your Help

Parsing issue with Google function =importhtml in Google scripting javascript

javascript google-spreadsheet-api

Love the "=IMPORTHTML" function in google sheets. However, having an issue parsing the function in a script that I have written in Google Sheets script. The error I receive a "Formula parse error".

wired junit test assert

eclipse maven junit assert

Hi i have the following junit test