How do FK:PK relations work in Sqlite?

Question

I am using the DB Browser for SQLite to try and figure this out. I've opened Northwind.sqlite and in it it shows me the following for a table:

CREATE TABLE `Order Details` (
    `OrderID`   int,
    `ProductID` int,
    `UnitPrice` float ( 26 ),
    `Quantity`  int,
    `Discount`  float ( 13 ),
    PRIMARY KEY(`OrderID`,`ProductID`)
);

However, in the Sql Server Northwind OrderID and ProductID are foreign keys, not primary keys. Does this work differently in SQLite? And if so, how do the relationships work?

thanks - dave

MikeT · Accepted Answer · 2018-04-06T21:39:45.643

The above will create a table that has no FOREIGN keys but 2 indexes.

One a largely hidden index according to rowid.
The other, PRIMARY KEY(OrderID,ProductID) will be an index according to the combination of OrderId and ProductID.

some things about rowid (aka id)

rowid is an automatically created column called rowid (it can also be referenced using oid or rowid (case independent)) and if present is really the primary key.
rowid will be a unique signed integer using up to 64 bits. The lowest value and also the first value will be 1, the highest value being 9223372036854775807.
In later versions of SQLite 3.8.2 on the WITHOUT ROWID keyword was added to allow suppression of the rowid column/index (your Order Details table may benefit being a without rowid table).
if a column is defined with the type INTEGER PRIMARY KEY or INTEGER PRIMARY KEY AUTOINCREMENT then that column (there can only be 1 such column per table) is an alias of for the rowid column.
- AUTOINCREMENT introduces a rule that when inserting a row the rowid must be greater than any that exist or existed.
- It DOES NOT guarantee that the rowid will monotonically increase, although generally the id will (even without AUTOINCREMENT (perhaps the most misused/misunderstood keyword in SQLite)).
- Without AUTOINCREMENT SQlite may find a lower rowid and use that, but not until a rowid of 9223372036854775807 has been reached.
- AUTOINCREMENT, if a rowid of 9223372036854775807 has been reached will is an SQLITE_FULL exception.
- AUTOINCREMENT results in overheads (e.q. a table named sqlite_sequence is then maintained recording the highest given sequence number). The documentation recommends that it not be used unless required, which is rarely the case.
- Some limited testing I did resulted in an 8-12% greater processing time for AUTOINCREMENT. What are the overheads of using AUTOINCREMENT for SQLite on Android?
- For more about rowid see SQLite Autoincrement and also Clustered Indexes and the WITHOUT ROWID Optimization

Coding PRIMARY KEY (if not on an INTEGER column i.e. not an alias of rowid) implies a UNIQUE constraint. It is not saying/checking that the value or any of the values in a clustered index exists in any other table.

Note null is not considered to be the same value, so in your Order Details table it is possible to have any combination of the values as null.

Coding a FOREIGN KEY introduces a constraint that the referenced value(s) must exist in the respective table/column. Additionally :-

Usually, the parent key of a foreign key constraint is the primary key of the parent table. If they are not the primary key, then the parent key columns must be collectively subject to a UNIQUE constraint or have a UNIQUE index. If the parent key columns have a UNIQUE index, then that index must use the collation sequences that are specified in the CREATE TABLE statement for the parent table. SQLite Foreign Key Support

Considering all of this you may want to do make some changes to the Order Details table :-

You could make it a WITHOUT ROWID table.
You could make both the OrderID and the ProductID columns NOT NULL.
You could add FOREIGN KEY's to both the OrderID and the ProductID columns.

So perhaps you could have :-

CREATE TABLE `Order Details` (
    `OrderID`   int NOT NULL REFERENCES `Orders` (`OrderId`), -- ADDED NOT NULL and FKEY
    `ProductID` int NOT NULL REFERENCES `Products`(`ProductId`) , -- ADDED NOT NULL and FKEY
    `UnitPrice` float ( 26 ),
    `Quantity`  int,
    `Discount`  float ( 13 ),
    PRIMARY KEY(`OrderID`,`ProductID`)
    )
    WITHOUT ROWID -- ADDED WITHOUT ROWID
;

The above uses column constraints

Alternately, utilising TABLE constraints, you could do :-

CREATE TABLE `Order Details` (
    `OrderID`   int NOT NULL, -- ADDED NOT NULL
    `ProductID` int NOT NULL, -- ADDED NOT NULL
    `UnitPrice` float ( 26 ),
    `Quantity`  int,
    `Discount`  float ( 13 ),
    PRIMARY KEY(`OrderID`,`ProductID`),
    FOREIGN KEY (`OrderId`) REFERENCES `Orders`(`OrderId`), -- ADDED FKEY AS TABLE CONSTRAINT
    FOREIGN KEY (`ProductID`) REFERENCES `Products`(`ProductID`) -- ADDED FKEY AS TABLE CONSTRAINT
    )
    WITHOUT ROWID -- ADDED WITHOUT ROWID
;

Both have the same outcome, the only difference being where the FOREIGN KEY constraints are defined.

Both the above assumes that the referenced tables are Orders and Products.

First off thank you. This is super helpful. Second, the Northwind.sqlite comes from the SQLite site - any idea why they didn't put in the PK:FK relationships? thanks - dave — David Thielen, Apr 06 '18 at 22:25
A quick look and found [Northwind.Sqlite3.create.sql](https://github.com/jpwhite3/northwind-SQLite3/blob/master/Northwind.Sqlite3.create.sql) which does have FK's e.g. `FOREIGN KEY ([OrderID]) REFERENCES [Orders] ([OrderID]) ON DELETE NO ACTION ON UPDATE NO ACTION, FOREIGN KEY ([ProductID]) REFERENCES [Products] ([ProductID]) ON DELETE NO ACTION ON UPDATE NO ACTION` and also **-- Modified by Len Boyette @ 2013-02-08 -- Added foreign keys** — MikeT, Apr 06 '18 at 22:37
Important note for others needing this, the sql scripts referenced above have the FK:PK relationships. But the various Northwind.sqlite files at both northwind-SQLite3 and another at the official SQLite site do NOT have the FK:PK relationships — David Thielen, Apr 12 '18 at 17:20

How do FK:PK relations work in Sqlite?

1 Answers1