Different Types of Constraints¶
Let us understand details about different types of constraints used in RDBMS databases.
- Supported constraints:
- NOT NULL constraint
- CHECK constraint
- UNIQUE constraint
- PRIMARY KEY constraint
- FOREIGN KEY constraint
- All constraints can be added while creating the table or on pre-created tables using
- Typically we define
CHECKconstraints while creating the tables. However, we can also specify not null constraints as well as check constraints to the columns while adding columns using
FOREIGN KEYconstraints are created after the tables are created. It is primarily used to define relationship between 2 tables – example: users is parent table and user_login_details is child table with one to many relationship between them.
UNIQUEconstraints might be added as part of CREATE table statements or ALTER table statements. Both are commonly used practices.
- Let us compare and contrast
- There can be only one
PRIMARY KEYin a table where as there can be any number of
UNIQUEcolumns can have null values unless
NOT NULLis also enforced. In case of
PRIMARY KEY, both uniqueness as well as not null are strictly enforced. In other words a primary key column cannot be null where as unique column can be null.
FOREIGN KEYfrom a child table can be defined against
PRIMARY KEYcolumn or
PRIMARY KEYcolumns are surrogate keys which are supported by sequence.
UNIQUEcan be composite. It means there can be more than one column to define
- There can be only one
- Let’s take an example of LMS (Learning Management System).
- USERS – it contains columns such as user_id, user_email_id, user_first_name etc. We can enforce primary key constraint on user_id and unique constraint on user_email_id.
- COURSES – it contains columns such as course_id, course_name, course_price etc. Primary key constraint will be enforced on course_id.
- STUDENTS – A student is nothing but a user who is enrolled for one or more courses. But he can enroll for one course only once.
- It contains fields such as student_id, user_id, course_id, amount_paid, enrolled_dt etc.
- Primary key constraint will be enforced on student_id.
- A foreign key constraint can be enforced on students.user_id against users.user_id.
- Another foreign key constraint can be enforced on students.course_id against courses.course_id.
- Also we can have unique constraint enforced on students.user_id and students.course_id. It will be composite key as it have more than one column.