Defining Relationships Between Tables in MySQL

In MySQL, the relationships between tables are defined through the use of matching columns referred to as primary keys and foreign keys.

They allow to uniquely identify each record in a table ensuring data integrity and referential integrity across the database.

Setting a primary key

A primary key is a non-null column or set of columns that uniquely identifies each record in a table.

Primary keys can essentially be of three types:

• A natural key is a single column based on a real-world observable, such as an email address, a social security number, or a phone number.
• A surrogate key is a single column based on a value specifically created to be used as a key within the boundaries of the database and that has no meaning in the real-world, such as a numerical identifier that is automatically incremented by the database itself whenever a new record is created.
• A composite key is a set of columns that when considered separately may not be unique, but when taken altogether are guaranteed to ensure uniqueness, such as the combination of a first name, a last name, and an address.

To define a column as a primary key of a table, you can use the PRIMARY KEY constraint:

CREATE TABLE table_name(
  column_name column_type [...] PRIMARY KEY,
  ...
);

Example

This SQL statement will create a new table named customers:

mysql> CREATE TABLE customers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(320) NOT NULL UNIQUE
);

Where the self-incrementing id column is defined as the primary surrogate key of the table.

Setting foreign keys

A foreign key is a column or set of columns that establishes a relationship with a primary key in another table.

It can either contain a NULL value or a valid reference to an existing primary key to preserve the referential integrity.

It ensures the consistency and validity of relationships between tables, which helps prevent "orphaned" records, either by inserting a record with an inexistent foreign key or deleting a record that is referenced by a foreign key.

Note: While a table can have multiple foreign keys, it can only have one primary key.

To define a column as foreign key, you can use the FOREIGN KEY keywords:

CREATE TABLE table_name (
  column_name [...],
  ...,
  [CONSTRAINT constraint_name] FOREIGN KEY (column_name) REFERENCES foreign_table_name (foreign_column_name)
);

Where:

• CONSTRAINT constraint_name is an optional name used to reference the foreign key.
• FOREIGN KEY (column_name) is used to specify the column that will serve as a foreign key in the current table.
• REFERENCES foreign_table_name (foreign_column_name) is used to specify the column that serves as the primary key in the reference table.

Note: If the CONSTRAINT constraint is not specified, MySQL will automatically create a new constraint based on the table name (e.g., orders_ibfk_1).

Example

This SQL statement will create a new table named orders:

CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NOT NULL,
  product VARCHAR(50) NOT NULL UNIQUE,
  quantity INT UNSIGNED NOT NULL,
  FOREIGN KEY(customer_id) REFERENCES customers(id)
);

Where:

• The id column is defined as the primary surrogate key of the table.
• The customer_id column is defined as a foreign key that references the id column of the customers table.

Modifying primary and foreign keys

Adding a primary key

To set the column of a table as primary key, you can use the ALTER TABLE keywords combined with the ADD PRIMARY KEY keywords:

ALTER TABLE table_name
ADD PRIMARY KEY (column_name);

Adding a foreign key

To set the column of a table as foreign key, you can use the ALTER TABLE keywords combined with the ADD CONSTRAINT keywords:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name FOREIGN KEY (column_name) REFERENCES foreign_table_name (foreign_column_name)

Dropping a primary key

To remove the primary key constraint from a column, you can use the ALTER TABLE keywords combined with the DROP PRIMARY KEY keywords:

ALTER TABLE table_name
DROP PRIMARY KEY;

Dropping foreign keys

To remove the foreign key constraint from a column, you can use the ALTER TABLE keywords combined with the DROP FOREIGN KEY keywords:

ALTER TABLE table_name
DROP FOREIGN KEY constraint_name;

Summary

Here's a summary of what you've learned in this lesson:

• A primary key is a column (or set of columns) that identifies each record in a table.
• A foreign key is a column (or set of columns) in a table that establishes a relationship with a primary key in another table.
• The PRIMARY KEY constraint is used to set a column as the primary key of a table.
• The FOREIGN KEY constraint is used to set a column as a foreign key of a table.
• The ADD PRIMARY KEY keywords are used to set an existing column as the primary key of a table.
• The ADD CONSTRAINT with FOREIGN KEY keywords are used to set an existing column as a foreign key of a table.
• The DROP PRIMARY KEY keywords are used to unset the primary key of a table.
• The DROP FOREIGN KEY keywords are used to unset a column as a foreign key of a table.