Column constraints (NOT NULL, UNIQUE, CHECK) in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of INSERT with NOT NULL constraint violation

Given the table users with a NOT NULL constraint on the email column, what happens when you run this query?

INSERT INTO users (id, email) VALUES (1, NULL);

PostgreSQL

CREATE TABLE users (id SERIAL PRIMARY KEY, email VARCHAR(255) NOT NULL);

A The row is inserted with a NULL email value.

B The query fails with a NOT NULL constraint violation error.

C The query succeeds but sets email to an empty string.

D The query ignores the NULL and inserts a default email.

💡 Hint

NOT NULL means the column cannot have NULL values.

The NOT NULL constraint prevents inserting NULL values into the column. The database raises an error if you try.

❓ query_result

intermediate

Effect of UNIQUE constraint on duplicate inserts

Consider a table products with a UNIQUE constraint on the sku column. What happens if you run these two queries?

INSERT INTO products (id, sku) VALUES (1, 'ABC123');
INSERT INTO products (id, sku) VALUES (2, 'ABC123');

PostgreSQL

CREATE TABLE products (id SERIAL PRIMARY KEY, sku VARCHAR(50) UNIQUE);

A The second insert fails with a UNIQUE constraint violation error.

B The second insert overwrites the first row.

C The second insert ignores the duplicate sku and inserts anyway.

D Both rows are inserted successfully with the same sku.

💡 Hint

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

The UNIQUE constraint prevents duplicate values in the sku column. The second insert violates this and causes an error.

📝 Syntax

advanced

Correct CHECK constraint syntax for age column

Which option correctly adds a CHECK constraint to ensure the age column is between 18 and 65 inclusive in PostgreSQL?

PostgreSQL

ALTER TABLE employees ADD CONSTRAINT age_check CHECK (age >= 18 AND age <= 65);

A ALTER TABLE employees ADD CONSTRAINT age_check CHECK (age >= 18 AND age <= 65);

B ALTER TABLE employees ADD CHECK age BETWEEN 18 AND 65;

C ALTER TABLE employees ADD CONSTRAINT age_check CHECK age >= 18 AND age <= 65;

D ALTER TABLE employees ADD CONSTRAINT age_check CHECK (age > 18 OR age < 65);

💡 Hint

CHECK constraints require a condition inside parentheses.

Option A uses correct syntax with CONSTRAINT name and a condition in parentheses using AND for the range.

🔧 Debug

advanced

Identify the error in this table definition with constraints

What is wrong with this table creation statement?

CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
customer_id INT NOT NULL UNIQUE,
order_date DATE CHECK order_date > CURRENT_DATE
);

A The SERIAL type cannot be used with PRIMARY KEY.

B The UNIQUE constraint cannot be combined with NOT NULL.

C The PRIMARY KEY must be declared after all columns.

D The CHECK constraint syntax is missing parentheses around the condition.

💡 Hint

CHECK constraints require parentheses around the condition.

The CHECK constraint must have the condition inside parentheses. Missing parentheses cause a syntax error.

🧠 Conceptual

expert

Behavior of multiple constraints on a single column

If a column username has both NOT NULL and UNIQUE constraints, which of the following statements is true?

A The column can have multiple NULL values but no duplicates otherwise.

B The column can have NULL values but only one unique non-NULL value.

C The column cannot have NULL values and all values must be unique.

D The UNIQUE constraint overrides NOT NULL, allowing NULLs.

💡 Hint

NOT NULL means no NULLs allowed; UNIQUE means no duplicates allowed.

NOT NULL disallows NULLs, so the column must have unique, non-NULL values.

Practice

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 ('[e-mail]', 25);

B. INSERT INTO employees (email, age) VALUES ('[e-mail]', 20);

C. INSERT INTO employees (email, age) VALUES ('[e-mail]', 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