Debugging a SQL error in a SQL script line by line in DBeaver

Question

I am using DBeaver 23.1.2. The following SQL script is failing with the following SQL error:

SQL Error [1]: [SQLITE_ERROR] SQL error or missing database (near "type_id": syntax error)

Is there a way to debug the following SQL script in DBeaver line by line to see

CREATE TABLE IF NOT EXISTS my_types 
(
    type_id INTEGER PRIMARY KEY AUTOINCREMENT,
    type_name TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS objects 
(
    id INTEGER PRIMARY KEY,
    FOREIGN KEY type_id REFERENCES my_types(type_id),
    content TEXT NOT NULL
);

score 0 · Answer 1 · answered Aug 21 '23 at 00:38

I created the table without the FOREIGN KEY, then I created the FOREIGN KEY manually via the DBeaver GUI, and this is what it now looks like:

CREATE TABLE IF NOT EXISTS my_types (
  type_id INTEGER PRIMARY KEY AUTOINCREMENT,
  type_name TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS objects (
  id INTEGER PRIMARY KEY,
  type_id INTEGER,
  content TEXT NOT NULL,
  CONSTRAINT objects_FK FOREIGN KEY (type_id) REFERENCES my_types(type_id) ON UPDATE CASCADE
);

It seems the CONSTRAINT line must be at the end (or I guess at least after the 'local' type_id field is created which I think becomes the store for the FOREIGN KEY).

Debugging a SQL error in a SQL script line by line in DBeaver

1 Answers1