MySQL - Primary Key & Foreign Key

In MySQL, a primary key is a unique identifier for each row in a table. It is a column or a set of columns that uniquely identifies each record in the table. The primary key ensures that each row is unique and can be referenced from other tables.

Here are the rules and an example of creating a primary key in MySQL:

Rules:

• The primary key column(s) must contain unique values.
• The primary key column(s) cannot contain NULL values.
• Each table can have only one primary key.
• The primary key can be a single column or a combination of columns.

Example:

Let's say we have a table named "students" that contains student information such as ID, name, and age. We want to create a primary key for the "students" table.

Creating the "students" table with a primary key:

CREATE TABLE students (

  id INT PRIMARY KEY,

  name VARCHAR(50),

  age INT

);

In this example, we created a primary key on the "id" column. This column is unique for each student and cannot be NULL. We specified the PRIMARY KEY constraint after the column definition to create a primary key.

When we insert data into the "students" table, the primary key constraint will be enforced. For example, if we try to insert a student with an existing id, MySQL will raise an error because it violates the PRIMARY KEY constraint.

INSERT INTO students (id, name, age) VALUES (1, 'John', 18);

ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

This means that the student with id 1 already exists in the "students" table, and we cannot insert another student with the same id.

In summary, a primary key is a unique identifier for each row in a table. It is essential for maintaining data integrity and ensuring that each row can be referenced from other tables.

In MySQL, a foreign key is a constraint that is used to enforce referential integrity between two tables. It is a column or a set of columns in one table that refers to the primary key of another table. The foreign key ensures that the data in the referencing table matches the data in the referenced table.

Here are the rules and an example of creating a foreign key in MySQL:

Rules:

• The foreign key column(s) must have the same data type as the referenced column(s).
• The foreign key column(s) must have a value that matches a value in the referenced column(s).
• The foreign key constraint can only reference a primary key or a unique key in the referenced table.
• If a foreign key references a primary key, it must have the same number of columns as the primary key.

Example:

Let's say we have two tables, "orders" and "customers". The "orders" table has a foreign key column that refers to the "customers" table.

Creating the "customers" table:

CREATE TABLE customers (

  id INT PRIMARY KEY,

  name VARCHAR(50)

);

Creating the "orders" table with a foreign key:

CREATE TABLE orders (

  id INT PRIMARY KEY,

  customer_id INT,

  order_date DATE,

  FOREIGN KEY (customer_id) REFERENCES customers(id)

);

In the "orders" table, the "customer_id" column is a foreign key that refers to the "id" column in the "customers" table. The FOREIGN KEY constraint ensures that every value in the "customer_id" column of the "orders" table must match a value in the "id" column of the "customers" table.

When we insert data into the "orders" table, the foreign key constraint will be enforced. For example, if we try to insert an order with a non-existing customer_id, MySQL will raise an error because it violates the FOREIGN KEY constraint.

INSERT INTO orders (id, customer_id, order_date) VALUES (1, 5, '2022-01-01');

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`))

This means that the order with id 1 cannot be inserted because the customer_id 5 does not exist in the "customers" table.