NOT NULL and DEFAULT constraints in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Effect of NOT NULL constraint on INSERT

Given the table users defined as:

CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(50) NOT NULL, age INT DEFAULT 18);

What happens when you run this query?

INSERT INTO users (id) VALUES (1);

MySQL

CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(50) NOT NULL, age INT DEFAULT 18);
INSERT INTO users (id) VALUES (1);

A The query fails with an error because the NOT NULL column 'name' is missing a value.

B The query succeeds, inserting id=1, name=NULL, and age=18.

C The query succeeds, inserting id=1, name='', and age=18.

D The query fails with an error because the DEFAULT value for 'age' is missing.

💡 Hint

Think about what NOT NULL means for a column when no value is provided.

The NOT NULL constraint means the column must have a value. Since 'name' is NOT NULL and no value is given, the INSERT fails. DEFAULT applies only when a value is not provided for columns that allow NULL or have a default.

❓ query_result

intermediate

Default value usage on INSERT

Consider the table products defined as:

CREATE TABLE products (id INT PRIMARY KEY, name VARCHAR(50) NOT NULL, stock INT DEFAULT 0);

What will be the result of this query?

INSERT INTO products (id, name) VALUES (10, 'Pen');
SELECT * FROM products WHERE id = 10;

MySQL

CREATE TABLE products (id INT PRIMARY KEY, name VARCHAR(50) NOT NULL, stock INT DEFAULT 0);
INSERT INTO products (id, name) VALUES (10, 'Pen');
SELECT * FROM products WHERE id = 10;

A The SELECT returns no rows because the INSERT failed.

B Row with id=10, name='Pen', stock=NULL is inserted and returned.

C Row with id=10, name='Pen', stock=0 is inserted and returned.

D The INSERT fails because 'stock' is NOT NULL and no value is provided.

💡 Hint

What happens when a column has a DEFAULT value and no value is given in INSERT?

Since 'stock' has a DEFAULT of 0 and is not NOT NULL, the INSERT uses the default value 0 for 'stock'. The row is inserted successfully.

📝 Syntax

advanced

Identify the syntax error in table definition

Which of the following CREATE TABLE statements has a syntax error related to NOT NULL and DEFAULT constraints?

A CREATE TABLE orders (id INT PRIMARY KEY, status VARCHAR(20) DEFAULT NOT NULL 'pending');

B CREATE TABLE orders (id INT PRIMARY KEY, status VARCHAR(20) NOT NULL DEFAULT 'pending' COMMENT 'Order status');

C CREATE TABLE orders (id INT PRIMARY KEY, status VARCHAR(20) DEFAULT 'pending' NOT NULL);

D CREATE TABLE orders (id INT PRIMARY KEY, status VARCHAR(20) NOT NULL DEFAULT 'pending');

💡 Hint

Check the order of NOT NULL and DEFAULT keywords in column definition.

The correct order is NOT NULL before DEFAULT. Option A places DEFAULT before NOT NULL, which is invalid syntax.

🧠 Conceptual

advanced

Behavior of NOT NULL with DEFAULT NULL

If a column is defined as col INT NOT NULL DEFAULT NULL, what will happen when you insert a row without specifying a value for col?

A The INSERT succeeds and inserts the default NULL value, which is treated as 0.

B The INSERT succeeds and inserts NULL into <code>col</code>.

C The INSERT succeeds and inserts 0 into <code>col</code>.

D The INSERT fails because NOT NULL conflicts with DEFAULT NULL.

💡 Hint

Think about the contradiction between NOT NULL and DEFAULT NULL.

NOT NULL means the column cannot be NULL. DEFAULT NULL tries to set NULL as default. This conflict causes an error on insert without a value.

❓ optimization

expert

Optimizing table design with NOT NULL and DEFAULT

You have a large table events with a column status defined as VARCHAR(10) DEFAULT 'active' but without NOT NULL. You want to ensure every row has a valid status and optimize storage. Which is the best approach?

A Keep the column as is and rely on application logic to prevent NULLs.

B Alter the column to <code>status VARCHAR(10) NOT NULL DEFAULT 'active'</code> to enforce NOT NULL and default value.

C Remove the DEFAULT and allow NULLs to save space.

D Change the column to <code>status VARCHAR(10) NOT NULL</code> without DEFAULT and always specify status on INSERT.

💡 Hint

Consider constraints that enforce data integrity and reduce NULL storage.

Adding NOT NULL with a DEFAULT ensures no NULLs and automatic default insertion, improving data integrity and storage efficiency.