sql - 主键定义上的HSQLDB错误

Question

我在本地HSQLDB 2.3.2数据库中运行以下SQL:

CREATE TABLE IF NOT EXISTS countries (
    country_id              PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL,
    country_version         INTEGER NOT NULL,
    country_name            NVARCHAR(100) NOT NULL,
    country_label           NVARCHAR(100) NOT NULL,
    country_description     NVARCHAR(500) NOT NULL
    country_code            NVARCHAR(10) NOT NULL,

    CONSTRAINT uc_countries UNIQUE (country_id, country_version, country_label, country_description, country_code)
);

CREATE TABLE IF NOT EXISTS states (
    state_id                PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL,
    state_version           INTEGER NOT NULL
    state_name              NVARCHAR(100) NOT NULL,
    state_label             NVARCHAR(100) NOT NULL,
    state_description       NVARCHAR(500) NOT NULL,
    country_id              INT NOT NULL,

    FOREIGN KEY (country_id) REFERENCES countries(country_id),
    CONSTRAINT uc_states UNIQUE (state_id, state_version, state_label, state_description, country_id)
);

CREATE TABLE IF NOT EXISTS addresses (
    address_id              PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL,
    address_version         INTEGER NOT NULL,
    address_line_1          NVARCHAR(500) NOT NULL,
    address_line_2          NVARCHAR(500),
    address_line_3          NVARCHAR(500),
    address_city            NVARCHAR(100) NOT NULL,
    state_id                INTEGER NOT NULL,
    address_postal_code     INTEGER NOT NULL,

    FOREIGN KEY (state_id) REFERENCES states(state_id),
    CONSTRAINT uc_addresses UNIQUE (address_line_1, address_city, state_id)
);

我的 PRIMARY KEY的目标是使它们自动递增(开始:1，逐步:1)不能为NULL的整数。

执行此操作时，我得到:

Error: unexpected token: PRIMARY: line: 2
SQLState:  42581
ErrorCode: -5581

这里发生了什么？我的表/约束设置还有其他不好的地方吗？

Answer 1

您需要该列的数据类型，并且primary key关键字放在末尾(as documented in the manual)

country_id  INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL PRIMARY KEY

在 countries.country_description和 states.state_version中的列定义之后，您还缺少逗号