Table, row, column, primary key

A relational database organizes data into tables, a bit like spreadsheet sheets, but with much stricter rules.

Basic vocabulary

• a table groups together all the information of the same type, for example a table eleves;
• a column defines a specific type of information for each record, for example nom, prenom, or note;
• a row (also called a record) represents a single entry, for example one particular student with all their information.

table: eleves

id | nom      | prenom  | note
---+----------+---------+------
1  | Martin   | Lucie   | 15.5
2  | Dubois   | Karim   | 9.0
3  | Leroy    | Emma    | 12.0

Here, eleves is the table, id, nom, prenom, and note are the columns, and each row corresponds to a student.

The primary key

The primary key is the column (or set of columns) that uniquely identifies each row of the table. In the example above, this is the id column: even if two students are both named Martin, their identifiers will always be different. This is what prevents duplicates and makes it possible to refer to a student unambiguously, even if their name changes or is repeated.

CREATE TABLE eleves (
    id INTEGER PRIMARY KEY,
    nom VARCHAR(50) NOT NULL,
    prenom VARCHAR(50) NOT NULL,
    note DECIMAL(4,2)
);

This statement creates the eleves table with four columns. PRIMARY KEY indicates that id is the primary key: the DBMS will automatically refuse to insert the same value twice (a guarantee that a simple text file could not offer).