Bird
Raised Fist0
PostgreSQLquery~5 mins

Column constraints (NOT NULL, UNIQUE, CHECK) in PostgreSQL

Choose your learning style10 modes available
LearnWhyDeepVisualTryChallengeProjectRecallTime
Introduction

Column constraints help keep your data clean and correct by setting simple rules on what values can be stored in each column.

When you want to make sure a column always has a value (NOT NULL).
When you want to prevent duplicate values in a column (UNIQUE).
When you want to allow only certain values based on a condition (CHECK).
When you are designing a table to store user information and need to enforce rules.
When you want to avoid mistakes like empty or invalid data in your database.
Syntax
PostgreSQL
CREATE TABLE table_name (
  column_name data_type CONSTRAINT constraint_name constraint_type,
  ...
);

-- Examples of constraint_type:
-- NOT NULL
-- UNIQUE
-- CHECK (condition)

Constraints are added when creating or altering a table.

CHECK constraints use a condition to allow only valid values.

Examples
This creates a users table where email cannot be empty and must be unique, and age must be 18 or older.
PostgreSQL
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  age INT CHECK (age >= 18)
);
Here, product name cannot be empty and price must be greater than zero.
PostgreSQL
CREATE TABLE products (
  product_id INT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  price NUMERIC CHECK (price > 0)
);
Sample Program

This creates an employees table with constraints: name cannot be empty, email must be unique if given, and salary must be at least 30000. The last insert will fail because salary is less than 30000.

PostgreSQL
CREATE TABLE employees (
  employee_id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE,
  salary NUMERIC CHECK (salary >= 30000)
);

INSERT INTO employees (name, email, salary) VALUES
('Alice', 'alice@example.com', 50000),
('Bob', NULL, 40000),
('Charlie', 'charlie@example.com', 25000);
OutputSuccess
Important Notes

NOT NULL means the column must have a value; it cannot be empty.

UNIQUE means no two rows can have the same value in that column.

CHECK lets you define a rule that values must follow, like a minimum or maximum.

Summary

Use NOT NULL to require a value in a column.

Use UNIQUE to prevent duplicate values.

Use CHECK to enforce custom rules on column values.

Practice

(1/5)
1. Which column constraint in PostgreSQL ensures that a column cannot have empty or missing values?
easy
A. PRIMARY KEY
B. NOT NULL
C. CHECK
D. UNIQUE

Solution

  1. Step 1: Understand the NOT NULL constraint

    NOT NULL means the column must always have a value; it cannot be left empty.

  2. Step 2: Compare with other constraints

    UNIQUE prevents duplicates but allows NULLs; CHECK enforces custom rules; PRIMARY KEY implies NOT NULL and UNIQUE but is more specific.

  3. Final Answer:

    NOT NULL -> Option B

  4. Quick Check:

    NOT NULL = no empty values [OK]
Hint: NOT NULL means no empty values allowed in the column [OK]
Common Mistakes:
  • Confusing UNIQUE with NOT NULL
  • Thinking CHECK prevents NULLs
  • Assuming PRIMARY KEY is the only way to disallow NULL
2. Which of the following is the correct syntax to add a UNIQUE constraint to the column email in a table users?
easy
A. ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email);
B. ALTER TABLE users ADD UNIQUE email;
C. ALTER TABLE users ADD UNIQUE (email) CONSTRAINT unique_email;
D. ALTER TABLE users ADD CONSTRAINT email UNIQUE;

Solution

  1. Step 1: Recall the syntax for adding UNIQUE constraint

    The correct syntax is: ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (column_name);

  2. Step 2: Match the syntax with options

    ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email); matches the correct syntax exactly, naming the constraint and specifying the column.

  3. Final Answer:

    ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email); -> Option A

  4. Quick Check:

    ADD CONSTRAINT name UNIQUE (column) [OK]
Hint: Use ADD CONSTRAINT name UNIQUE (column) to add UNIQUE [OK]
Common Mistakes:
  • Omitting CONSTRAINT keyword
  • Placing UNIQUE before CONSTRAINT name incorrectly
  • Not specifying column in parentheses
3. Given the table definition:
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  price NUMERIC CHECK (price > 0),
  sku TEXT UNIQUE
);

What happens if you try to insert price = -5 and sku = 'A123'?
medium
A. Insert fails due to UNIQUE constraint violation on sku
B. Insert succeeds because CHECK allows negative values
C. Insert fails due to CHECK constraint violation on price
D. Insert succeeds because price can be negative

Solution

  1. Step 1: Analyze the CHECK constraint on price

    The CHECK constraint requires price > 0, so negative values like -5 violate this rule.

  2. Step 2: Consider the UNIQUE constraint on sku

    Since 'A123' is new, UNIQUE is not violated here.

  3. Final Answer:

    Insert fails due to CHECK constraint violation on price -> Option C

  4. Quick Check:

    CHECK (price > 0) rejects negative price [OK]
Hint: CHECK enforces rules; negative price breaks price > 0 [OK]
Common Mistakes:
  • Ignoring CHECK constraint effects
  • Confusing UNIQUE violation with CHECK violation
  • Assuming negative numbers are allowed
4. You have this table:
CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  email TEXT UNIQUE NOT NULL,
  age INT CHECK (age >= 18)
);

Which statement will cause an error when inserting data?
medium
A. INSERT INTO employees (email, age) VALUES ('john@example.com', 25);
B. INSERT INTO employees (email, age) VALUES ('jane@example.com', 20);
C. INSERT INTO employees (email, age) VALUES ('jane@example.com', 18);
D. INSERT INTO employees (email, age) VALUES (NULL, 30);

Solution

  1. Step 1: Check NOT NULL constraint on email

    Email cannot be NULL because of NOT NULL constraint.

  2. Step 2: Check CHECK constraint on age

    Age must be 18 or older; the ages in A(25), B(20), and D(18) all satisfy this.

  3. Final Answer:

    INSERT with NULL email causes error due to NOT NULL constraint -> Option D

  4. Quick Check:

    NOT NULL rejects NULL email [OK]
Hint: NOT NULL disallows NULL values, CHECK enforces conditions [OK]
Common Mistakes:
  • Focusing on CHECK constraint instead of NOT NULL
  • Ignoring NOT NULL on email
  • Assuming UNIQUE causes error on first insert
5. You want to create a table orders where the quantity column must be a positive integer and the order_code must be unique and never empty. Which is the best column definition for quantity and order_code?
hard
A. quantity INT CHECK (quantity > 0), order_code TEXT UNIQUE NOT NULL
B. quantity INT NOT NULL, order_code TEXT CHECK (order_code != '')
C. quantity INT CHECK (quantity >= 0), order_code TEXT UNIQUE
D. quantity INT, order_code TEXT NOT NULL UNIQUE CHECK (order_code IS NOT NULL)

Solution

  1. Step 1: Ensure quantity is positive integer

    CHECK (quantity > 0) enforces positive values only, better than >= 0 which allows zero.

  2. Step 2: Ensure order_code is unique and not empty

    UNIQUE prevents duplicates; NOT NULL disallows empty; this combination ensures order_code is unique and present.

  3. Final Answer:

    quantity INT CHECK (quantity > 0), order_code TEXT UNIQUE NOT NULL -> Option A

  4. Quick Check:

    CHECK positive + UNIQUE NOT NULL = correct constraints [OK]
Hint: Use CHECK for positive, UNIQUE + NOT NULL for unique non-empty [OK]
Common Mistakes:
  • Allowing zero quantity
  • Missing NOT NULL on unique column
  • Using CHECK without NOT NULL for order_code
Recommended

Ready to prove it?

Pass the test and this pattern moves unknown strong while earning +1 Grip toward your certificate.

10 questions~5 mineasy hard