Database Essentials using Postgres - Creating Tables and Indexes - Different Types of Constraints

Databases Postgres
1

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 and 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. They are primarily used to define relationships between 2 tables - for example, users as the parent table and user_login_details as the child table with a 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’s compare and contrast PRIMARY KEY and UNIQUE constraints:

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

    • UNIQUE columns can have null values unless NOT NULL is also enforced. In the case of a PRIMARY KEY, both uniqueness and not null are strictly enforced. In other words, a primary key column cannot be null, whereas a unique column can be null.

    • A FOREIGN KEY from a child table can be defined against a PRIMARY KEY column or a UNIQUE column.

    • Typically, PRIMARY KEY columns are surrogate keys supported by sequences.

    • PRIMARY KEY or UNIQUE constraints can be composite, meaning there can be more than one column to define the PRIMARY KEY or UNIQUE constraint.

  • Let’s take an example of an LMS (Learning Management System):

    • USERS - it contains columns such as user_id, user_email_id, user_first_name, etc. We can enforce a primary key constraint on user_id and a unique constraint on user_email_id.

    • COURSES - it contains columns such as course_id, course_name, course_price, etc. A 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. However, they can enroll for one course only once.

      • It contains fields such as student_id, user_id, course_id, amount_paid, enrolled_dt, etc.

      • A 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 a unique constraint enforced on students.user_id and students.course_id. It will be a composite key as it has more than one column.

Watch the video tutorial here