Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Does SQLite3 not support foreign key constraints?

I am have a problem with SQLITE3.

I have created 2 tables persons and orders using the following SQL script:

sqlite> create table Persons(             P_Id int primary key,             LastName varchar,             FirstName varchar,             Address varchar,             City varchar         );  sqlite> create table Orders(             O_Id int NOT NULL,             OrderNo int NOT NULL,             P_Id int,              PRIMARY KEY (O_Id),             FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)         );  sqlite> insert into Orders values(1,77895,3);  sqlite> select * from Orders;  1|77895|3  sqlite> 

Even though the persons table is empty rows can be inserted into the orders table.

It does not show any error.

How is this possible.

like image 250
Abbas Mulani Avatar asked Mar 30 '12 05:03

Abbas Mulani


People also ask

What are the limitations of SQLite?

An SQLite database is limited in size to 281 terabytes (248 bytes, 256 tibibytes). And even if it could handle larger databases, SQLite stores the entire database in a single disk file and many filesystems limit the maximum size of files to something less than this.

Why foreign key is not recommended?

Having active foreign keys on tables improves data quality but hurts performance of insert, update and delete operations. Before those tasks database needs to check if it doesn't violate data integrity. This is a reason why some architects and DBAs give up on foreign keys at all.

Does SQLite support referential integrity?

In SQLite referential integrity constraints are used to maintain the relationship between the tables and these constraints will make sure that the value in one table referenced to value in another table like a foreign key relationship.


2 Answers

In SQLite 3.x, you have to make the following query every time you connect to an SQLite database:

PRAGMA foreign_keys = ON; 

Otherwise SQLite will ignore all foreign key constraints.

Why every time? Backwards compatibility with SQLite 2.x, according to the the documentation.

In SQLite 4.x, FK constraints will be enabled by default.

like image 144
kijin Avatar answered Sep 30 '22 09:09

kijin


SQLite Foreign Key Support

sqlite> PRAGMA foreign_keys = ON; 

This will enable foreign key constraint.

like image 40
vinayak jadi Avatar answered Sep 30 '22 09:09

vinayak jadi