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.