NOT NULL constraint in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Effect of NOT NULL constraint on INSERT

Given the table Users(id INT PRIMARY KEY, name VARCHAR(50) NOT NULL, age INT), what happens when you run this query?

INSERT INTO Users (id, age) VALUES (1, 25);

SQL

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

A The query fails with a NOT NULL constraint violation error.

B The row is inserted with name set to NULL.

C The row is inserted with name set to an empty string.

D The query succeeds but issues a warning about missing name.

💡 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. Omitting the 'name' column in the INSERT causes the database to try to insert NULL, which violates the constraint and causes an error.

🧠 Conceptual

intermediate

Purpose of NOT NULL constraint

Why do database designers use the NOT NULL constraint on columns?

A To ensure that the column always contains a value and never NULL.

B To allow the column to store multiple values in one field.

C To automatically generate a unique value for the column.

D To speed up queries by indexing the column.

💡 Hint

Think about data integrity and missing information.

NOT NULL ensures that every row has a meaningful value in that column, preventing missing or unknown data.

📝 Syntax

advanced

Adding NOT NULL constraint to existing column

Which SQL statement correctly adds a NOT NULL constraint to the existing column email in the Customers table?

A ALTER TABLE Customers SET email NOT NULL;

B ALTER TABLE Customers ADD NOT NULL (email);

C ALTER TABLE Customers MODIFY email VARCHAR(100) NOT NULL;

D ALTER TABLE Customers CHANGE email email VARCHAR(100) NOT NULL;

💡 Hint

Different SQL dialects use different syntax; consider standard SQL or MySQL style.

Option C uses the correct syntax to modify the column definition to add NOT NULL. Option C is MySQL-specific but changes the column name redundantly. Options A and B are invalid syntax.

❓ query_result

advanced

Query result with NOT NULL and DEFAULT

Consider this table:
CREATE TABLE Products(id INT PRIMARY KEY, name VARCHAR(50) NOT NULL, price DECIMAL(5,2) NOT NULL DEFAULT 9.99);
What is the result of this query?

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

A Row inserted with id=1, name='Pen', price=NULL.

B Query fails because price is NOT NULL but not provided.

C Row inserted with id=1, name='Pen', price=0.00.

D Row inserted with id=1, name='Pen', price=9.99.

💡 Hint

Think about how DEFAULT values work with NOT NULL columns.

Since price has a DEFAULT value and is NOT NULL, omitting it in INSERT uses the default 9.99, so the row inserts successfully with that price.

🔧 Debug

expert

Diagnosing NOT NULL constraint violation in multi-step insert

You have this table:
CREATE TABLE Orders(order_id INT PRIMARY KEY, customer_id INT NOT NULL, order_date DATE NOT NULL);
You run these queries:

INSERT INTO Orders (order_id, customer_id) VALUES (101, 5);

What error will you get and why?

A Error: Duplicate primary key because order_id 101 already exists.

B Error: NOT NULL constraint failed on 'order_date' because it was not provided.

C No error, row inserted with order_date set to current date automatically.

D Error: Foreign key constraint failed on customer_id.

💡 Hint

Check which NOT NULL columns are missing values in the INSERT.

The 'order_date' column is NOT NULL and no value or default was provided, so the insert fails with a NOT NULL constraint violation.