Building a table: CREATE TABLE and data types

Before any data goes in, someone has to decide what each field will hold. FirstName holds text. Average holds a number that can have a decimal part. Enrolled holds a date.

That decision is the field's data type, and it is the DBMS's main defence against rubbish data. Put 'Amara' into an INTEGER field and the DBMS refuses the row. You cannot store half a date.

So a table definition is really a set of promises, and the DBMS enforces every one of them on every row, for ever.

A database is a container. A table lives inside one, so create the database first:

CREATE DATABASE School;

Then define the table inside it:

CREATE TABLE Student (
    StudentID   INTEGER       PRIMARY KEY,
    FirstName   VARCHAR(30),
    FeePaid     BOOLEAN,
    Average     REAL,
    Enrolled    DATE
);

Read it in this order:

1. CREATE TABLE Student — make a table called Student.
2. The brackets hold the field list, one field per line.
3. Each line is field name, then data type, then any extra rule such as PRIMARY KEY.
4. The semicolon ends the statement.

Spelling and punctuation matter: an extra comma before the last bracket is a syntax error, and a missing comma between two fields is too.

You need to recognise these and choose between them.

How to choose, in one line each:

• Text of unpredictable length (names, emails, addresses) → VARCHAR(n). Reserve enough characters, but not 500 "just in case".
• Text of fixed length (a two-letter country code, a product code like AB-1234) → CHARACTER. The syllabus lists it as CHARACTER; most DBMS let you write CHARACTER(2) or CHAR(2) to fix the length.
• A yes/no flag (fee paid? account active?) → BOOLEAN.
• A count or an identifier (number of seats, student ID) → INTEGER.
• A measurement that can have decimals (average mark, mass in kg) → REAL.
• A date or a time of day → DATE or TIME. Never store a date in a text field: you could not then compare dates or sort them properly.

PRIMARY KEY marks the field that uniquely identifies each row. StudentID is a good choice; FirstName is not, because two students can share a name.

The primary key comes with two guarantees, enforced by the DBMS:

• Unique — no two rows may have the same key value.
• Not null — every row must have one.

A table can have only one primary key, though that key can be made of more than one field. You write it on its own line when it is the last rule:

CREATE TABLE Booking (
    StudentID  INTEGER,
    ClassID    INTEGER,
    PRIMARY KEY (StudentID, ClassID)
);

That is a composite key: the pair must be unique, so one student cannot book the same class twice, but may book many classes.