FOREIGN KEYS

Back to: Structured Query Language (SQL)

---

A FOREIGN KEY is a field/column(or collection of fields) in a table that refers to a PRIMARY KEY in another table. It is used for linking one or more than one table together. FOREIGN KEY is also called referencing key. A Foreign key creates a link (relation) between two tables thus creating referential integrity.

Let us understand the concept of MySQL FOREIGN KEY with an example.

We’ll make two new tables, namely customers and transactionss. The customers table will be the parent table, as it contains the PRIMARY KEY (customer_id) and referenced by a foreign key (customer_id) in the transactions table will (the child table).

The primary key from the customers table can be found as the foreign key in the transactions table. If I look at the first transaction_id: ‘1000’, I can see that this aligns with customer_id: ‘3’ – which I can cross reference with the customers table to find that the customer was Bubble Bass. This establishes a link between the two tables.

Create and populate a customers table with some data:

CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);

INSERT INTO customers (first_name, last_name)
VALUES  ("Fred", "Fish"),
        ("Larry", "Lobster"),
        ("Bubble", "Bass");

SELECT * FROM customers;

Now we can create and populate the transactions table with data:

CREATE TABLE transactions (
    transaction_id INT PRIMARY KEY AUTO_INCREMENT,
    amount DECIMAL(5, 2),
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
ALTER TABLE transactions
AUTO_INCREMENT = 1000;

SELECT * FROM transactions;

This has created a transactions table with its own auto incrementing PRIMARY KEY, an amount and a reference to the customer_id that initiated the transaction. This customer_id is set as a FOREIGN KEY and linked to the parent tables PRIMARY KEY of the same name.

We also want the auto-incrementing of the transactions PRIMARY KEY to start at 1000.

Now we can add some transaction data to the transactions table:

INSERT INTO transactions (amount, customer_id)
VALUES (4.99, 3),
       (10.80, 2),
       (5.50, 3),
       (11.10, 1);

SELECT * FROM transactions;

We have now successfully created and linked the customers and transactions table.

---