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.

Practice

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

easy

A. To store large text data efficiently

B. To allow duplicate values in a column

C. To automatically generate numbers for a column

D. To uniquely identify each row in the table

Solution

1. Step 1: Understand the role of PRIMARY KEY

   A PRIMARY KEY ensures that each row in a table is unique and can be identified distinctly.

2. Step 2: Compare options with PRIMARY KEY purpose

   Only To uniquely identify each row in the table describes uniqueness, which is the main function of PRIMARY KEY.

3. Final Answer:

   To uniquely identify each row in the table -> Option D

4. Quick Check:

   PRIMARY KEY = unique row ID [OK]

Hint: PRIMARY KEY means unique row identifier [OK]

Common Mistakes:

• Confusing PRIMARY KEY with auto-increment behavior
• Thinking PRIMARY KEY allows duplicates
• Mixing PRIMARY KEY with data storage types

2. Which of the following is the correct way to define a column as an auto-incrementing primary key using SERIAL in PostgreSQL?

easy

A. id INTEGER PRIMARY KEY SERIAL

B. id SERIAL PRIMARY KEY

C. id PRIMARY KEY SERIAL

D. SERIAL id PRIMARY KEY

Solution

1. Step 1: Recall correct SERIAL syntax

   In PostgreSQL, the correct syntax is to declare the column as id SERIAL PRIMARY KEY.

2. Step 2: Check each option's order and syntax

   Only id SERIAL PRIMARY KEY follows the correct order and syntax for SERIAL with PRIMARY KEY.

3. Final Answer:

   id SERIAL PRIMARY KEY -> Option B

4. Quick Check:

   Correct SERIAL syntax = id SERIAL PRIMARY KEY [OK]

Hint: Use 'id SERIAL PRIMARY KEY' order for auto-increment [OK]

Common Mistakes:

• Placing SERIAL after PRIMARY KEY
• Using INTEGER before SERIAL
• Writing SERIAL before column name

3. Given the table definition:

CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT);

What will be the result of inserting two rows without specifying id values?

INSERT INTO users (name) VALUES ('Alice');
INSERT INTO users (name) VALUES ('Bob');

medium

A. Two rows with ids 1 and 2 automatically assigned

B. Error because id must be provided

C. Both rows get id 0

D. Rows inserted but id is NULL

Solution

1. Step 1: Understand SERIAL behavior on insert

   SERIAL creates an auto-incrementing integer sequence for the id column, so values are assigned automatically if not provided.

2. Step 2: Predict inserted ids for two rows

   The first insert gets id=1, the second gets id=2 automatically.

3. Final Answer:

   Two rows with ids 1 and 2 automatically assigned -> Option A

4. Quick Check:

   SERIAL auto-increments ids = 1, 2 [OK]

Hint: SERIAL auto-assigns incrementing ids if omitted [OK]

Common Mistakes:

• Expecting to provide id manually
• Thinking id defaults to NULL
• Assuming id starts at 0

4. Consider this table creation:

CREATE TABLE products (
  product_id SERIAL,
  name TEXT,
  PRIMARY KEY (product_id)
);

After successfully inserting a row without specifying product_id (which auto-assigns 1), which of the following statements will cause an error when inserting data?

medium

A. INSERT INTO products (product_id, name) VALUES (1, 'Eraser');

B. INSERT INTO products (product_id, name) VALUES (2, 'Pencil');

C. INSERT INTO products (name) VALUES ('Pen');

D. INSERT INTO products (product_id, name) VALUES (2, 'Notebook');

Solution

1. Step 1: Understand PRIMARY KEY uniqueness

   The product_id is a PRIMARY KEY and must be unique.

2. Step 2: Analyze insert statements for duplicates

   After a prior auto-insert with product_id=1, INSERT INTO products (product_id, name) VALUES (1, 'Eraser'); tries to insert product_id=1 again, causing a duplicate key error.

3. Final Answer:

   INSERT INTO products (product_id, name) VALUES (1, 'Eraser'); -> Option A

4. Quick Check:

   Duplicate PRIMARY KEY value causes error [OK]

Hint: Duplicate PRIMARY KEY values cause insert errors [OK]

Common Mistakes:

• Assuming SERIAL prevents manual duplicate inserts
• Ignoring PRIMARY KEY uniqueness constraint
• Thinking SERIAL auto-updates duplicates

5. You have a table:

CREATE TABLE orders (
  order_id SERIAL PRIMARY KEY,
  customer TEXT
);

You manually insert a row with order_id = 100. What happens if you then insert a new row without specifying order_id? Choose the correct behavior.

hard

A. The new row gets order_id = 100, causing a duplicate key error

B. The new row gets order_id = 101, continuing from the highest manual id

C. The new row gets order_id = 1, restarting the sequence

D. The insert fails because manual ids break SERIAL sequence

Solution

1. Step 1: Understand SERIAL sequence behavior with manual inserts

   PostgreSQL sequences do not automatically adjust to manual inserts; explicit values do not advance the sequence.

2. Step 2: Predict next sequence value after manual insert

   Manual insert of 100 does not call nextval, so sequence next value remains 1; auto insert gets order_id=1.

3. Final Answer:

   The new row gets order_id = 1, restarting the sequence -> Option C

4. Quick Check:

   SERIAL sequence unaffected by manual inserts [OK]

Hint: Manual inserts do not advance SERIAL sequence [OK]

Common Mistakes:

• Assuming sequence syncs to MAX(id) and continues from 101
• Expecting duplicate key error on next insert
• Thinking manual insert breaks sequence permanently