H2数据库使用约束创建表格

Question

我有两个SQL语句：

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

我99%肯定我应该能够将它们压缩成一个，即：

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

然而，我一直得到一个语法错误。据我所知，这就是应该放置约束条件的地方。

- c24w

Answer 1

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

- user330315

---

检查约束必须全部位于表底部，不能与列混合，因为在PostgreSQL中，CHECK约束可以混合使用。 - Kent Bull