Data Constraints and Defaults in PostgreSQL

1. Introduction

In PostgreSQL, data integrity is crucial for maintaining accurate and reliable data. Data constraints and defaults help enforce rules on data input and ensure that data adheres to specified formats and conditions.

2. Data Constraints

Data constraints are rules that limit the type of data that can be inserted into a table. PostgreSQL supports several types of constraints, including:

• NOT NULL
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY
• CHECK

2.1 NOT NULL

The NOT NULL constraint enforces that a column cannot have a NULL value.

2.2 UNIQUE

The UNIQUE constraint ensures that all values in a column are distinct.

2.3 PRIMARY KEY

A PRIMARY KEY constraint is a combination of NOT NULL and UNIQUE. It uniquely identifies each row in a table.

2.4 FOREIGN KEY

A FOREIGN KEY constraint ensures referential integrity by linking two tables.

2.5 CHECK

The CHECK constraint ensures that all values in a column satisfy a specific condition.

3. Defaults

A default value is assigned to a column when no value is specified during data insertion. This can be useful for ensuring that certain columns always have a value.

3.1 Defining a Default Value

Default values can be defined in the column definition during table creation or altered later. The syntax for setting a default value is:

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    hire_date DATE DEFAULT CURRENT_DATE
);

4. Examples

Below are examples demonstrating the use of data constraints and defaults:

4.1 Creating a Table with Constraints

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL UNIQUE,
    price NUMERIC CHECK (price > 0),
    category_id INT,
    FOREIGN KEY (category_id) REFERENCES categories(category_id)
);

4.2 Adding a Default Value

ALTER TABLE employees
ADD COLUMN status VARCHAR(10) DEFAULT 'active';

5. Best Practices

• Always validate data before insertion to ensure integrity.
• Use NOT NULL for mandatory fields to avoid unexpected NULLs.
• Define appropriate default values to improve user experience.
• Utilize CHECK constraints to enforce business rules on data.

6. FAQ