Column constraints (NOT NULL, UNIQUE, CHECK) in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the NOT NULL constraint do in a database column?

The NOT NULL constraint ensures that a column cannot have a NULL value. Every row must have a value for this column.

beginner

Explain the purpose of the UNIQUE constraint in a table column.

The UNIQUE constraint makes sure that all values in a column are different from each other. No two rows can have the same value in that column.

intermediate

What is a CHECK constraint and how is it used?

A CHECK constraint limits the values that can be stored in a column by specifying a condition. Only values that meet the condition are allowed.

intermediate

Can a column have both NOT NULL and UNIQUE constraints? What does that mean?

Yes, a column can have both. It means every row must have a value (not null) and all values must be different (unique).

beginner

Write a simple SQL example adding a CHECK constraint to ensure age is 18 or older.

Example: <br>CREATE TABLE users (id SERIAL PRIMARY KEY, age INT CHECK (age >= 18));<br>This means age must be 18 or more.

What happens if you try to insert a NULL value into a column with a NOT NULL constraint?

A The insert fails with an error.

B The NULL value is accepted.

C The NULL is converted to zero.

D The row is ignored.

NOT NULL means the column must have a value. Inserting NULL causes an error.

Which constraint ensures all values in a column are different?

A PRIMARY KEY

B CHECK

C NOT NULL

D UNIQUE

UNIQUE constraint makes sure no two rows have the same value in that column.

What does this SQL do? CHECK (salary > 0)

A Allows salary to be zero or negative.

B Allows only positive salary values.

C Makes salary optional.

D No effect on salary.

CHECK ensures salary must be greater than zero.

Can a UNIQUE column contain NULL values in PostgreSQL?

A Yes, but only one NULL is allowed.

B No, NULLs are not allowed.

C Yes, multiple NULLs are allowed.

D UNIQUE constraint ignores NULLs.

In PostgreSQL, UNIQUE allows multiple NULLs because NULL is not equal to NULL.

Which constraint would you use to prevent empty values in a column?

A NOT NULL

B FOREIGN KEY

C CHECK

D UNIQUE

NOT NULL prevents empty (NULL) values in a column.

Describe the differences and purposes of NOT NULL, UNIQUE, and CHECK constraints in a database column.

Think about what each constraint controls about the data.

Write an example SQL statement to create a table with columns using NOT NULL, UNIQUE, and CHECK constraints.

Use simple column names and conditions like age >= 18.