SQL and MySQL course Β· Module 4: Creating tables (DDL)
Column constraints - rules of order
In this lesson4
The data type alone is not enough. The Keeper of the Catalog imposes rules on shelves - so that the data stays consistent and trustworthy. We call these rules constraints, and we write them after the type of a column.
PRIMARY KEY - the primary key
It marks the column that uniquely identifies each row. The value must be unique and cannot be empty:
id INT PRIMARY KEYIt is like a scroll's catalog number - two rows never share the same one.
AUTO_INCREMENT - automatic numbering
Added to the primary key, it makes MySQL assign by itself the next increasing number to every new row (1, 2, 3...):
id INT AUTO_INCREMENT PRIMARY KEYYou do not have to remember the last number - the Library counts for you. This is the most common way to create an id column.
NOT NULL - a required field
It forces the column to never be empty:
nazwa VARCHAR(100) NOT NULLA category without a name makes no sense - so we require it. A column without NOT NULL may stay empty (NULL).
Order matters
In a column definition we always write: the name, the type, and then the constraints - one after another, separated by a space:
id INT AUTO_INCREMENT PRIMARY KEY,
nazwa VARCHAR(100) NOT NULLThis is a pattern you will repeat in almost every table.
Spotted a mistake in this lesson?
Check yourself
Answer the questions from this lesson. Pick an answer to see right away whether it is correct.
1. What does a PRIMARY KEY constraint placed on the id column guarantee?
2. What is AUTO_INCREMENT used for on the id column?
These are 2 of 3 questions for this lesson. Solve the rest in the game.
Hands-on tasks in the game
- Code editor
Write a CREATE TABLE statement that creates the kategorie table with columns: id of type INT with the AUTO_INCREMENT and PRIMARY KEY constraints, and nazwa of type VARCHAR(100) with the NOT NULL constraint.
- Horizontal ordering
Arrange the elements of a primary key definition on one line, from left to right:
- Code editor
Write a CREATE TABLE statement that creates the czytelnicy table with columns: id of type INT with AUTO_INCREMENT and PRIMARY KEY, imie of type VARCHAR(50) and nazwisko of type VARCHAR(50).