SQL and MySQL course Β· Module 4: Creating tables (DDL)

Column constraints - rules of order

2 min read Β·
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 KEY

It 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 KEY

You 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 NULL

A 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 NULL

This 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. 1. What does a PRIMARY KEY constraint placed on the id column guarantee?

  2. 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).

  1. QuestionHow PRIMARY KEY works
  2. QuestionThe role of AUTO_INCREMENT
  3. QuestionMeaning of NOT NULL
  4. Coding taskPrimary key with auto-numbering
  5. Put in orderDefining the id primary key
  6. Coding taskA czytelnicy table with a primary key

Useful articles