How to add a column and make it a foreign key in single MySQL statement?

In MySQL, adding a column and defining it as a foreign key in a single statement typically involves using the ALTER TABLE statement to add the column and combining it with ADD CONSTRAINT to specify the foreign key.

Here's a concrete example: Suppose we have two tables: students and classes. The students table contains fields student_id and name, while the classes table contains fields class_id and class_name. We aim to add a new column class_id to the students table and set it as a foreign key referencing the class_id column in the classes table.

The corresponding MySQL statement is:

sql
ALTER TABLE students
ADD COLUMN class_id INT,
ADD CONSTRAINT fk_class_id
FOREIGN KEY (class_id)
REFERENCES classes(class_id);

This statement performs the following steps:

1. ADD COLUMN class_id INT - Adds a new integer column named class_id to the students table.
2. ADD CONSTRAINT fk_class_id - Defines a constraint named fk_class_id.
3. FOREIGN KEY (class_id) - Specifies the class_id column as the foreign key.
4. REFERENCES classes(class_id) - References the class_id column in the classes table.

This successfully adds the class_id column to the students table and sets it as a foreign key pointing to the class_id column in the classes table. Consequently, each student can be associated with a class, and the database automatically enforces data integrity for class_id.