Get instant live expert help with SQL
“My expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Enter your problem description Our Experts are available now

Your message must be at least 40 characters
Select your SQL Environment:
Your message must be at least 40 characters
By submitting this form you agree to Got It's privacy policy.

What is Primary and Foreign Key in SQLite – Querychat

In this article, we will find out what are the primary and foreign keys and what is their purpose in SQLite.

SQLite primary key

The primary key is what makes each row of our records in the database unique. Thanks to the primary key, each record has its own unique identifier, which does not allow the row to be confused with another similar row from the same database.

Primary keys contain unique values, which means that they are not repeated within the table. Also, primary keys cannot contain NULL values.

There can be only one such key with one field in the table. If there are several fields, then such a key is called a composite key.

The primary key column itself identifies our table in the database.

SQLite ensures that the values of primary keys are not duplicated.

Example. Creating a primary key

In this example, we will make the “idFlower” column to be a primary key for the ‘Flower” table.

We will use this script: 

CREATE TABLE IF NOT EXISTS Flowers (

idFlower INTEGER PRIMARY KEY ,

name_flower VARCHAR (20) NOT NULL

);

SQLite foreign key

When we connect two tables using the foreign key, we can prove to one table that a certain value exists in the table where the foreign key is specified.

When working with several tables, we often relate them with one single column, and we need to know that if we enter a value in the associated column of the first table, then this value also exists in the column of the second table. To be sure, we can use a foreign key.

Thus, it turns out that when we enter values in this associated column, this key gives us a 100% guarantee that this value will exist in the second table.

Note: Foreign keys are turned off by default in SQLite and we need to turn them on to use them.

To turn this constraint on we can use the script: 

PRAGMA foreign_keys = ON;

Example. Creating a foreign key

Let’s start by creating a “ClientsCoolRedCar” table with some records.

We will use a script: 

CREATE TABLE ClientsCoolRedCar (

                             ClientId INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,

                             ClientName NVARCHAR(50) NULL,

                             CliendPhone NVARCHAR(30) NULL

);

INSERT INTO ClientsCoolRedCar VALUES (1, 'Ann Smith', 'xxxxxxxx');

INSERT INTO ClientsCoolRedCar VALUES (2, 'John Takke', 'xxxxxxxx');

INSERT INTO ClientsCoolRedCar VALUES (3, 'Austin Reeves', 'xxxxxxxx');

INSERT INTO ClientsCoolRedCar VALUES (4, 'Mike Johnson', 'xxxxxxxx');

INSERT INTO ClientsCoolRedCar VALUES (5, 'Ana Wilson', 'xxxxxxxx');

INSERT INTO ClientsCoolRedCar VALUES (6, 'Jane Miller', 'xxxxxxxx');

INSERT INTO ClientsCoolRedCar VALUES (7, 'Ian Huckabee', 'xxxxxxxx');

INSERT INTO ClientsCoolRedCar VALUES (8, 'Ann Brown', 'xxxxxxxx');

INSERT INTO ClientsCoolRedCar VALUES (9, 'Matthew Davis', 'xxxxxxxx');

INSERT INTO ClientsCoolRedCar VALUES (10, 'Dave Scott', 'xxxxxxxx');

And now we will create a related table called “RedCoolCars”. The associated column will be “ClientId”.

We will use this script: 

CREATE TABLE RedCoolCars (

                         RedCoolcarId INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,

                         Kind_of_Car NVARCHAR(30) NULL,

                         ClientID INTEGER NOT NULL,

                         FOREIGN KEY(ClientID) REFERENCES ClientsCoolRedCar(ClientId)

);

INSERT INTO RedCoolCars VALUES (1, 'Lamborghini', 10);

INSERT INTO RedCoolCars VALUES (2, 'Mazda', 5);

INSERT INTO RedCoolCars VALUES (3, 'Chevrolet', 3);

INSERT INTO RedCoolCars VALUES (4, 'Ford', 3);

INSERT INTO RedCoolCars VALUES (5, 'Dodge Charger', 2);

INSERT INTO RedCoolCars VALUES (6, 'BMW', 8);

INSERT INTO RedCoolCars VALUES (7, 'KIA', 7);

INSERT INTO RedCoolCars VALUES (8, 'Audi', 9);

INSERT INTO RedCoolCars VALUES (9, 'Mercedes', 5);

INSERT INTO RedCoolCars VALUES (10, 'Hyundai', 1);

INSERT INTO RedCoolCars VALUES (11, 'Hyundai', 10);

INSERT INTO RedCoolCars VALUES (12, 'Chevrolet', 4);

INSERT INTO RedCoolCars VALUES (13, 'Mazda', 7);

Result:

Figure 1. The “RedCoolCars” table

The foreign key will work this way: when we try to add the “DoctorId” in the “ClientsCooolRedCars” table that doesn’t exist in the “RedCoolCars” table, then this constraint will prevent us from doing this.

The classic SQL syntax to add a foreign key to our table would be: 

ALTER TABLE ClientRedCoolCar ADD CONSTRAINT key_doctor FOREIGN KEY (DoctorId)  

REFERENCES Doctors(id);

SQLite doesn’t support the ADD CONSTRAINT variant of the ALTER TABLE command. 

Therefore, the only way to add a foreign key in SQLite is during CREATE TABLE as follows: 

CREATE TABLE NameTable ( id INTEGER PRIMARY KEY, description TEXT, FOREIGN KEY (some_id) REFERENCES something(id) );

Instant livechat to an Expert!

Most of the time, the problem you will need to solve will be more complex than a simple method. If you want to save hours of research and frustration, try our live SQLExpert service! 

Our SQLQuerychat Experts are available 24/7 to answer any questions you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Leave a Reply

avatar
  Subscribe  
Notify of
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc