Enforcing Referential Integrity in MariaDB: The ALTER TABLE FOREIGN KEY Syntax

REFERENCES parent_table_name (reference_column_list): This part specifies the referenced table and column(s).

• reference_column_list: A comma-separated list of columns in the parent table that correspond to the foreign key columns.
• parent_table_name: The name of the table that holds the columns the foreign key references.

Additional Clauses (Optional):

• ON UPDATE: This clause specifies how to handle updates to the referenced columns in the parent table. It has the same options as ON DELETE.
• ON DELETE: This clause specifies how to handle deletion of rows in the parent table that are referenced by foreign keys. Here are the options:
  • RESTRICT (default): This prevents deletion if it would violate the referential constraint.
  • CASCADE: This automatically deletes child rows that reference the deleted parent row.
  • SET NULL: This sets the foreign key column to NULL in the child table if the referenced parent row is deleted.
  • NO ACTION: This throws an error if the deletion would violate the constraint.

Here's an example of adding a foreign key constraint named fk_orders_customer_id to the orders table. This constraint ensures the customer_id in the orders table references the id column in the customers table:

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer_id
FOREIGN KEY (customer_id)
REFERENCES customers(id)
ON DELETE CASCADE;

In this example, if a row is deleted from the customers table, any corresponding rows in the orders table that reference the deleted customer's ID will also be automatically deleted due to the ON DELETE CASCADE clause.

This example adds a foreign key constraint named fk_book_author to the books table. The author_id column in books will reference the id column in the authors table.

ALTER TABLE books
ADD CONSTRAINT fk_book_author
FOREIGN KEY (author_id)
REFERENCES authors(id);

Example 2: Foreign Key with ON DELETE CASCADE

This example adds a foreign key constraint named fk_order_product to the orders table. The product_id column in orders will reference the id column in the products table. If a product is deleted from the products table, any corresponding orders referencing that product will also be deleted.

ALTER TABLE orders
ADD CONSTRAINT fk_order_product
FOREIGN KEY (product_id)
REFERENCES products(id)
ON DELETE CASCADE;

This example adds a foreign key constraint named fk_course_instructor to the courses table. The instructor_id column in courses will reference the id column in the instructors table. If an instructor's record is updated in the instructors table (e.g., name change), any courses referencing that instructor will have their instructor_id set to NULL.

ALTER TABLE courses
ADD CONSTRAINT fk_course_instructor
FOREIGN KEY (instructor_id)
REFERENCES instructors(id)
ON UPDATE SET NULL;

1. Using CREATE TABLE: This is the most common approach and involves defining the foreign key constraint along with the table structure itself. The FOREIGN KEY clause is included within the CREATE TABLE statement.

Here's an example:

CREATE TABLE orders (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
  ON DELETE CASCADE
);

1. Using ALTER TABLE (During Initial Creation): If you're creating a new table and immediately want to define foreign key relationships, you can use ALTER TABLE right after the CREATE TABLE statement. This achieves the same outcome as method 1, but separates the table definition and foreign key creation into two statements.

CREATE TABLE orders (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NOT NULL
);

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer_id
FOREIGN KEY (customer_id) REFERENCES customers(id)
ON DELETE CASCADE;

While both methods achieve the same result, using FOREIGN KEY within CREATE TABLE is generally considered more concise and easier to maintain.

Other Considerations:

• Data Integrity Checks: Regardless of the method used, MariaDB will perform data integrity checks whenever a foreign key constraint is added. This ensures existing data adheres to the newly defined relationship between tables.
• Schema Changes: If you're altering an existing database schema and need to add foreign key constraints to already created tables, then ALTER TABLE with the FOREIGN KEY clause is the only option.