Constraints in MySQL Workbench

• In MySQL Workbench, constraints are rules that you can define to enforce data integrity and consistency in your database tables. They ensure that the data stored in the tables follows certain rules and guidelines.

Here's a detailed explanation of various constraints available in MySQL Workbench, along with examples:

• Primary Key Constraint: A primary key constraint ensures that a column (or a combination of columns) uniquely identifies each row in a table. It prevents duplicate or NULL values.

    CREATE TABLE students (

        student_id INT PRIMARY KEY,

        first_name VARCHAR(50),

        last_name VARCHAR(50)

    );

• Unique Constraint: A unique constraint enforces that the values in a column (or a combination of columns) are unique across all rows in the table.

    CREATE TABLE employees (

        employee_id INT PRIMARY KEY,

        email VARCHAR(100) UNIQUE,

        department VARCHAR(50)

    );

• Foreign Key Constraint: A foreign key constraint establishes a relationship between two tables by linking a column in one table to a primary key in another table.

    CREATE TABLE orders (

        order_id INT PRIMARY KEY,

        customer_id INT,

        order_date DATE,

        FOREIGN KEY (customer_id) REFERENCES customers(customer_id)

    );

• Check Constraint: A check constraint specifies a condition that must be satisfied for the values in a column.

    CREATE TABLE products (

        product_id INT PRIMARY KEY,

        product_name VARCHAR(100),

        price DECIMAL(10, 2),

        stock_quantity INT,

        CHECK (price > 0 AND stock_quantity >= 0)

    );

• Default Constraint: A default constraint assigns a default value to a column when a new row is inserted without explicitly providing a value for that column.

    CREATE TABLE employees (

        employee_id INT PRIMARY KEY,

        first_name VARCHAR(50),

        last_name VARCHAR(50),

        hire_date DATE DEFAULT CURRENT_DATE

    );

• Not Null Constraint: A not null constraint enforces that a column cannot contain NULL values.

    CREATE TABLE customers (

        customer_id INT PRIMARY KEY,

        customer_name VARCHAR(100) NOT NULL,

        email VARCHAR(100)

    );

• Enum Constraint: An enum constraint restricts the values that can be stored in a column to a predefined set of values.

    CREATE TABLE colors (

        color_id INT PRIMARY KEY,

        color_name ENUM('Red', 'Green', 'Blue')

    );

• Auto Increment Constraint: An auto increment constraint automatically generates a unique value for a column whenever a new row is inserted.

    CREATE TABLE employees (

        employee_id INT PRIMARY KEY AUTO_INCREMENT,

        first_name VARCHAR(50),

        last_name VARCHAR(50)

    );

• These are some of the common constraints available in MySQL Workbench. Constraints ensure data accuracy, integrity, and consistency within your database tables, and they play a crucial role in maintaining the quality of your data.