CREATE TABLE: data types and constraints

In this lesson you will learn

Create a table with CREATE TABLE and change it with ALTER TABLE and DROP TABLE
Choose data types in different DBMSs
Protect data quality with constraints

So far we have worked with ready-made tables. But who builds them, and how? A good table is not just a list of columns — it is also a set of rules: a student without a surname, a negative price or two users with the same email should never get into the database at all. In this lesson we create our own tables for school clubs.

CREATE TABLE

CREATE TABLE name (...) lists each column's name, type and rules in brackets. Below we create the clubs table: id is the primary key, name can be neither empty nor repeated, and max_members is 20 when not given and must be positive.

SQL· Edit the code and run it again

CREATE TABLE clubs (
  id          INTEGER PRIMARY KEY,
  name        TEXT NOT NULL UNIQUE,
  room        TEXT,
  max_members INTEGER DEFAULT 20
              CONSTRAINT positive_max CHECK (max_members > 0)
);

INSERT INTO clubs (name, room) VALUES ('Chess club', '201'), ('Robotics', 'Lab 2');
INSERT INTO clubs (name, room, max_members) VALUES ('Debate', '105', 12);

SELECT * FROM clubs ORDER BY id;

▸ Expected output

id | name | room | max_members
1 | Chess club | 201 | 20
2 | Robotics | Lab 2 | 20
3 | Debate | 105 | 12

max_members was not given for the first two clubs, so the DEFAULT value of 20 was used.

Data types

Every column has a type: whole number, decimal, text, date. MySQL, PostgreSQL and SQL Server enforce types strictly: you can't put text into an integer column. The names of the types, however, differ from one DBMS to another.

Data	SQLite	MySQL	PostgreSQL	SQL Server
Whole number	INTEGER	INT	INTEGER	INT
Exact decimal (money)	none: whole cents as INTEGER	DECIMAL(10, 2)	NUMERIC(10, 2)	DECIMAL(10, 2)
Text	TEXT	VARCHAR(100)	VARCHAR(100), TEXT	NVARCHAR(100)
Date	TEXT ('2025-09-15')	DATE	DATE	DATE
True / false	INTEGER (0/1)	BOOLEAN	BOOLEAN	BIT
Auto number	INTEGER PRIMARY KEY	INT AUTO_INCREMENT	INTEGER GENERATED ALWAYS AS IDENTITY	INT IDENTITY(1, 1)

VARCHAR(100) is text of up to 100 characters; DECIMAL(10, 2) is a number with 10 digits, 2 of them after the decimal point.

In SQLite, however, the type is only a recommendation (type affinity). SQLite tries to convert the value to the column type: the text '15' becomes the number 15 in an integer column, but 'abc' can't be converted and stays text. The TYPEOF() function shows how a value is really stored.

SQL· Edit the code and run it again

CREATE TABLE t (n INTEGER, s TEXT);
INSERT INTO t VALUES ('abc', 42), ('15', 7);

SELECT n, TYPEOF(n) AS n_type,
       s, TYPEOF(s) AS s_type
FROM t;

▸ Expected output

n | n_type | s | s_type
abc | text | 42 | text
15 | integer | 7 | text

SQL· Edit the code and run it again

SELECT 0.1 + 0.2 AS result;

▸ Expected output

result
0.30000000000000004

Constraints

A constraint is a rule the DBMS checks on every INSERT and UPDATE. If the rule is broken, the statement is rejected and the table doesn't change. Catching mistakes in the database, not only in the application, is reliable: the rule works no matter which program writes the data.

PRIMARY KEY — the unique identifier of each row; it can't repeat.
NOT NULL — the column can't be empty (NULL).
UNIQUE — no two rows may have the same value (for example, an email).
DEFAULT — the value used when none is given.
CHECK (condition) — the condition must hold, e.g. price >= 0.
FOREIGN KEY / REFERENCES — the value must exist in another table.

SQL· Edit the code and run it again

CREATE TABLE clubs (
  id   INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE
);

INSERT INTO clubs (name) VALUES ('Chess club');
INSERT INTO clubs (name) VALUES ('Chess club');  -- same name again

▸ Expected output

Error: UNIQUE constraint failed: clubs.name

SQL· Edit the code and run it again

CREATE TABLE clubs (
  id          INTEGER PRIMARY KEY,
  name        TEXT NOT NULL UNIQUE,
  max_members INTEGER DEFAULT 20
              CONSTRAINT positive_max CHECK (max_members > 0)
);

INSERT INTO clubs (name, max_members) VALUES ('Drama', 0);

▸ Expected output

Error: CHECK constraint failed: positive_max

CONSTRAINT positive_max gives the rule a name, which makes the error message easy to read.

FOREIGN KEY: links between tables

The club_members table stores which student is in which club. REFERENCES clubs(id) says that club_id must point to an existing club. UNIQUE (club_id, student_id) stops a student from joining the same club twice. Note: for compatibility with old programs, SQLite does not check foreign keys by default — you must write PRAGMA foreign_keys = ON. MySQL (InnoDB), PostgreSQL and SQL Server check them by default.

SQL· Edit the code and run it again

PRAGMA foreign_keys = ON;

CREATE TABLE clubs (
  id   INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE
);
CREATE TABLE club_members (
  id         INTEGER PRIMARY KEY,
  club_id    INTEGER NOT NULL REFERENCES clubs(id),
  student_id INTEGER NOT NULL REFERENCES students(id),
  UNIQUE (club_id, student_id)
);

INSERT INTO clubs (name) VALUES ('Chess club'), ('Robotics');
INSERT INTO club_members (club_id, student_id) VALUES (1, 3), (1, 6), (2, 10);

SELECT c.name AS club, s.first_name
FROM club_members AS m
JOIN clubs    AS c ON c.id = m.club_id
JOIN students AS s ON s.id = m.student_id
ORDER BY c.name, s.first_name;

▸ Expected output

club | first_name
Chess club | Leyla
Chess club | Rəşad
Robotics | Kamran

SQL· Edit the code and run it again

PRAGMA foreign_keys = ON;

CREATE TABLE clubs (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE);
CREATE TABLE club_members (
  id         INTEGER PRIMARY KEY,
  club_id    INTEGER NOT NULL REFERENCES clubs(id),
  student_id INTEGER NOT NULL REFERENCES students(id)
);

INSERT INTO clubs (name) VALUES ('Chess club');
-- there is no club 5 and no student 99
INSERT INTO club_members (club_id, student_id) VALUES (5, 99);

▸ Expected output

Error: FOREIGN KEY constraint failed

If you delete the PRAGMA line, SQLite will silently accept this bad row.

Changing and dropping a table

ALTER TABLE ... ADD COLUMN adds a new column to an existing table; old rows get the DEFAULT value in it. Below we add a discount column to the products and give games a 15% discount.

SQL· Edit the code and run it again

ALTER TABLE products ADD COLUMN discount REAL DEFAULT 0;

UPDATE products SET discount = 0.15 WHERE category = 'Games';

SELECT name, price, discount,
       ROUND(price * (1 - discount), 2) AS final_price
FROM products
WHERE category IN ('Games', 'Home')
ORDER BY id;

▸ Expected output

name | price | discount | final_price
Desk lamp | 34.99 | 0 | 34.99
Chess set | 42 | 0.15 | 35.7

Exercise

Create a rooms table for classrooms: id is the primary key, name is text that can't be empty or repeated, and seats is an integer that defaults to 30 and must be greater than 0. Then insert Lab 1 with 24 seats and Hall A without giving the seats, and show name and seats sorted by id.

Exercise · SQL· Edit the code and run it again

CREATE TABLE rooms (
  id INTEGER PRIMARY KEY
  -- name and seats with their constraints
);

-- insert the two rooms, then SELECT

▸ Expected output

name | seats
Lab 1 | 24
Hall A | 30

Exercise

Add an integer column rating with a default of 0 to courses. Give the Informatics course a rating of 5. Then show the title and rating of the courses worth 3 credits, sorted by id.

Exercise · SQL· Edit the code and run it again

-- 1) ALTER TABLE ...
-- 2) UPDATE ...
-- 3) SELECT ...

▸ Expected output

title | rating
Geometry | 0
Organic Chemistry | 0
Python Basics | 5

Key points

CREATE TABLE defines the names, types and rules of the columns.
Type names differ between DBMSs; SQLite is flexible about types, the others are strict.
PRIMARY KEY, NOT NULL, UNIQUE, CHECK and DEFAULT stop bad data in the database itself.
REFERENCES forbids pointing to a row that doesn't exist; SQLite needs PRAGMA foreign_keys = ON.
ALTER TABLE changes a table; DROP TABLE deletes it for good.

Check yourself

10 questions. Every correct answer earns XP.

1 / 10

Which constraint forbids a column from being empty?