How do you add constraints to a table in MySQL (SQL)?

 In MySQL, you can add constraints to a table using the ALTER TABLE statement. Constraints define rules for the data in the table to ensure integrity, such as primary keys, foreign keys, unique constraints, and others.

Here’s how to add some common types of constraints:

1. Adding a Primary Key

If the table doesn't already have a primary key, you can add one using:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name PRIMARY KEY (column_name);

2. Adding a Foreign Key

To add a foreign key constraint, you need to specify the column in the current table and the reference column in another table:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name FOREIGN KEY (column_name) REFERENCES referenced_table (referenced_column);

3. Adding a Unique Constraint

A unique constraint ensures that all values in a column or a combination of columns are unique:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column_name);

4. Adding a Check Constraint

A check constraint ensures that all values in a column meet a specific condition. Note that this is supported starting from MySQL 8.0.16:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name CHECK (condition);

5. Adding a Default Constraint

You can add a default value for a column in a table. For example, setting the default value of a column to 0:

ALTER TABLE table_name
ALTER column_name SET DEFAULT 0;

Example:

Let’s say we have a table employees, and we want to:

• Add a primary key on id,
• Add a foreign key that references another table departments,
• Add a unique constraint on email.

ALTER TABLE employees
ADD CONSTRAINT pk_employees PRIMARY KEY (id),
ADD CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES departments (id),
ADD CONSTRAINT uq_email UNIQUE (email);

Removing Constraints:

To remove a constraint, use DROP CONSTRAINT:

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

In some cases, you may need to use DROP INDEX or DROP FOREIGN KEY depending on the constraint type.

Let me know if you need more specific examples!