AUTO_INCREMENT vs SERIAL vs IDENTITY in SQL - Quick Revision & Key Differences

Recall & Review

beginner

What does AUTO_INCREMENT do in SQL?

AUTO_INCREMENT automatically generates a unique number for a column when a new row is inserted, usually used for primary keys.

beginner

What is SERIAL in SQL?

SERIAL is a shorthand in PostgreSQL that creates an integer column and automatically sets up a sequence to generate unique numbers for new rows.

beginner

What is IDENTITY in SQL?

IDENTITY is a SQL standard feature that defines a column to auto-generate unique numbers, often used in SQL Server and newer versions of other databases.

intermediate

How does AUTO_INCREMENT differ from SERIAL?

AUTO_INCREMENT is used mainly in MySQL and automatically increments a number, while SERIAL in PostgreSQL creates a sequence object behind the scenes to generate numbers.

intermediate

Why might you choose IDENTITY over AUTO_INCREMENT or SERIAL?

IDENTITY follows the SQL standard and offers more control over number generation, making it more portable across different database systems.

Which SQL feature automatically creates a sequence object in PostgreSQL?

A AUTO_INCREMENT

B SERIAL

C IDENTITY

D PRIMARY KEY

SERIAL in PostgreSQL creates a sequence object to generate unique numbers automatically.

In which database is AUTO_INCREMENT commonly used?

A MySQL

B SQL Server

C PostgreSQL

D Oracle

AUTO_INCREMENT is commonly used in MySQL to auto-generate unique numbers.

What does the IDENTITY property do in SQL?

A Creates a foreign key

B Creates a view

C Generates unique numbers automatically

D Defines a text column

IDENTITY defines a column to auto-generate unique numbers, often for primary keys.

Which of these is a SQL standard feature for auto-numbering?

A SEQUENCE

B SERIAL

C AUTO_INCREMENT

D IDENTITY

IDENTITY is part of the SQL standard for auto-generating unique numbers.

Which feature is NOT typically used for auto-incrementing primary keys?

A VARCHAR

B SERIAL

C IDENTITY

D AUTO_INCREMENT

VARCHAR is a text data type and does not auto-increment numbers.

Explain the differences between AUTO_INCREMENT, SERIAL, and IDENTITY in SQL.

Think about which database uses which feature and how they generate numbers.

Why is IDENTITY considered more portable than AUTO_INCREMENT or SERIAL?

Consider standards and cross-database compatibility.