Introduction

This section of the lecture explores creating tables in PostgreSQL with constraints, which are rules enforced on data columns to ensure the accuracy and reliability of the data in your databases. 

Understanding Table Constraints

Constraints are rules applied to table columns to enforce data integrity and implement business rules. Common types of constraints include: NOT NULL : Ensures that a column cannot have a NULL value. 

 1. PRIMARY KEY: Uniquely identifies each row in a table. It is a combination of NOT NULL and UNIQUE. 

 2.  UNIQUE: Ensures all values in a column are different. 

 3.  CHECK: Ensures the value in columns meets specific conditions. 

 4.  FOREIGN KEY: Ensures the integrity of the data across tables and establishes a relationship between columns in different tables. 

Creating a Table with Constraints: Example

SQL Query for Table Creation:

CREATE TABLE student_cons(
    roll_number     BIGSERIAL NOT NULL PRIMARY KEY,
    first_name      VARCHAR(50) NOT NULL,
    last_name       VARCHAR(50) NOT NULL,
    gender          VARCHAR(20) NOT NULL,
    date_of_birth   DATE NOT NULL
);

Conclusion

Incorporating constraints into your table creation process is crucial for maintaining data integrity and enforcing business logic directly at the database level. The student_cons table example illustrates how to implement these constraints effectively in PostgreSQL, ensuring that all entries are complete and correctly formatted. This approach minimizes errors and maintains the quality of your data.