CODEWITHSUNDEEP
sqlsqlite
Bruce Chidester
Bruce Chidester

Reputation: 621

SQLite FOREIGN KEY type-name

The SQLite syntax documentation shows that a "type-name" can be specified when declaring a foreign key.

https://www.sqlite.org/syntax/column-def.html

I have not been able to find anywhere, what role it has with the "type" of the key that is refers to. What happen when the foreign key "type-name" does not match the "type-name" of the value it refers to? Does it play any role at all, or just for readability?

Thanks in advance.

Upvotes: 0

Views: 340

Answers (1)

CL.
CL.

Reputation: 180070

A foreign key constraint definition does not contain a type name. It can be attached to a column definition, which can have a type name, but that type name belongs to the column itself:

CREATE TABLE Parent (
    ID    INTEGER  PRIMARY KEY,
    Name  TEXT,
    [...]
);
CREATE TABLE Child (
    [...],
    ParentID  INTEGER  REFERENCES Parent(ID)
    -- column def. --  ---- foreign key ----
);

All three columns shown above have a type, but the foreign key constraint begins with the REFERENCES and does not contain a type.

A foreign key constraint can also be written as a table constraint:

CREATE TABLE Child (
    ParentID  INTEGER,
    [...],
    FOREIGN KEY ParentID REFERENCES Parent(ID)
);

This form makes it obvious that there is no separate type for the constraint.

Anyway, SQLite uses dynamic typing and ignores the type name in most circumstances, so it does not matter what you write in there, or if you're using a type at all.

Upvotes: 1

Related Questions