SQL ET POSTGRE 3.14 : data definition language/h1>

Here is the solution to our challenge. From the Servers panel, right-click on Databases and choose Create Database. We name it conducteur_voiture and save. Inside that database we open the query editor (Create Script). We then tackle the four exercises one by one. We start by creating the driver table: CREATE TABLE conducteur with parentheses and a trailing semicolon. The first column is id SERIAL PRIMARY KEY, followed by nom VARCHAR(30), prenom VARCHAR(30), departement INT and date_permis DATE. We run the query, then verify with SELECT * FROM conducteur.

Creating the car table

Next we create the car table: CREATE TABLE voiture with an id SERIAL PRIMARY KEY, marque VARCHAR(30), modele VARCHAR(30), annee INTEGER and a foreign key conducteur_id INT REFERENCES conducteur(id). We run it, it succeeds, and a SELECT * FROM voiture confirms the five columns are in place. 
ALTER TABLE conducteur ADD COLUMN email VARCHAR(50) UNIQUE;
ALTER TABLE conducteur ALTER COLUMN nom TYPE VARCHAR(50);
  • Add an email VARCHAR(50) column to conducteur with a UNIQUE constraint.
  • Change nom from VARCHAR(30) to VARCHAR(50).
  • Verify each step with a SELECT *.
We then add the email column using ALTER TABLE conducteur ADD COLUMN email VARCHAR(50) UNIQUE (the UNIQUE constraint makes sense for emails), and we confirm with a SELECT *. Finally we change the type of nom: ALTER TABLE conducteur ALTER COLUMN nom TYPE VARCHAR(50). The query returns successfully, and the final SELECT confirms nom is now VARCHAR(50). Well done — the full code is provided alongside this video.

Summary

This lesson demonstrates Data Definition Language (DDL) operations in PostgreSQL, including creating new tables with primary and foreign keys, adding columns to existing tables with constraints, and modifying column data types. The practical examples walk through creating "conducteur" (driver) and "voiture" (car) tables with appropriate relationships and validating each operation through SELECT queries.

Key points

  • CREATE TABLE statement defines new tables with columns, data types, and constraints including PRIMARY KEY and FOREIGN KEY
  • PRIMARY KEY constraint uniquely identifies each row in a table, while FOREIGN KEY establishes relationships between tables
  • ALTER TABLE ADD COLUMN adds new columns to existing tables and allows constraints like UNIQUE to be applied
  • ALTER TABLE ALTER COLUMN modifies the data type of existing columns (e.g., changing VARCHAR 30 to VARCHAR 50)
  • Common PostgreSQL data types: serial (auto-incrementing), VARCHAR (variable-length text), INT (integer), DATE (date values)
  • Verify table structure after creation with SELECT * FROM table_name to confirm columns and constraints were applied correctly

FAQ

What is the purpose of a PRIMARY KEY in a table?

A PRIMARY KEY uniquely identifies each row in a table and ensures no duplicate or NULL values exist in that column. In the lesson, the id column (SERIAL type) serves as the PRIMARY KEY for both conducteur and voiture tables.

How do you add a new column to an existing table in PostgreSQL?

Use the ALTER TABLE table_name ADD COLUMN column_name data_type [constraints] statement. In the lesson, the email column is added to the conducteur table with VARCHAR(50) and UNIQUE constraint.

How do FOREIGN KEY constraints work in PostgreSQL?

A FOREIGN KEY in one table references a PRIMARY KEY in another table, establishing relationships between them. In the lesson, the voiture table's conductor_id column references the conducteur table's id column, linking cars to their drivers.