MySQL Note: Foreign Key in MySQL

• The FOREIGN KEY constraint establishes relationships between tables by referencing the primary key of another table.
• Take a look at the following diagram, let say we have two tables in a database:

• You can think of a foreign key as a primary key from 1 table that can be found within a different table.
• Using a foreign key, we can establish a link between 2 tables.

• Benefit 1: In the transactions table, if we want to look at the customer ID of who initiated the transaction, we can look at the customers table and find the name of that customer. (This will discuss in detail under topic: Join)
• Benefit 1: When we create a foreign key constraint, we will also create a link between two tables which prevents any actions that would destroy that link between them.

• To simulate this, let's create a table called customers:

CREATE TABLE customers {
	customer_id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR (255)
);

• Then, insert data:

INSERT INTO customers (name)
VALUES 	("Hans"),
		("John"),
		("Laila");

SELECT * FROM customers;

• Now, let's create a link between our customers table and our transactions table via the customer_id.

• Now, creat the second table, which is table transactions:

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;

• At the sidebar panel, go to "Tables" >> "transactions" >> "Foregn Keys". This section will display any foreign keys that are applied to this table. So, let's say it shows: transactions_ibfk_1

• To drop a foreign key, type in and execute the following code:

ALTER TABLE transactions
DROP FOREIGN KEY transactions_ibfk_1;

• To modify a foreign key name, into a table that already exist, the syntax will be as follows:

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

• Refresh the sidebar panel, and check if the name had changed.

• For demonstration purposes, make sure the transactions table contain empty row. Execute the following code to delete all rows and display the table:

DELETE FROM transactions;
SELECT * FROM transactions;

• Now, let's modify the auto increment, to start at 1000. We can do as follows:

ALTER TABLE transactions AUTO_INCREMENT = 1000;

• Then, insert the new rows.

INSERT INTO transaction(amount, customer_id)
	VALUES 	(4.99, 3)
			(2.86, 2)
			(3.33, 3)
			(2.15, 1);
SELECT * FROM transaction;

• Try to delete the customer_id and see what happen:

DELETE FROM customers WHERE customer_id = 3;

• We will get an error: Cannot delete or update a parent row, a foreign key constraint fails..