INSERT with DEFAULT values in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the SQL statement INSERT INTO table_name DEFAULT VALUES; do?

It inserts a new row into table_name using all the default values defined for the columns. No specific values are provided.

intermediate

Can you use INSERT INTO table_name DEFAULT VALUES; if the table has columns without default values and no NULL allowed?

No. If a column does not have a default value and does not allow NULL, the insert will fail because the database doesn't know what value to put.

beginner

How do you insert a row with default values for some columns and specific values for others?

You specify the columns and values you want, and omit the others. The omitted columns will use their default values if defined.

beginner

Why might using INSERT with DEFAULT VALUES be useful in real life?

It is useful when you want to add a new row but rely on the database to fill in standard values like timestamps, auto-increment IDs, or default status flags.

intermediate

What happens if you try INSERT INTO table_name DEFAULT VALUES; on a table with no default values at all?

The insert will fail if any column requires a value and has no default or allows NULL. Otherwise, it inserts a row with NULLs where allowed.

What does INSERT INTO users DEFAULT VALUES; do?

A Updates existing rows with default values

B Deletes all rows from the users table

C Inserts a new row with all columns set to their default values

D Returns an error always

This statement inserts a new row using the default values defined for each column.

If a column has no default and does not allow NULL, what happens when you use INSERT DEFAULT VALUES?

A The database assigns a default value automatically

B The insert succeeds with NULL in that column

C The column gets a random value

D The insert fails with an error

The insert fails because the database requires a value but none is provided.

How can you insert a row with some columns using default values and others with specific values?

A Use <code>INSERT INTO table (col1, col2) VALUES (val1, DEFAULT);</code>

B Use <code>INSERT INTO table DEFAULT VALUES;</code> and then update

C You cannot mix default and specific values in one insert

D Use <code>UPDATE</code> instead of <code>INSERT</code>

You can specify DEFAULT for columns in the VALUES list to use their default values.

Which of these is a valid reason to use INSERT with DEFAULT VALUES?

A To delete all rows in a table

B To quickly add a row with system-generated values like timestamps

C To update existing rows with default values

D To select rows with default values

It allows inserting rows that rely on default column values such as auto-generated timestamps.

What is required for INSERT DEFAULT VALUES to work without error?

A All columns must have default values or allow NULL

B At least one column must have a default value

C The table must be empty

D The table must have a primary key

If any column requires a value and has no default or NULL allowed, the insert will fail.

Explain how INSERT with DEFAULT VALUES works and when you would use it.

Think about adding a new record without specifying any data.

What happens if you try to insert default values into a table with columns that have no defaults and do not allow NULL?

Consider what the database needs to store a valid row.