PRIMARY KEY and SERIAL behavior in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a PRIMARY KEY in a PostgreSQL table?

A PRIMARY KEY is a column or set of columns that uniquely identifies each row in a table. It ensures no duplicate or NULL values exist in those columns.

beginner

What does the SERIAL keyword do in PostgreSQL?

SERIAL creates an auto-incrementing integer column. It automatically generates a unique number for each new row inserted.

beginner

Can a SERIAL column be used as a PRIMARY KEY?

Yes, a SERIAL column is often used as a PRIMARY KEY because it provides unique, automatically generated values for each row.

beginner

What happens if you insert a row without specifying a value for a SERIAL column?

PostgreSQL automatically assigns the next number in the sequence to the SERIAL column for that row.

intermediate

How does PostgreSQL ensure uniqueness for a PRIMARY KEY?

PostgreSQL creates a unique index on the PRIMARY KEY column(s) to prevent duplicate values and enforce uniqueness.

What is the main purpose of a PRIMARY KEY in a table?

A To store large text data

B To allow NULL values

C To speed up SELECT queries only

D To uniquely identify each row

A PRIMARY KEY uniquely identifies each row and does not allow NULL values.

What type of data does SERIAL create in PostgreSQL?

A Fixed-length string

B Floating point number

C Auto-incrementing integer

D Boolean value

SERIAL creates an auto-incrementing integer column.

If you insert a row without specifying a SERIAL column value, what happens?

A An error occurs

B PostgreSQL assigns the next sequence number automatically

C The column is set to NULL

D The row is ignored

PostgreSQL automatically assigns the next number in the sequence.

Can a PRIMARY KEY column contain NULL values?

A No, PRIMARY KEY columns cannot be NULL

B Only if it is a SERIAL column

C Yes, always

D Only if the table is empty

PRIMARY KEY columns must have unique, non-NULL values.

What does PostgreSQL create automatically when you define a PRIMARY KEY?

A A unique index on the key column(s)

B A foreign key constraint

C A trigger for inserts

D A default value

PostgreSQL creates a unique index to enforce uniqueness for the PRIMARY KEY.

Explain what a PRIMARY KEY is and why it is important in a database table.

Think about how you find a specific item in a list without confusion.

Describe how the SERIAL keyword works in PostgreSQL and how it relates to PRIMARY KEY columns.

Imagine numbering pages in a book automatically.