NOT NULL and DEFAULT constraints in MySQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a column that cannot have NULL values.

MySQL

CREATE TABLE users (id INT, name VARCHAR(100) [1]);

Drag options to blanks, or click blank then click option'

APRIMARY KEY

BNOT NULL

CAUTO_INCREMENT

DDEFAULT 'anonymous'

Attempts:

3 left

💡 Hint

Common Mistakes

Using DEFAULT instead of NOT NULL

Forgetting to add any constraint

The NOT NULL constraint ensures the column cannot have NULL values.

2fill in blank

medium

Complete the code to set a default value for the column 'status'.

MySQL

CREATE TABLE orders (order_id INT, status VARCHAR(20) [1]);

Drag options to blanks, or click blank then click option'

ANOT NULL

BPRIMARY KEY

CDEFAULT 'pending'

DUNIQUE

Attempts:

3 left

💡 Hint

Common Mistakes

Using NOT NULL instead of DEFAULT

Forgetting to quote string default values

The DEFAULT constraint sets a default value when no value is provided.

3fill in blank

hard

Fix the error in the column definition to correctly set NOT NULL and DEFAULT constraints.

MySQL

CREATE TABLE products (id INT, price DECIMAL(5,2) [1] DEFAULT 0.00);

Drag options to blanks, or click blank then click option'

ANULL

BAUTO_INCREMENT

CPRIMARY KEY

DNOT NULL

Attempts:

3 left

💡 Hint

Common Mistakes

Using NULL instead of NOT NULL

Omitting NOT NULL when DEFAULT is present

The NOT NULL constraint must be explicitly stated to prevent NULLs even if a DEFAULT is set.

4fill in blank

hard

Fill both blanks to create a column 'age' that cannot be NULL and has a default value of 18.

MySQL

CREATE TABLE members (id INT, age INT [1] [2] 18);

Drag options to blanks, or click blank then click option'

ANOT NULL

BDEFAULT

CAUTO_INCREMENT

DUNIQUE

Attempts:

3 left

💡 Hint

Common Mistakes

Reversing the order of NOT NULL and DEFAULT

Using AUTO_INCREMENT instead of DEFAULT

Use NOT NULL to prevent NULLs and DEFAULT 18 to set the default age.

5fill in blank

hard

Fill all three blanks to create a table with a 'username' column that is NOT NULL, UNIQUE, and has a default value 'guest'.

MySQL

CREATE TABLE accounts (id INT PRIMARY KEY, username VARCHAR(50) [1] [2] [3] 'guest');

Drag options to blanks, or click blank then click option'

ANOT NULL

BDEFAULT

CUNIQUE

DAUTO_INCREMENT

Attempts:

3 left

💡 Hint

Common Mistakes

Mixing up UNIQUE and NOT NULL

Placing DEFAULT before NOT NULL

Using AUTO_INCREMENT incorrectly

The username column must be NOT NULL, UNIQUE, and have a DEFAULT 'guest' value.

Practice

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

easy

A. It automatically fills the column with a default value.

B. It deletes rows where the column is empty.

C. It allows the column to accept any type of data.

D. It ensures the column cannot have empty or missing values.

Solution

1. Step 1: Understand NOT NULL constraint

   The NOT NULL constraint means a column must always have a value; it cannot be left empty or null.

2. Step 2: Compare with other options

   Only It ensures the column cannot have empty or missing values. correctly describes this behavior. It automatically fills the column with a default value. describes DEFAULT, C is unrelated, and D is incorrect.

3. Final Answer:

   It ensures the column cannot have empty or missing values. -> Option D

4. Quick Check:

   NOT NULL means no empty values allowed [OK]

Hint: NOT NULL means value required, no blanks allowed [OK]

Common Mistakes:

• Confusing NOT NULL with DEFAULT
• Thinking NOT NULL deletes rows
• Assuming NOT NULL allows empty strings

2. Which of the following is the correct syntax to add a NOT NULL and DEFAULT constraint to a column named age of type INT in MySQL?

easy

A. ALTER TABLE users MODIFY age INT DEFAULT 18 NULL;

B. ALTER TABLE users MODIFY age INT NOT NULL DEFAULT 18;

C. ALTER TABLE users CHANGE age INT DEFAULT 18 NOT NULL;

D. ALTER TABLE users ADD age INT DEFAULT NOT NULL 18;

Solution

1. Step 1: Review correct ALTER TABLE syntax

   To modify a column with NOT NULL and DEFAULT, the syntax is: MODIFY column_name data_type NOT NULL DEFAULT value.

2. Step 2: Check each option

   ALTER TABLE users MODIFY age INT NOT NULL DEFAULT 18; matches this syntax exactly. Options B and C have wrong order or keywords. ALTER TABLE users MODIFY age INT DEFAULT 18 NULL; allows NULL, which is incorrect.

3. Final Answer:

   ALTER TABLE users MODIFY age INT NOT NULL DEFAULT 18; -> Option B

4. Quick Check:

   Correct syntax uses MODIFY with NOT NULL then DEFAULT [OK]

Hint: Use MODIFY column TYPE NOT NULL DEFAULT value [OK]

Common Mistakes:

• Placing DEFAULT before NOT NULL
• Using ADD instead of MODIFY for existing columns
• Allowing NULL when NOT NULL is required

3. Given the table employees with column status VARCHAR(10) NOT NULL DEFAULT 'active', what will be the value of status after this insert?

INSERT INTO employees (name) VALUES ('John');

medium

A. Empty string ''

B. NULL

C. 'active'

D. Error due to missing status

Solution

1. Step 1: Understand DEFAULT and NOT NULL behavior

   The column status cannot be NULL and has a default value 'active'. If no value is given, MySQL uses the default.

2. Step 2: Analyze the INSERT statement

   The insert only provides name, so status uses its default 'active'. No error occurs.

3. Final Answer:

   'active' -> Option C

4. Quick Check:

   Missing value uses DEFAULT when NOT NULL [OK]

Hint: Missing NOT NULL column uses DEFAULT value automatically [OK]

Common Mistakes:

• Expecting NULL instead of default
• Thinking insert will fail without explicit value
• Assuming empty string is default

4. You have this table creation statement:

CREATE TABLE products (id INT NOT NULL, price DECIMAL(5,2) DEFAULT 0.00);

What is the problem with this table definition?

medium

A. The id column is NOT NULL but has no DEFAULT, causing insert errors.

B. The id column allows NULL values.

C. The price column cannot have a default value.

D. There is no PRIMARY KEY defined, so the table is invalid.

Solution

1. Step 1: Check NOT NULL columns without DEFAULT

   The id column is NOT NULL but has no default value. If an insert omits id, it will cause an error.

2. Step 2: Analyze other options

   The price column cannot have a default value. is wrong because price can have a default. The id column allows NULL values. is wrong because id is NOT NULL. There is no PRIMARY KEY defined, so the table is invalid. is not mandatory for table creation.

3. Final Answer:

   The id column is NOT NULL but has no DEFAULT, causing insert errors. -> Option A

4. Quick Check:

   NOT NULL without DEFAULT requires explicit value on insert [OK]

Hint: NOT NULL columns need value or DEFAULT to avoid insert errors [OK]

Common Mistakes:

• Assuming NOT NULL columns get default NULL
• Thinking PRIMARY KEY is required for table creation
• Believing DEFAULT is mandatory for NOT NULL

5. You want to create a table orders where the status column must never be empty and should default to 'pending'. Which of these statements correctly creates this table?

hard

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

B. CREATE TABLE orders (id INT NOT NULL, status VARCHAR(20) DEFAULT 'pending');

C. CREATE TABLE orders (id INT NOT NULL, status VARCHAR(20) NOT NULL);

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

Solution

1. Step 1: Identify constraints needed

   Status must never be empty (NOT NULL) and default to 'pending' if no value is given.

2. Step 2: Check each CREATE TABLE option

   CREATE TABLE orders (id INT NOT NULL, status VARCHAR(20) NOT NULL DEFAULT 'pending'); correctly uses NOT NULL and DEFAULT together. CREATE TABLE orders (id INT NOT NULL, status VARCHAR(20) DEFAULT 'pending'); misses NOT NULL, allowing NULLs. CREATE TABLE orders (id INT NOT NULL, status VARCHAR(20) NOT NULL); misses DEFAULT, so no automatic value. CREATE TABLE orders (id INT NOT NULL, status VARCHAR(20) DEFAULT NOT NULL 'pending'); has wrong syntax.

3. Final Answer:

   CREATE TABLE orders (id INT NOT NULL, status VARCHAR(20) NOT NULL DEFAULT 'pending'); -> Option A

4. Quick Check:

   NOT NULL with DEFAULT sets required and default value [OK]

Hint: Use NOT NULL before DEFAULT for required columns [OK]

Common Mistakes:

• Omitting NOT NULL allows NULL values
• Placing DEFAULT before NOT NULL
• Using invalid syntax with DEFAULT NOT NULL