Column definitions and constraints in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this CREATE TABLE statement?

Consider the following MySQL table creation code:

CREATE TABLE Employees (
  ID INT NOT NULL AUTO_INCREMENT,
  Name VARCHAR(50) NOT NULL,
  Age INT CHECK (Age >= 18),
  PRIMARY KEY (ID)
);

What will happen when this code runs in MySQL 8.0?

MySQL

CREATE TABLE Employees (
  ID INT NOT NULL AUTO_INCREMENT,
  Name VARCHAR(50) NOT NULL,
  Age INT CHECK (Age >= 18),
  PRIMARY KEY (ID)
);

A Table is created successfully with a check constraint on Age.

B Syntax error because CHECK constraints are not supported in MySQL.

C Table is created but the CHECK constraint is ignored.

D Error because AUTO_INCREMENT must be on a PRIMARY KEY column.

💡 Hint

MySQL supports CHECK syntax but does not enforce it before version 8.0.16.

In MySQL versions before 8.0.16, CHECK constraints are parsed but ignored. The table will be created, but the Age check will not be enforced.

🧠 Conceptual

intermediate

Which column definition enforces uniqueness and non-null values?

You want to create a column in MySQL that must always have a unique value and cannot be NULL. Which of the following column definitions achieves this?

A email VARCHAR(100) UNIQUE NOT NULL

B email VARCHAR(100) NOT NULL

C email VARCHAR(100) UNIQUE

D email VARCHAR(100) PRIMARY KEY

💡 Hint

Think about what constraints ensure uniqueness and disallow NULLs.

UNIQUE ensures values are unique, NOT NULL disallows NULLs. PRIMARY KEY also enforces both but is usually used for main identifiers.

📝 Syntax

advanced

Identify the syntax error in this column definition

Which option contains a syntax error in the column definition for a MySQL table?

MySQL

CREATE TABLE Products (
  ProductID INT PRIMARY KEY,
  Price DECIMAL(10, 2) NOT NULL CHECK Price > 0,
  Name VARCHAR(100) NOT NULL
);

A Price DECIMAL(10, 2) NOT NULL CHECK (Price > 0)

B Price DECIMAL(10, 2) NOT NULL CHECK Price > 0

C Price DECIMAL(10, 2) NOT NULL CHECK (Price > 0) COMMENT 'Price must be positive'

D Price DECIMAL(10, 2) NOT NULL

💡 Hint

CHECK constraints require parentheses around the condition.

In MySQL, the CHECK constraint condition must be enclosed in parentheses. Option B misses parentheses causing a syntax error.

🔧 Debug

advanced

Why does this table creation fail?

Given this MySQL table creation code:

CREATE TABLE Orders (
  OrderID INT AUTO_INCREMENT,
  CustomerID INT NOT NULL,
  PRIMARY KEY (CustomerID)
);

Why does this code cause an error?

A PRIMARY KEY cannot be on CustomerID because it is NOT NULL.

B CustomerID cannot be PRIMARY KEY because it is not AUTO_INCREMENT.

C AUTO_INCREMENT cannot be used without UNIQUE constraint.

D AUTO_INCREMENT column must be part of the PRIMARY KEY.

💡 Hint

AUTO_INCREMENT columns must be indexed as PRIMARY KEY or UNIQUE.

In MySQL, an AUTO_INCREMENT column must be defined as a PRIMARY KEY or have a UNIQUE index. Here, OrderID is AUTO_INCREMENT but not a key, causing an error.

🧠 Conceptual

expert

Which constraint combination ensures a column stores only positive integers and is unique?

You want a column in MySQL that stores only positive integers (greater than zero) and each value must be unique. Which combination of column definition and constraints achieves this?

A col INT UNSIGNED UNIQUE CHECK (col > 0)

B col INT CHECK (col > 0) UNIQUE

C col INT PRIMARY KEY CHECK (col > 0)

D col INT UNSIGNED PRIMARY KEY

💡 Hint

UNSIGNED means zero or positive; CHECK enforces greater than zero; UNIQUE enforces uniqueness.

INT UNSIGNED allows zero and positive numbers; CHECK (col > 0) restricts to strictly positive; UNIQUE enforces no duplicates. PRIMARY KEY also enforces uniqueness and NOT NULL but does not guarantee positive values without CHECK.

Practice

1. Which constraint ensures that a column cannot have empty (NULL) values in MySQL?

easy

A. NOT NULL

B. UNIQUE

C. PRIMARY KEY

D. DEFAULT

Solution

1. Step 1: Understand the purpose of NOT NULL

   The NOT NULL constraint prevents a column from having NULL values, meaning it must always have data.

2. Step 2: Compare with other constraints

   UNIQUE allows NULLs but no duplicates, PRIMARY KEY implies NOT NULL and uniqueness, and DEFAULT sets a default value but does not prevent NULL.

3. Final Answer:

   NOT NULL -> Option A

4. Quick Check:

   Prevent empty values = NOT NULL [OK]

Hint: NOT NULL means no empty values allowed [OK]

Common Mistakes:

• Confusing UNIQUE with NOT NULL
• Thinking DEFAULT prevents NULL
• Assuming PRIMARY KEY is only for uniqueness

2. Which of the following is the correct way to define a column named id as an auto-incrementing primary key in MySQL?

easy

A. id AUTO_INCREMENT INT PRIMARY KEY

B. id INT AUTO_INCREMENT PRIMARY

C. id INT PRIMARY KEY AUTO_INCREMENT

D. id PRIMARY KEY INT AUTO_INCREMENT

Solution

1. Step 1: Recall correct column definition order

   In MySQL, the correct syntax places the data type first, then constraints like PRIMARY KEY and AUTO_INCREMENT.

2. Step 2: Validate each option's syntax

   id INT PRIMARY KEY AUTO_INCREMENT follows the correct order: id INT PRIMARY KEY AUTO_INCREMENT. Others have incorrect order or missing keywords.

3. Final Answer:

   id INT PRIMARY KEY AUTO_INCREMENT -> Option C

4. Quick Check:

   Data type first, then constraints [OK]

Hint: Data type first, then PRIMARY KEY and AUTO_INCREMENT [OK]

Common Mistakes:

• Placing AUTO_INCREMENT before data type
• Using PRIMARY instead of PRIMARY KEY
• Mixing order of keywords

3. Given the table definition:

CREATE TABLE users (
  user_id INT PRIMARY KEY,
  email VARCHAR(100) UNIQUE NOT NULL,
  age INT DEFAULT 18
);

What will happen if you try to insert:

INSERT INTO users (user_id, email) VALUES (1, NULL);

medium

A. Insert fails due to NOT NULL constraint on email

B. Insert succeeds with email set to NULL

C. Insert succeeds with email set to default value

D. Insert fails due to UNIQUE constraint violation

Solution

1. Step 1: Check the email column constraints

   Email is defined as UNIQUE NOT NULL, so it cannot be NULL.

2. Step 2: Analyze the insert statement

   The insert tries to set email to NULL, violating the NOT NULL constraint, causing an error.

3. Final Answer:

   Insert fails due to NOT NULL constraint on email -> Option A

4. Quick Check:

   NOT NULL rejects NULL values [OK]

Hint: NOT NULL columns reject NULL inserts [OK]

Common Mistakes:

• Assuming DEFAULT applies when value is NULL
• Confusing UNIQUE with NOT NULL
• Thinking NULL is allowed if UNIQUE

4. Consider this table creation statement:

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  name VARCHAR(50) UNIQUE,
  price DECIMAL(5,2) NOT NULL DEFAULT 0.00
);

Which of the following statements will cause an error?

medium

A. INSERT INTO products (product_id, name, price) VALUES (1, 'Pen', 1.50);

B. INSERT INTO products (product_id, name) VALUES (2, 'Book');

C. INSERT INTO products (product_id, price) VALUES (3, 2.00);

D. INSERT INTO products (product_id, name, price) VALUES (1, 'Pencil', 0.50);

Solution

1. Step 1: Check constraints on each insert

   INSERT INTO products (product_id, name, price) VALUES (1, 'Pen', 1.50); inserts all required fields correctly. INSERT INTO products (product_id, name) VALUES (2, 'Book'); omits price but uses default 0.00, allowed. INSERT INTO products (product_id, price) VALUES (3, 2.00); omits name, which is nullable, allowed.

2. Step 2: Identify duplicate primary key error

   INSERT INTO products (product_id, name, price) VALUES (1, 'Pencil', 0.50); tries to insert product_id 1 again, violating the PRIMARY KEY uniqueness constraint, causing an error.

3. Final Answer:

   INSERT INTO products (product_id, name, price) VALUES (1, 'Pencil', 0.50); -> Option D

4. Quick Check:

   PRIMARY KEY must be unique [OK]

Hint: Duplicate PRIMARY KEY values cause errors [OK]

Common Mistakes:

• Assuming UNIQUE allows duplicates
• Forgetting DEFAULT applies only if column omitted
• Ignoring PRIMARY KEY uniqueness

5. You want to create a table orders where order_id is a primary key, customer_email must be unique and not null, and order_date defaults to the current date if not provided. Which of the following column definitions is correct?

hard

A. order_id INT PRIMARY KEY, customer_email VARCHAR(100) NOT NULL UNIQUE, order_date DATE DEFAULT NOW()

B. order_id INT PRIMARY KEY, customer_email VARCHAR(100) UNIQUE NOT NULL, order_date DATE DEFAULT CURRENT_DATE

C. order_id INT PRIMARY KEY, customer_email VARCHAR(100) UNIQUE, order_date DATE DEFAULT CURRENT_TIMESTAMP

D. order_id INT PRIMARY KEY, customer_email VARCHAR(100) NOT NULL UNIQUE, order_date DATE DEFAULT CURDATE()

Solution

1. Step 1: Check constraints on customer_email

   The column must be both UNIQUE and NOT NULL. Using either UNIQUE NOT NULL or NOT NULL UNIQUE is valid in MySQL.

2. Step 2: Verify default for order_date

   MySQL supports DEFAULT CURRENT_DATE for DATE columns. NOW() and CURRENT_TIMESTAMP return datetime, not date, causing errors. CURDATE() is a function and cannot be used directly as default.

3. Step 3: Confirm full correctness

   order_id INT PRIMARY KEY, customer_email VARCHAR(100) UNIQUE NOT NULL, order_date DATE DEFAULT CURRENT_DATE uses correct syntax for all constraints and default value.

4. Final Answer:

   order_id INT PRIMARY KEY, customer_email VARCHAR(100) UNIQUE NOT NULL, order_date DATE DEFAULT CURRENT_DATE -> Option B

5. Quick Check:

   Use CURRENT_DATE for DATE default [OK]

Hint: Use CURRENT_DATE for DATE default, UNIQUE NOT NULL order matters [OK]

Common Mistakes:

• Using NOW() or CURRENT_TIMESTAMP for DATE default
• Trying to use functions like CURDATE() as default
• Confusing UNIQUE and NOT NULL order