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.