H2 database CREATE TABLE with constraint

Question

I have two SQL statements:

CREATE TABLE legs(legid INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
                  playerid1 INT NOT NULL REFERENCES players(playerid),
                  playerid2 INT NOT NULL REFERENCES players(playerid),
                  added TIMESTAMP AS CURRENT_TIMESTAMP NOT NULL);

ALTER TABLE legs ADD CONSTRAINT distinct_players CHECK(playerid1 <> playerid2);

I am 99% sure I should be able to condense them into one, i.e:

CREATE TABLE table(...
                   playerid2 INT NOT NULL REFERENCES players(playerid) CHECK(playerid1 <> playerid2),
                   ...);

However, I am consistently getting a syntax error. AFAIK, this is where the constraint should be.

score 22 · Accepted Answer · answered Mar 24 '12 at 13:26

22

CREATE TABLE legs(legid INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
                  playerid1 INT NOT NULL REFERENCES players(playerid),
                  playerid2 INT NOT NULL REFERENCES players(playerid),
                  added TIMESTAMP AS CURRENT_TIMESTAMP NOT NULL,
                  CHECK (playerid1 <> playerid2));

answered Mar 24 '12 at 13:26

The check constraints have to all be at the bottom of the table and cannot be intermixed with the columns, as CHECK constraints can be intermixed in PostgreSQL. – Kent Bull Apr 11 '17 at 19:20

H2 database CREATE TABLE with constraint

1 Answers1