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 ALTER.

• Typically we define NOT NULL, CHECK constraints while creating the tables. However, we can also specify not null constraints as well as check constraints to the columns while adding columns using ALTER TABLE.

• FOREIGN KEY constraints 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.

• PRIMARY KEY and UNIQUE constraints might be added as part of CREATE table statements or ALTER table statements. Both are commonly used practices.

• Let us compare and contrast PRIMARY KEY and UNIQUE constraints.

  • There can be only one PRIMARY KEY in a table where as there can be any number of UNIQUE constraints.

  • UNIQUE columns can have null values unless NOT NULL is 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 KEY from a child table can be defined against PRIMARY KEY column or UNIQUE column.

  • Typically PRIMARY KEY columns are surrogate keys which are supported by sequence.

  • PRIMARY KEY or UNIQUE can be composite. It means there can be more than one column to define PRIMARY KEY or UNIQUE constraint.

• 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.