Types of constraints (primary key, foreign key, unique, check, and not null)

Types of constraints (primary key, foreign key, unique, check, and not null) with user-defined names:

Table-Level Constraints with User-Defined Names

1. Primary Key Constraint The primary key ensures that the column(s) uniquely identify each record in the table.

CREATE TABLE employees (
employee_id INT,
employee_name VARCHAR(100),
CONSTRAINT pk_employee_id PRIMARY KEY (employee_id) -- User-defined name for primary key constraint
);

pk_employee_id is the user-defined name for the primary key constraint.

The employee_id column must have unique and non-null values.

1. Foreign Key Constraint The foreign key constraint ensures that the values in one table's column(s) match the values in another table's primary or unique column(s).

CREATE TABLE departments (
department_id INT,
department_name VARCHAR(100),
CONSTRAINT pk_department_id PRIMARY KEY (department_id) -- User-defined primary key constraint
);

CREATE TABLE employees (
employee_id INT,
employee_name VARCHAR(100),
department_id INT,
CONSTRAINT pk_employee_id PRIMARY KEY (employee_id), -- User-defined primary key constraint
CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES departments(department_id) -- User-defined name for foreign key constraint
);

fk_department is the user-defined name for the foreign key constraint.

The department_id column in employees references the department_id column in the departments table.

1. Unique Constraint The unique constraint ensures that all values in a column are distinct.

CREATE TABLE employees (
employee_id INT,
employee_email VARCHAR(100),
employee_name VARCHAR(100),
CONSTRAINT pk_employee_id PRIMARY KEY (employee_id), -- User-defined primary key constraint
CONSTRAINT unique_email UNIQUE (employee_email) -- User-defined name for unique constraint
);

unique_email is the user-defined name for the unique constraint on the employee_email column.

The employee_email must have unique values across the table.

1. Check Constraint The check constraint ensures that the values in a column meet a specific condition.

CREATE TABLE employees (
employee_id INT,
employee_name VARCHAR(100),
salary INT,
CONSTRAINT pk_employee_id PRIMARY KEY (employee_id), -- User-defined primary key constraint
CONSTRAINT chk_salary CHECK (salary > 0) -- User-defined name for check constraint
);

chk_salary is the user-defined name for the check constraint on the salary column.

The salary must be greater than 0.

1. Not Null Constraint The not null constraint ensures that a column does not contain NULL values.

CREATE TABLE employees (
employee_id INT,
employee_name VARCHAR(100) NOT NULL, -- Not null constraint on employee_name
salary INT,
CONSTRAINT pk_employee_id PRIMARY KEY (employee_id) -- User-defined primary key constraint
);

The employee_name column is explicitly defined to not allow NULL values.

The user-defined primary key is also applied to the employee_id column.

---

Table-Level Constraints Summary with User-Defined Names:

---

Key Points to Remember:

1. User-Defined Names: Constraints like PRIMARY KEY, FOREIGN KEY, UNIQUE, and CHECK can all have user-defined names for better clarity and ease of management, especially in large databases.

2. Constraints on Multiple Columns: You can apply constraints to multiple columns as needed. For example:

CREATE TABLE orders (
order_id INT,
customer_id INT,
order_date DATE,
CONSTRAINT pk_order PRIMARY KEY (order_id),
CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
CONSTRAINT chk_order_date CHECK (order_date > '2023-01-01')
);

1. Not Null Constraint: While NOT NULL does not require a user-defined name, you can still reference the column name to ensure the values are never NULL.

---

Conclusion:

In this approach, user-defined names help manage and maintain constraints more effectively, especially when performing tasks like altering, dropping, or analyzing constraints. Using descriptive names such as pk_employee_id, fk_department, or chk_salary makes it clear what each constraint is doing, helping developers and administrators understand the database schema better.