PRIMARY KEY and SERIAL behavior in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a table with an auto-incrementing primary key.

PostgreSQL

CREATE TABLE users (id [1] PRIMARY KEY, name VARCHAR(100));

Drag options to blanks, or click blank then click option'

AINTEGER

BTEXT

CSERIAL

DBOOLEAN

Attempts:

3 left

💡 Hint

Common Mistakes

Using INTEGER without auto-increment will not auto-generate values.

Using TEXT or BOOLEAN types for primary keys is not appropriate.

The SERIAL keyword creates an auto-incrementing integer column, perfect for primary keys.

2fill in blank

medium

Complete the code to insert a new user without specifying the primary key value.

PostgreSQL

INSERT INTO users (name) VALUES ([1]);

Drag options to blanks, or click blank then click option'

A1

BNULL

CDEFAULT

D'Alice'

Attempts:

3 left

💡 Hint

Common Mistakes

Trying to insert a value for the SERIAL primary key manually.

Using DEFAULT or NULL as the name value.

When the primary key is SERIAL, you don't specify it in the insert; just provide the other columns.

3fill in blank

hard

Fix the error in the code to create a table with a primary key that auto-increments.

PostgreSQL

CREATE TABLE orders (order_id [1] PRIMARY KEY, order_date DATE);

Drag options to blanks, or click blank then click option'

ASERIAL

BTEXT

CBOOLEAN

DVARCHAR(20)

Attempts:

3 left

💡 Hint

Common Mistakes

Using TEXT or VARCHAR for auto-incrementing keys causes errors.

Using BOOLEAN for a primary key is invalid.

Using SERIAL for order_id ensures it auto-increments and can be a primary key.

4fill in blank

hard

Fill both blanks to create a table with a primary key that auto-increments and a unique username.

PostgreSQL

CREATE TABLE accounts (id [1] PRIMARY KEY, username [2] UNIQUE);

Drag options to blanks, or click blank then click option'

ASERIAL

BVARCHAR(50)

CTEXT

DINTEGER

Attempts:

3 left

💡 Hint

Common Mistakes

Using TEXT for username without length limit.

Using INTEGER for username which is not suitable.

The id uses SERIAL for auto-incrementing primary key. The username uses VARCHAR with UNIQUE constraint.

5fill in blank

hard

Fill all three blanks to insert a new account with a username, letting the id auto-increment.

PostgreSQL

INSERT INTO accounts (id, username) VALUES ([1], [2]); -- Use [3] for id to auto-increment

Drag options to blanks, or click blank then click option'

ANULL

B'bob123'

CDEFAULT

D0

Attempts:

3 left

💡 Hint

Common Mistakes

Using NULL or 0 for id which may cause errors.

Not quoting the username string.

Use DEFAULT for id to let it auto-increment. Provide the username as a string.