Constraints In MySQL

In MySQL, Constraints are rules that are applied to the columns of a table to ensure the integrity and consistency of the data stored in the database.

Constraints define certain conditions that must be satisfied for data manipulation operations (such as INSERT, UPDATE, or DELETE) to be allowed.

Syntax of Constraints in MySQL Table:

Primary Key Constraint in MySQL Table:

Ensures that each row in a table can be uniquely identified by a specific column or combination of columns. A Table can have only one primary key.

Example:

CREATE TABLE products (
    product_code INT PRIMARY KEY,
    product_name VARCHAR(50)
);

Unique Constraint in MySQL Table:

Ensures that the values in a column (or a set of columns) are unique across all rows in the table.

Example:

CREATE TABLE products (
    product_code INT,
    product_id VARCHAR(50) UNIQUE,
);

Foreign Key Constraint in MySQL Table:

Establishes a link between two tables by enforcing referential integrity.

Example:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    product_code INT,
    FOREIGN KEY (product_code) REFERENCES products(product_code)
);

CREATE TABLE products (
    product_code INT PRIMARY KEY,
    product_name VARCHAR(50) NOT NULL,
    product_description VARCHAR(50) NOT NULL,
    product_price VARCHAR(100) UNIQUE NOT NULL
);

NOT NULL Constraint in MySQL Table:

Ensures that a column cannot have a NULL value instead it should contain some meaningful value in the column.

Example:

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(50) NOT NULL
);

Check Constraint in MySQL Table:

Specifies a condition that must be true for each row in a table.

Example:

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    price DECIMAL(10, 2) CHECK (price >= 0)
);

Default Constraint In MySQL Table:

Specifies a default value for a column, which is used if no value is explicitly provided during an INSERT operation.

Example:

CREATE TABLE products (
    product_code INT PRIMARY KEY,
    product_added_date DATE DEFAULT CURRENT_DATE
);

Multiple Constraints In MySQL Table:

Create a `products` table with various constraints in the `products` table.

CREATE TABLE products (
    product_code INT PRIMARY KEY,
    product_name VARCHAR(50) NOT NULL, 
    product_description VARCHAR(50) NOT NULL, 
    product_id VARCHAR(100) UNIQUE NOT NULL,  
    product_arrival_date DATE NOT NULL,   
    product_price DECIMAL(10, 2) CHECK (salary > 0),  
    product_status VARCHAR(20) DEFAULT 'Active',  
    FOREIGN KEY (catalog_id) REFERENCES catalog(catalog_id) 
);

<-- Alter Table

Not Null -->