Using constraints in database design is essential to enforce data integrity, ensure data consistency, and prevent invalid data entry. Here’s when and how to use various constraints effectively:
1. PRIMARY KEY Constraint
Purpose: Uniquely identifies each record in a table.
When to Use: Every table should have a primary key. Use it for columns or a combination of columns that uniquely identify a row.
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50)
);
2. FOREIGN KEY Constraint
Purpose: Ensures referential integrity by linking columns in one table to the primary key or a unique key in another table.
When to Use: Use it to define relationships between tables (one-to-one, one-to-many, many-to-many).
department_id INT PRIMARY KEY,
department_name VARCHAR(50)
);
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
department_id INT,
FOREIGN KEY (department_id) REFERENCES departments(department_id)
);
3. NOT NULL Constraint
Purpose: Ensures that a column cannot have NULL values.
When to Use: Use it for columns that must always have a value, such as primary key columns and essential attributes.
employee_id INT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL
);
4. CHECK Constraint
Purpose: Ensures that all values in a column satisfy a specific condition.
When to Use: Use it to enforce domain integrity by restricting the values that can be inserted into a column.
employee_id INT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
age INT CHECK (age >= 18 AND age <= 65)
);
5. DEFAULT Constraint
Purpose: Provides a default value for a column when no value is specified.
When to Use: Use it to ensure a column has a default value if none is provided upon insertion.
employee_id INT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
hire_date DATE DEFAULT CURRENT_DATE
);
