A full example - the recenzje shelf

Time to combine everything you have learned: types and constraints in one complete statement. We will build the recenzje (reviews) shelf - here readers will leave ratings of books.

The whole shelf in one statement

1CREATE TABLE recenzje (
2  id INT AUTO_INCREMENT PRIMARY KEY,
3  ksiazka_id INT NOT NULL,
4  ocena INT,
5  tresc TEXT,
6  data_dodania DATE
7);

Breakdown line by line

• id INT AUTO_INCREMENT PRIMARY KEY

  - the primary key, numbered automatically. Each review gets its own catalog number.

• ksiazka_id INT NOT NULL

  - points to which book the review concerns. A required field - a review without a book does not exist.

• ocena INT

  - a numeric rating (e.g. from 1 to 5). A plain whole number.

• tresc TEXT

  - the content of the review, that is a long text without a strict limit.

• data_dodania DATE

  - when the review was added.

What to watch out for

• Each column is on a separate line - that reads most comfortably.
• After each definition there is a comma, except for the last one (

  data_dodania DATE

  ).
• The whole thing is closed by a parenthesis

  )

  and a semicolon ";".

This is a model shelf - remember its shape. We build most tables in the Library exactly like this: the primary key on top, then columns with appropriate types and constraints.