[MySQL] Day 11. FOREIGN KEY constraint

You can think of FOREIGN KEY as a primary key from one table that can be found within a different table.

FOREIGN KEY constraint is used to prevent actions that would destory links between tables.

Using a FOREIGN KEY, we can establish a link between two tables. 

 

Let's stary by creating a new table for customer data. 

We will have customer_id, first_name, and last_name columns, and customer_id will be the PRIMARY KEY.

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

SELECT * FROM customers;

 

Let's insert some values. 

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

SELECT * FROM customers;

 

Next, we will make a link between a new table transaction table and customer table by using the FOREIGN KEY constraint. 

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)
);

SELECT * FROM transactions;

 

We can add FOREIGN KEY constraint by specifying the column in current table, and referencing table with the column.

We added FOREIGN KEY constraint to customer_id column, referencing the column with the same name in customers table.

 

transactions table references customer_id FOREIGN KEY from customers table to select a specific customer_id, because customer_id in customers table is a PRIMARY KEY, meaning that there must be no duplicates in customer_id column. 

In MySQL Workbench, you can check the FOREIGN KEY constraints under the table. 

The name of our FOREIGN KEY constraint transactions_ibfk_1. 

 

 

If we want to drop a FOREIGN KEY constraint, we have to specify the name that we checked above. 

ALTER TABLE transactions
DROP FOREIGN KEY transactions_ibfk_1;

 

 

If you want to add a FOREIGN KEY constraint to an existing table, you can do this. 

ALTER TABLE transactions
ADD CONSTRAINT fk_customer_id
FOREIGN KEY(customer_id) REFERENCES customers(customer_id);

You can also set a unique name for a FORIENG KEY constraint by specifying it. 

In our case, I named it as fk_customer_id. 

 

 

Now let's delete all rows to do reset and add some new rows, and set AUTO_INCREMENT to start from 1000.

DELETE FROM transactions;

ALTER TABLE transactions 
AUTO_INCREMENT = 1000;

 

Now let's insert some new rows.

INSERT INTO transactions (amount, customer_id)
VALUES  (4.99, 3),
        (2.89, 2),
        (3.38, 3),
        (4.99, 1);

SELECT * FROM transactions;

 

Let's try adding another transaction with customer_id data of 10.

INSERT INTO transactions (amount, customer_id)
VALUES  (10.99, 10);

SELECT * FROM transactions;
-- Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`new_db`.`transactions`, CONSTRAINT `fk_customer_id` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`))

It makes an error because there is no customer_id data of 10 in customers column. 

There are only 1, 2, and 3 for customer_id in customers table, so that FOREIGN KEY constraint limits new data for customer_id in transactions table to 1, 2, and 3. 

 

Now, what if we try to delete one of the rows that is referenced?

DELETE FROM customers 
WHERE customer_id = 3;
--Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`new_db`.`transactions`, CONSTRAINT `fk_customer_id` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`))

이것 역시 FOREIGN KEY로 established된 link를 파괴하게 되기 때문에, FOREIGN KEY constraint가 에러를 발생시키는 것을 확인할 수 있습니다.

 

We'll learn more about FORIEGN KEYS when we learn about JOIN.