🎓 A1Learner
🔍

SQL Foreign Keys

A foreign key is a column that points at the key of another table. It links a row in one table to a row in another table.

The orders table points two ways

Each order row holds two foreign keys. One names the customer, and one names the product.

SELECT order_id, customer_id, product_id FROM orders ORDER BY order_id LIMIT 3;

Build a notes table

The order_notes table holds one note for one order. Its order_id column points at the orders table.

CREATE TABLE order_notes (
  note_id INTEGER PRIMARY KEY,
  order_id INTEGER REFERENCES orders(order_id),
  note TEXT
);

Add a note for a real order

Order 1 exists in the orders table. The note joins back to its order in the SELECT.

CREATE TABLE order_notes (
  note_id INTEGER PRIMARY KEY,
  order_id INTEGER REFERENCES orders(order_id),
  note TEXT
);
INSERT INTO order_notes VALUES (1, 1, 'Gift wrap requested');
SELECT n.note_id, n.note, o.order_date FROM order_notes n JOIN orders o ON o.order_id = n.order_id;

Example

CREATE TABLE order_notes (
  note_id INTEGER PRIMARY KEY,
  order_id INTEGER REFERENCES orders(order_id),
  note TEXT
);
INSERT INTO order_notes VALUES (1, 999, 'Check the address');

Key points

  • REFERENCES: the word that ties a column to another table's key
  • The orders table ties each order to one customer with customer_id REFERENCES customers(customer_id).
  • The orders table ties each order to one product with product_id REFERENCES products(product_id).
  • A foreign key value must match a key that exists in the other table.
  • SQLite refuses an insert whose foreign key matches no row.

In this example, the code builds the order_notes table. The insert names order 999. No order with that id exists, so SQLite refuses the row.

Note

SQLite leaves foreign key checks off by default, and this course's practice setup switches them on.

Try it Yourself

Edit the code, then press Run.

Running is simulated · DB Fiddle

Exercise

What does REFERENCES do in a column definition?