CODEWITHSUNDEEP
sqlitereact-nativeexpo
Praneeth Rajarikam
Praneeth Rajarikam

Reputation: 88

Expo sqlite foreign key

Did anyone work on react native expo's sqlite database with foreign key constraints? Can we use structure similar to sql?

I'm trying to work on it building multiple tables with foreign key condition.

Example: If we have 2 tables Persons and Orders where personID is referred as foreign key in orders table. How would it be done using sqlite?

Upvotes: 1

Views: 2359

Answers (1)

MikeT
MikeT

Reputation: 56938

You would have two tables, perhaps with a column as the alias of the rowid column, this

e.g.

CREATE TABLE persons (
    personid INTEGER PRIMARY KEY, 
    personname TEXT
);

CREATE TABLE orders (
    orderid INTEGER PRIMARY KEY, 
    ordername TEXT, 
    person_reference INTEGER REFERENCES persons(personid)
);
  • Note that you have to turn foreign key handling on e.g. by executing PRAGMA foreign_keys = ON; (or true). See PRAGMA foreign_keys
  • in SQLite coding column_name INTEGER PRIMARY KEY defines that column as an alias of the rowid column, and if a value is not provided for the column when inserting then an integer value will be assigned. The initial value for the first row will be 1, subsequent values will typically be 1 greater than the highest rowid value (read the link above in regards why the word typically has been used).

If you then try to insert an Order for a non-existent personid you will then get a Foreign Key conflict.

An alternative to the column level definition would be to define the foreign key(s) at the table level e.g.

CREATE TABLE orders (
    orderid INTEGER PRIMARY KEY, 
    ordername TEXT, 
    person_reference INTEGER, 
    FOREIGN KEY (person_reference) REFERENCES persons(personid)
);

As an example, consider the following :-

INSERT INTO persons (personname) VALUES 
    ('Fred'),
    ('Mary'),
    ('Sue'),
    ('Tom')
;
INSERT INTO orders (ordername, person_reference) VALUES
    ('Order 1 for Fred',1),
    ('Order 2 for Sue',3),
    ('Order 3 for Fred',1),
    ('Order 4 for Mary',2)
;   
INSERT into orders (ordername, person_reference) VALUES
    ('Order 5 for nobody',100);

The result would be :-

INSERT INTO persons (personname) VALUES ('Fred'),('Mary'),('Sue'),('Tom')
> Affected rows: 4
> Time: 0.453s


INSERT INTO orders (ordername, person_reference) VALUES
  ('Order 1 for Fred',1),('Order 2 for Sue',3),('Order 3 for Fred',1),('Order 4 for Mary',2)
> Affected rows: 4
> Time: 0.084s


INSERT into orders (ordername, person_reference) VALUES
  ('Order 5 for nobody',100)
> FOREIGN KEY constraint failed
> Time: 0s

i.e. the last as there is no row in the persons table with a personid of 100, then the last insert (on it's own doe demonstration) fails.

You may wish to refer to SQLite Foreign Key Support

Upvotes: 3

Related Questions