Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Creating foreign key on same table SQLite

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?

like image 598
user2273652 Avatar asked Apr 12 '13 09:04

user2273652


1 Answers

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)
like image 178
Donal Fellows Avatar answered Nov 16 '22 18:11

Donal Fellows