How to set up foreign keys in mysql?

@lizzie 

To set up foreign keys in MySQL, follow these steps:

1. Start by creating the tables with the necessary columns in your database. For example, suppose we have two tables: "customers" and "orders". The "customers" table has a primary key called "customer_id", and the "orders" table has a foreign key called "customer_id".
2. Use the "ALTER TABLE" statement to add a foreign key constraint to the column in the child table. The syntax for adding a foreign key is as follows:

1
2
3
4

ALTER TABLE child_table
  ADD CONSTRAINT constraint_name
  FOREIGN KEY (child_column)
  REFERENCES parent_table(parent_column);

For example, to set "customer_id" in the "orders" table as a foreign key referencing "customer_id" in the "customers" table, you would run the following command:

1
2
3
4

ALTER TABLE orders
  ADD CONSTRAINT fk_orders_customers
  FOREIGN KEY (customer_id)
  REFERENCES customers(customer_id);

This statement creates a foreign key constraint named "fk_orders_customers" that references the "customer_id" column in the "customers" table.

1. Optionally, you can specify additional actions to be performed when certain events occur by using the "ON DELETE" and "ON UPDATE" clauses. For example, to specify that the corresponding row in the child table should be deleted when the referenced row in the parent table is deleted, use the "ON DELETE CASCADE" clause:

1
2
3
4
5

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

In this example, when a customer is deleted from the "customers" table, all corresponding orders in the "orders" table will also be deleted.

1. Repeat the process to add foreign key constraints to other tables as needed.

Note: Before adding foreign key constraints, make sure that the referenced columns in the parent table have appropriate indexes.