Relationship Between Tables in SQLite/ SQLite - Create a Relationship

SQLite is a relational database management system (RDBMS). It uses the same relational model that other popular DBMSs (such as MySQL, Oracle, SQL Server, MS Access) use.

SQLite supports relationships just like any other relational database management system. We can create multiple tables, then have them linking to each other via a relationship. A relationship is where you have multiple tables that contain related data, and the data is linked by a common value that is stored in both tables.

The following diagram illustrate this concept.

Now, First Create Std_Master table

CREATE TABLE Std_Master(Roll_No int primary key, Name text, Address text);

Let’s, create another table, Std_Marks and then have linked to Std_Master table via relationship.

CREATE TABLE Std_Marks(Roll_No int primary key, Subject text, Marks int, foreign key(Roll_No) references Std_Master(Roll_No));

Both table, Std_Master and Std_Marks are similar but, in Std_Marks table, we have added foreign key(Roll_No) references Std_Master(Roll_No) to the end of the statement.

This creates a Foreign Key constraint on the Std_Marks.Roll_No column. This means that any data that is inserted into this column must match a value in the Std_Master.Roll_No column.

Now, if we run a .tables command, we should see both tables in the database:

Sqlite>.tables

Std_Master Std_Marks

Now Enter record in Std_Master table:

insert into Std_Master values(1, ‘Raja’, ‘Dibrugarh’);

insert into Std_Master values(2, ‘Priya’, ‘Tinsukia’);

insert into Std_Master values(3, ‘Liza’, ‘Tinsukia’);

Test the Relationship

Once we’ve created the table with the foreign key, we can test it by attempting to enter erroneous data. We can try to enter data in Std_Marks table with a Roll_No that doesn’t match Roll_No in the referenced table (i.e. the Std_Master table).

Add Record in Std_Marks table

insert into Std_Marks values(4, ‘Math’, 80);

This should result in the following:

Runtime error: FOREIGN KEY constraint failed.

Also, running a SELECT statement on the table will return no data. This is because the foreign key constraint blocked the wrong value from being inserted.

Didn’t Work Relationship?

If we don’t receive an error when trying to enter erroneous data like this, we may need to check settings.

Run the following command:

PRAGMA foreign_keys;

If this results in 0 it means that foreign key constraints are disabled. In fact, this is the default behaviour of SQLite (it’s for backwards compatibility).

To enable foreign key constraints, type the following:

PRAGMA foreign_keys = ON;

Now, running PRAGMA foreign_keys; should return 1, and subsequent attempts at inserting an invalid foreign key will fail.

Insert More Data

Now, that the relationship has been established. We can add as much data as we need. Only record with valid foreign keys will be inserted.

SQLite – JOIN Statements

Now that we have created a relationship between two tables, we can now use both tables within a single SELECT statement to return related data. We can do this using a JOIN statement. A join statement is used to query data across multiple related tables.

We will query our tables using the three main join types: