Clarification on table creation error on CockroachDB

Question

I have a framework which uses SQLalchemy to connect to SQL databases. I am trying to make it work with CockroachDB, even if official support is limited to MySQL, Postgres or Oracle.

The operations I am performing work flawlessly with the supported SQL databases. Using a 3-nodes CockroachDB cluster is not so trivial.

The issue appears with the following SQL operation:

CREATE TABLE dids (
    scope VARCHAR(25) NOT NULL,
    name VARCHAR(250) NOT NULL,
    account VARCHAR(25),
    did_type VARCHAR(1),
    is_open BOOLEAN,
    monotonic BOOLEAN DEFAULT '0',
    hidden BOOLEAN DEFAULT '0',
    obsolete BOOLEAN DEFAULT '0',
    complete BOOLEAN,
    is_new BOOLEAN DEFAULT '1',
    availability VARCHAR(1),
    suppressed BOOLEAN DEFAULT '0',
    bytes BIGINT,
    length BIGINT,
    md5 VARCHAR(32),
    adler32 VARCHAR(8),
    expired_at TIMESTAMP WITHOUT TIME ZONE,
    purge_replicas BOOLEAN DEFAULT '1',
    deleted_at TIMESTAMP WITHOUT TIME ZONE,
    events BIGINT,
    guid CHAR(32),
    project VARCHAR(50),
    datatype VARCHAR(50),
    run_number INTEGER,
    stream_name VARCHAR(70),
    prod_step VARCHAR(50),
    version VARCHAR(50),
    campaign VARCHAR(50),
    task_id INTEGER,
    panda_id INTEGER,
    lumiblocknr INTEGER,
    provenance VARCHAR(2),
    phys_group VARCHAR(25),
    transient BOOLEAN DEFAULT '0',
    accessed_at TIMESTAMP WITHOUT TIME ZONE,
    closed_at TIMESTAMP WITHOUT TIME ZONE,
    eol_at TIMESTAMP WITHOUT TIME ZONE,
    is_archive BOOLEAN,
    constituent BOOLEAN,
    access_cnt INTEGER,
    updated_at TIMESTAMP WITHOUT TIME ZONE,
    created_at TIMESTAMP WITHOUT TIME ZONE,
    CONSTRAINT "DIDS_PK" PRIMARY KEY (scope, name),
    CONSTRAINT "DIDS_ACCOUNT_FK" FOREIGN KEY(account) REFERENCES accounts (account) ON DELETE CASCADE,
    CONSTRAINT "DIDS_SCOPE_FK" FOREIGN KEY(scope) REFERENCES scopes (scope),
    CONSTRAINT "DIDS_MONOTONIC_NN" CHECK (MONOTONIC IS NOT NULL),
    CONSTRAINT "DIDS_OBSOLETE_NN" CHECK (OBSOLETE IS NOT NULL),
    CONSTRAINT "DIDS_SUPP_NN" CHECK (SUPPRESSED IS NOT NULL),
    CONSTRAINT "DIDS_ACCOUNT_NN" CHECK (ACCOUNT IS NOT NULL),
    CONSTRAINT "DIDS_PURGE_REPLICAS_NN" CHECK (PURGE_REPLICAS IS NOT NULL),
    CONSTRAINT "DIDS_CREATED_NN" CHECK (CREATED_AT IS NOT NULL),
    CONSTRAINT "DIDS_UPDATED_NN" CHECK (UPDATED_AT IS NOT NULL),
    CONSTRAINT "DIDS_TYPE_CHK" CHECK (did_type IN ('A', 'C', 'D', 'F', 'Y', 'X', 'Z')),
    CONSTRAINT "DIDS_AVAILABILITY_CHK" CHECK (availability IN ('A', 'D', 'L'))
)

And ends up in the following error:

invalid syntax: statement ignored: unimplemented at or near "cascade"
DETAIL: source SQL:
CREATE TABLE dids (
 scope VARCHAR(25) NOT NULL,
 name VARCHAR(250) NOT NULL,
 account VARCHAR(25),
 did_type VARCHAR(1),
 is_open BOOLEAN,
 monotonic BOOLEAN DEFAULT '0',
 hidden BOOLEAN DEFAULT '0',
 obsolete BOOLEAN DEFAULT '0',
 complete BOOLEAN,
 is_new BOOLEAN DEFAULT '1',
 availability VARCHAR(1),
 suppressed BOOLEAN DEFAULT '0',
 bytes BIGINT,
 length BIGINT,
 md5 VARCHAR(32),
 adler32 VARCHAR(8),
 expired_at TIMESTAMP WITHOUT TIME ZONE,
 purge_replicas BOOLEAN DEFAULT '1',
 deleted_at TIMESTAMP WITHOUT TIME ZONE,
 events BIGINT,
 guid CHAR(32),
 project VARCHAR(50),
 datatype VARCHAR(50),
 run_number INTEGER,
 stream_name VARCHAR(70),
 prod_step VARCHAR(50),
 version VARCHAR(50),
 campaign VARCHAR(50),
 task_id INTEGER,
 panda_id INTEGER,
 lumiblocknr INTEGER,
 provenance VARCHAR(2),
 phys_group VARCHAR(25),
 transient BOOLEAN DEFAULT '0',
 accessed_at TIMESTAMP WITHOUT TIME ZONE,
 closed_at TIMESTAMP WITHOUT TIME ZONE,
 eol_at TIMESTAMP WITHOUT TIME ZONE,
 is_archive BOOLEAN,
 constituent BOOLEAN,
 access_cnt INTEGER,
 updated_at TIMESTAMP WITHOUT TIME ZONE,
 created_at TIMESTAMP WITHOUT TIME ZONE,
 CONSTRAINT "DIDS_PK" PRIMARY KEY (scope, name),
 CONSTRAINT "DIDS_ACCOUNT_FK" FOREIGN KEY(account) REFERENCES accounts (account) ON DELETE CASCADE,

I would like to understand the issue I am facing. Thank you

You have a comma at the end of the last line of your query. Try removing that. — daShier
Nope: the query is correct, the dangling comma is printed when the error message triggers. You might note that the second code section is an extract of the first. — jetstream
Which version of CockroachDB are you using? Running the above statement on v19.1.4 (latest stable) works for me, as long as accounts.account and scopes.scope have unique constraints. — Marc
Thank you for trying Mark, I used the version packed as standard in the containers published by cockroach itself. I have to check which one is that! — jetstream
The cockroachdb/cockroach:latest docker image always points to the latest stable version, currently v19.1.4. You can check a node's version by running SELECT version();. — Marc

Bilal Akhtar Bilal Akhtar · Accepted Answer · 2020-12-15T20:51:13

You want to mention your FOREIGN KEY references inline with the column definition, instead of having a separate CONSTRAINT field:

CREATE TABLE dids (
    scope VARCHAR(25) NOT NULL,
    name VARCHAR(250) NOT NULL,
    account VARCHAR(25) REFERENCES accounts (account) ON DELETE CASCADE,
...
);

See: https://www.cockroachlabs.com/docs/stable/foreign-key.html

Clarification on table creation error on CockroachDB

1 Answers