In relational databases, constraints are rules applied to table columns to enforce data integrity and consistency. They prevent invalid data entry and ensure relationships between tables are maintained. The most common constraints are Primary Key, Foreign Key, UNIQUE, and CHECK.
1. Primary Key (PK)
A Primary Key uniquely identifies each record in a table.
Key Points
- Each table can have only one primary key.
- The primary key column(s) cannot contain NULL values.
- Commonly used on ID columns to ensure uniqueness.
Example
If we have a table called Students with columns StudentID, Name, and Email, we can set StudentID as the primary key. This ensures no two students share the same ID.
2. Foreign Key (FK)
A Foreign Key establishes a relationship between two tables by linking a column in one table to the primary key of another table.
Key Points
- Ensures referential integrity between tables.
- Values in the foreign key column must exist in the referenced primary key column.
- Helps maintain consistent relationships, such as orders linked to customers.
Example
A table Orders has a column CustomerID that references CustomerID in the Customers table. This ensures each order is associated with a valid customer.
3. UNIQUE Constraint
The UNIQUE constraint ensures all values in a column are distinct across records.
Key Points
- Multiple UNIQUE constraints can exist in a table.
- Unlike the primary key, a UNIQUE column can allow NULL values (unless otherwise specified).
Example
In a Users table, the Email column can be set as UNIQUE to prevent multiple users from registering with the same email address.
4. CHECK Constraint
The CHECK constraint ensures that all values in a column satisfy a specific condition.
Key Points
- Can be applied to one or more columns.
- Useful for enforcing domain rules or business logic.
- Prevents insertion of invalid data.
Example
In an Employees table, a CHECK constraint can ensure that the Age column only contains values greater than 18.
Summary
Constraints help maintain data accuracy, integrity, and consistency in relational databases.
- Primary Key: Ensures each record is unique and not NULL.
- Foreign Key: Maintains relationships between tables.
- UNIQUE: Prevents duplicate values.
- CHECK: Validates data against a condition.