What is primary key?

A primary key is a unique identifier for a record. Let us create a simple users table.

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    lastUpdate DATE
);

In the above statement for id column there are AUTO_INCREMENT and PRIMARY KEY are assigned. The AUTO_INCREMENT is used to increment integer value by one automatically. And PRIMARY KEY making it a primary key.

Let's create some records by using the insert query.

INSERT INTO users (name, lastUpdate) VALUES
 ('Alice', '2023-05-12'),
 ('Bob', '2023-05-12'),
 ('Charlie', '2023-05-12');

This will generate following values in the table.

Primary key only stores the unique records only. That means id columns contains only unique values.

Let's see what happens when we try to make the duplicate records in the primary key value by running this query.

UPDATE users SET id=1 WHERE id=3;

It gives the following error if we try to insert identical values for the primary key.

Error Code: 1062. Duplicate entry '1' for key 'users.PRIMARY'

Primary key does not allow the null value.

What is foreign key?

The key which is a reference from primary key from the another table. To provide a example for foreign key, let's create a following posts table.

CREATE TABLE posts (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  description TEXT,
  userId INT NOT NULL,
  CONSTRAINT fk_user_id FOREIGN KEY (userId) REFERENCES users(id)
);

Let's add some posts by using the following insert query.

INSERT INTO posts (title, description, userId)
VALUES ("Introduction to Machine Learning", "This blog post provides an overview of the fundamentals of machine learning...", 1),
       ("Tips for Effective Data Visualization", "Learn how to create clear and compelling data visualizations to communicate...", 2),
       ("Exploring the World of Deep Learning", "Dive into the world of deep learning, a powerful subset of machine learning...", 3);

Then table would be like this.

What happens if we try to add userId which is not exist in the users table? Let's try here user with id 5 is not exist here.

INSERT INTO sakila.posts (title, description, userId)
VALUES ("Introduction to Machine Learning new", "This blog post provides an overview of the fundamentals of machine learning new...", 5);

It will return the following error message.

Cannot add or update a child row: a foreign key constraint fails (`posts`, CONSTRAINT `fk_user_id` FOREIGN KEY (`userId`) REFERENCES `users` (`id`))	

This is because id value 5 in users table is not exist. Since userId is taking reference from the users table id column.

Handle cascade on delete and cascade on update.

Let's add the on delete cascade clause

ALTER TABLE posts 
drop foreign key fk_user_id;

ALTER TABLE posts 
add foreign key (userId) references users(id) on delete cascade;

Now if we delete the user then associated posts automatically deleted from the users table.

DELETE from users where id=1;

Likewise There are some other options as cascade.

No Action It do nothing during the user delete.

ALTER TABLE posts 
add foreign key (userId) references users(id) on delete no action;

Set Null It set the userId value to null after related user deleted. However userId should be nullable field during creation of the posts table.

ALTER TABLE posts 
add foreign key (userId) references users(id) on delete no action;

Restrict It will restrict the operation if there is related post is available.

ALTER TABLE posts 
add foreign key (userId) references users(id) on delete restrict;

ON UPDATE CASCASE Whenever we update the user id then related userId in posts table automatically updated.

ALTER TABLE posts 
add foreign key (userId) references users(id) on delete cascade on update cascade;

UPDATE sakila.users SET id=200 where id=2; 

Then posts data will look like this.

Likewise we can use the no action, restrict and set null on the update as well.

These will help make the good relation with the tables and maintain the integrity after records update and delete (database transactions).