NOT NULL and DEFAULT constraints in MySQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the NOT NULL constraint do in a MySQL table?

The NOT NULL constraint ensures that a column cannot have a NULL value. It means every row must have a value for that column.

beginner

What is the purpose of the DEFAULT constraint in MySQL?

The DEFAULT constraint sets a default value for a column when no value is provided during data insertion.

intermediate

Can a column have both NOT NULL and DEFAULT constraints? What happens if you insert a row without specifying that column's value?

Yes, a column can have both. If you insert a row without specifying the column's value, MySQL uses the DEFAULT value instead of NULL.

beginner

Write a simple MySQL column definition using NOT NULL and DEFAULT constraints for a column named status with default value 'active'.

status VARCHAR(20) NOT NULL DEFAULT 'active'

intermediate

What happens if you try to insert a NULL value into a NOT NULL column without a DEFAULT value?

MySQL will return an error and reject the insert because the column cannot accept NULL and no default value is provided.

What does the NOT NULL constraint prevent?

A Inserting NULL values into a column

B Inserting duplicate values

C Inserting default values

D Deleting rows

NOT NULL means the column must always have a value; NULLs are not allowed.

If a column has a DEFAULT value and you insert a row without specifying that column, what happens?

A The insert fails

B The column is left empty

C The column is set to NULL

D The column gets the default value

The DEFAULT value is used automatically when no value is provided.

Which statement is true about a column defined as INT NOT NULL DEFAULT 0?

A It can store NULL values

B It must always have a value, defaulting to 0 if none is given

C It cannot have a default value

D It automatically increments

The column cannot be NULL and uses 0 as default if no value is inserted.

What error occurs if you insert NULL into a NOT NULL column without a default?

A Syntax error

B Duplicate entry error

C Constraint violation error

D No error

MySQL rejects NULL values for NOT NULL columns causing a constraint violation.

Which constraint helps ensure a column always has a meaningful value?

A NOT NULL

B UNIQUE

C DEFAULT

D CHECK

NOT NULL forces a value to be present, preventing empty or NULL entries.

Explain in your own words what the NOT NULL and DEFAULT constraints do in a database table.

Think about what happens when you insert data without specifying a column's value.

Describe a real-life example where using NOT NULL and DEFAULT constraints would be helpful.

Imagine a signup form where some fields must never be empty.