Creating a foreign key in a single SQLite table

Question

I recently started using SQLite (as required for my research), and I came up with a few SQLite limitations, and I was wondering: is it possible for SQLite to create foreign keys in a single table? For example. this is my code:

CREATE TABLE Categories
(
    name varchar(20),
    parent_category varchar(20) NULL,
    PRIMARY KEY(name),
    FOREIGN KEY parent_category_fk(parent_category) REFERENCES Categories(name)
)

But this gives me an error for a foreign key when I try to execute SQL in SQLiteStudio.

Does anyone know why this is not working?

Answer 1

The problem is that you have the wrong syntax for the FK clause. It should be:

FOREIGN KEY (parent_category) REFERENCES Categories(name)

If you want to name the restriction FK, you do this with a keyword prefix CONSTRAINT, for example:

CONSTRAINT parent_category_fk FOREIGN KEY (parent_category) REFERENCES Categories(name)