SQLite Keys - Primary Key

In SQLite, a primary key is a special type of unique identifier that uniquely identifies each row in a table. A primary key can be a single column or combination of columns, depending on the requirement.

Syntax

In SQLite, you can define a primary key for a table using the following syntax:

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
    ...
    PRIMARY KEY (column1, column2, ...)
);

Example

Suppose we want to create a table students to store information about students in a school. We can use the student_id column as the primary key.

CREATE TABLE students (
    student_id INTEGER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    email TEXT
);

Output

We can insert data into this table using the INSERT statement:

INSERT INTO students (student_id, first_name, last_name, email)
VALUES (1, 'John', 'Doe', 'john.doe@example.com');

We can retrieve data from this table using a SELECT statement:

SELECT * FROM students;

The output of this SELECT statement would be:

student_id | first_name | last_name | email
-----------+------------+-----------+-------------------------
1          | John       | Doe       | john.doe@example.com

Explanation

In the example above, we create a table called students with columns for student_id, first_name, last_name, and email. We define student_id as the primary key for the table by adding the PRIMARY KEY constraint after the column definition.

We then insert a row of data into the students table using the INSERT statement and retrieve the data from the table using a SELECT statement.

Use

Primary keys are used to uniquely identify each row in a table. They ensure that each row has a unique identifier and no two rows can have the same identifier. Primary keys are frequently used as foreign key references in other tables, allowing for efficient joins between related tables.

Important Points

• A primary key must be unique for every row in the table.
• A primary key cannot be NULL.
• A primary key can be a single column or combination of columns.
• A primary key column should be chosen based on its uniqueness, stability, and simplicity.
• Primary keys are frequently used as foreign key references in other tables.

Summary

In this tutorial, we learned about primary keys in SQLite and how to define them for a table. We saw an example of defining a primary key for a students table and how to insert and retrieve data from it. Primary keys are important for ensuring data integrity in a table and facilitating joins between related tables.