

0 / 2 embers
0 / 3000 xp
click for more info
Complete a lesson to start your streak
click for more info
Difficulty: 6
click for more info
Not enough gems
Cost: 6 gems
1: Null Values
incomplete
2: Constraints
incomplete
3: Primary Keys
incomplete
4: Foreign Keys
incomplete
5: Schema
incomplete
6: Relational Databases
incomplete
7: Relational vs. Non-Relational DBs
incomplete
Back
ctrl+,
Next
ctrl+.
This lesson's interactive features are locked, please to keep using them
Foreign keys define the relationships between tables. Simply put, a FOREIGN KEY is a field in one table that references the PRIMARY KEY (or a UNIQUE column) of another table.
Creating a FOREIGN KEY in SQLite happens at table creation! After we define the table fields and constraints we add a named CONSTRAINT where we define the FOREIGN KEY column and its REFERENCES.
Here's an example:
CREATE TABLE departments (id INTEGER PRIMARY KEY, department_name TEXT NOT NULL);
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
department_id INTEGER,
CONSTRAINT fk_departments FOREIGN KEY (department_id) REFERENCES departments (id)
);In this example, an employee has a department_id. The department_id must be the same as the id field of a record from the departments table. fk_departments is the specified name of the constraint.
CONSTRAINT fk_departments: create a constraint called fk_departments
FOREIGN KEY (department_id): make this constraint a foreign key assigned to the department_id field
REFERENCES departments(id): link the foreign field id from the departments table
Creating a foreign key without the CONSTRAINT keyword means the name of the constraint is auto-assigned.
The "locations" team at CashPal stores country data in countries rather than duplicating it in users. The setup already links users.country_code to countries.code with a foreign key.