Creating foreign key on same table SQLite

Question

Recently I started using SQLite (as required for my study) and I came accross a couple of restrictions of SQLite and I was wondering: can't SQLite create foreign keys on the same table? E.g. 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 it gives me an error for the foreign key when I try to execute the SQL in SQLiteStudio.

Does anyone know why this isn't 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 FK constraint, you do that with a prefix of the CONSTRAINT keyword, like this:

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