SQLite Keys: Foreign Key

A foreign key is a key used to link two tables together in a relational database management system such as SQLite. It is a column or set of columns in one table that refers to the primary key of another table.

Syntax

To create a table with a foreign key in SQLite, you can use the following syntax:

CREATE TABLE table1 (
  column1 datatype PRIMARY KEY,
  column2 datatype REFERENCES table2(column3)
);

In this syntax, the column2 contains the foreign key which references the primary key column3 in table2.

Example

Suppose we have two tables, employees and departments. The employees table has a foreign key dept_id that references the primary key id in the departments table.

CREATE TABLE departments (
  id INTEGER PRIMARY KEY,
  name TEXT
);

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT,
  dept_id INTEGER,
  FOREIGN KEY (dept_id) REFERENCES departments (id)
);

Explanation

In the example above, we create two tables employees and departments. The departments table has a primary key id and a column name. The employees table has a primary key id, a column name, and a foreign key dept_id, which references the id column in the departments table.

When an employee is added to the employees table, the dept_id value must match an id value in the departments table.

Use

Foreign keys allow you to maintain data integrity in a relational database. They ensure that data in one table corresponds to data in another table. Foreign keys can be used to create many-to-one, many-to-many, and one-to-one relationships between tables.

Important Points

• A foreign key column in a table must reference the primary key column of another table
• If a foreign key contains a value that has no corresponding value in the referenced table, the operation will fail
• If a row with a primary key that is referenced by a foreign key is deleted, the corresponding rows in the referencing table will also be deleted (this behavior can be changed using foreign key constraints)

Summary

In this tutorial, we learned about foreign keys in SQLite and how to create a table with a foreign key. We saw an example of using a foreign key in the employees and departments tables to create a relationship between them. Foreign keys ensure data integrity in relational databases and allow you to create relationships between tables. Understanding foreign keys is important for designing and maintaining relational databases.