Table constraints in MySQL are rules applied to columns or tables to enforce data integrity and maintain accuracy. They help ensure that the data stored in the database meets specific criteria and adheres to the rules defined by the database designer. Here’s a detailed explanation of the different types of table constraints in MySQL:

1. Primary Key

A primary key is a column or a combination of columns that uniquely identifies each row in a table. Each table can have only one primary key, and the values in this column(s) must be unique and not NULL.

Syntax:

PRIMARY KEY (column_name)

Example:

CREATE TABLE employees (
    id INT AUTO_INCREMENT,
    name VARCHAR(100),
    position VARCHAR(100),
    salary DECIMAL(10, 2),
    PRIMARY KEY (id)
);

In this example, the id column is defined as the primary key, ensuring that each id value is unique and not NULL.

2. Foreign Key

A foreign key is a column or a set of columns in one table that refers to the primary key in another table. It establishes a relationship between two tables and enforces referential integrity by ensuring that the value in the foreign key column matches a value in the referenced table’s primary key column.

Syntax:

FOREIGN KEY (column_name) REFERENCES other_table(column_name)

Example:

CREATE TABLE departments (
    id INT AUTO_INCREMENT,
    name VARCHAR(100),
    PRIMARY KEY (id)
);

CREATE TABLE employees (
    id INT AUTO_INCREMENT,
    name VARCHAR(100),
    department_id INT,
    PRIMARY KEY (id),
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

In this example, the department_id column in the employees table is a foreign key that references the id column in the departments table.

3. Unique Constraint

A unique constraint ensures that all values in a column or a combination of columns are unique across the table. Unlike the primary key, a table can have multiple unique constraints, and unique columns can contain NULL values.

Syntax:

UNIQUE (column_name)

Example:

CREATE TABLE users (
    id INT AUTO_INCREMENT,
    username VARCHAR(100),
    email VARCHAR(100),
    PRIMARY KEY (id),
    UNIQUE (username),
    UNIQUE (email)
);

In this example, the username and email columns must have unique values across the table.

4. Not Null Constraint

The not null constraint ensures that a column cannot have NULL values. This means that every row in the table must have a value for this column.

Syntax:

column_name data_type NOT NULL

Example:

CREATE TABLE products (
    id INT AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY (id)
);

In this example, both the name and price columns must have non-NULL values for every row.

5. Default Constraint

The default constraint provides a default value for a column when no value is specified during an insert operation.

Syntax:

column_name data_type DEFAULT default_value

Example:

CREATE TABLE orders (
    id INT AUTO_INCREMENT,
    order_date DATE DEFAULT CURRENT_DATE,
    status VARCHAR(50) DEFAULT 'Pending',
    PRIMARY KEY (id)
);

In this example, the order_date column defaults to the current date, and the status column defaults to 'Pending' if no value is provided.

6. Check Constraint

The check constraint ensures that all values in a column satisfy a specific condition. This constraint is used to enforce domain integrity.

Syntax:

CHECK (condition)

Example:

CREATE TABLE employees (
    id INT AUTO_INCREMENT,
    name VARCHAR(100),
    salary DECIMAL(10, 2),
    PRIMARY KEY (id),
    CHECK (salary >= 0)
);

In this example, the salary column must have a value greater than or equal to 0.

7. Composite Key

A composite key is a primary key made up of two or more columns. It is used when a single column is not sufficient to uniquely identify rows.

Syntax:

PRIMARY KEY (column1, column2, ...)

Example:

CREATE TABLE course_enrollments (
    student_id INT,
    course_id INT,
    enrollment_date DATE,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(id)
);

In this example, the combination of student_id and course_id is used as the primary key.

8. Using Constraints in ALTER TABLE

You can add or modify constraints using the ALTER TABLE statement.

• Add a Primary Key:

  ALTER TABLE table_name
  ADD PRIMARY KEY (column_name);
  

• Add a Foreign Key:

  ALTER TABLE table_name
  ADD CONSTRAINT fk_name
  FOREIGN KEY (column_name) REFERENCES other_table(column_name);
  

• Add a Unique Constraint:

  ALTER TABLE table_name
  ADD CONSTRAINT unique_name UNIQUE (column_name);
  

• Add a Check Constraint:

  ALTER TABLE table_name
  ADD CONSTRAINT check_name
  CHECK (condition);
  

Best Practices

• Define Primary Keys: Always define a primary key for each table to ensure each row can be uniquely identified.
• Use Foreign Keys: Use foreign keys to enforce relationships between tables and maintain referential integrity.
• Enforce Data Integrity: Use constraints like UNIQUE, NOT NULL, and CHECK to enforce data validity and prevent invalid data entries.
• Be Cautious with Defaults: Ensure default values make sense and align with your data requirements.