CODEWITHSUNDEEP
sqlsyntax-errorhsqldb
IAmYourFaja
IAmYourFaja

Reputation: 56894

HSQLDB Error: unexpected token: FOREIGN

I have the following SQL I am running in my local HSQLDB 2.3.2 database:

CREATE TABLE IF NOT EXISTS countries (
    country_id              INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL PRIMARY KEY,
    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                INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL PRIMARY KEY,
    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)
);

When I run this I get:

Error: unexpected token: FOREIGN
SQLState:  42581
ErrorCode: -5581

What is going on here, and what can I do to fix it?

Upvotes: 2

Views: 1507

Answers (1)

user3191188
user3191188

Reputation: 242

Is there a difference between INT NOT NULL and INTEGER NOT NULL? Because you have defined the country_id fields by both.

Also, if you insert SET REFERENTIAL_INTEGRITY FALSE; at the top, do you still get this error?

Upvotes: 2

Related Questions