Column definitions and constraints in MySQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a column definition in a database table?

A column definition specifies the name, data type, and optional settings for a column in a table. It tells the database what kind of data the column will hold.

beginner

What does the NOT NULL constraint do?

The NOT NULL constraint ensures that a column cannot have empty (NULL) values. Every row must have a value in that column.

intermediate

Explain the PRIMARY KEY constraint.

PRIMARY KEY uniquely identifies each row in a table. It means the column(s) must have unique values and cannot be NULL.

intermediate

What is the purpose of the UNIQUE constraint?

The UNIQUE constraint makes sure all values in a column are different from each other, but unlike PRIMARY KEY, it can allow NULL values.

beginner

How does the DEFAULT value work in column definitions?

DEFAULT sets a value automatically for a column when no value is provided during data insertion.

Which constraint prevents a column from having empty values?

A NOT NULL

B UNIQUE

C PRIMARY KEY

D DEFAULT

NOT NULL ensures that a column must always have a value and cannot be left empty.

What does the PRIMARY KEY constraint guarantee?

A Values are always NULL

B Values can be duplicated

C Values are unique and not NULL

D Values have a default

PRIMARY KEY ensures each row is uniquely identified with no NULL values.

Which constraint allows multiple NULL values but no duplicate non-NULL values?

A PRIMARY KEY

B UNIQUE

C NOT NULL

D DEFAULT

UNIQUE allows NULLs but ensures all non-NULL values are unique.

What happens if you insert a row without specifying a value for a column with a DEFAULT constraint?

A The column gets the default value

B The insertion fails

C The column is set to NULL

D The column value is random

DEFAULT provides a preset value when no value is given during insertion.

Which data type would you use to store a person's age in a column definition?

A VARCHAR

B TEXT

C DATE

D INT

INT is used for whole numbers like age.

Describe the main types of constraints you can apply to columns in a MySQL table and their purposes.

Think about how constraints control data validity and uniqueness.

Explain how you would define a column for storing email addresses that must be unique and never empty.

Combine data type and constraints to enforce rules.