Define database schemas with CREATE TABLE, choosing data types and enforcing PRIMARY KEY, FOREIGN KEY, NOT NULL and UNIQUE constraints

What this dot point is asking

SEAB wants you to define a database's structure with CREATE TABLE, choose appropriate data types for columns, and apply constraints - PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE - that the database enforces automatically. The core idea is that the schema encodes the rules of the data once, so invalid data is rejected at the source rather than checked by every program.

The answer

CREATE TABLE

Data Definition Language (DDL) defines structure. CREATE TABLE names a table and lists its columns, each with a name and a data type:

CREATE TABLE Book (
  BookID   INTEGER,
  Title    VARCHAR(120),
  Year     INTEGER,
  Price    DECIMAL(6, 2)
);

Choosing data types

The type fixes what a column may hold and how it is stored:

• INTEGER for whole numbers (IDs, counts).
• DECIMAL(p, s) for exact fractional values such as money.
• VARCHAR(n) for variable-length text up to $n$ characters; CHAR(n) for fixed length.
• DATE / DATETIME for dates and times.
• BOOLEAN for true/false flags.

A correct type gives validation, efficient storage and the right operations - dates can be compared and sorted, numbers can be summed.

Constraints that enforce rules

Constraints are rules the database guarantees:

• PRIMARY KEY - unique and not null; identifies each row.
• FOREIGN KEY ... REFERENCES - the value must match a primary key in another table (referential integrity).
• NOT NULL - the column must always have a value.
• UNIQUE - no two rows may share the value (without it being the primary key).

CREATE TABLE Student (
  StudentID INTEGER PRIMARY KEY,
  Name      VARCHAR(60) NOT NULL,
  Email     VARCHAR(120) UNIQUE,
  ClassID   INTEGER,
  FOREIGN KEY (ClassID) REFERENCES Class(ClassID)
);

Why enforce rules in the schema

Putting rules in the schema means every application using the database obeys them automatically. A NOT NULL name cannot be bypassed by a buggy program; a UNIQUE email is guaranteed even under concurrent inserts. The database becomes the single guardian of integrity.

Examples in context

Example 1. Preventing bad sign-ups. A registration system declares Email VARCHAR(120) UNIQUE NOT NULL. Even if two people submit the same email at the same instant, the database rejects the second, so duplicate accounts cannot exist - a guarantee the web form alone could not make.

Example 2. Money columns. An invoicing schema uses Amount DECIMAL(10, 2) rather than a floating-point type, so totals are exact to the cent. The choice of type is a design decision that directly prevents the rounding errors that plague binary floating point for currency.

Try this

Q1. Which constraint guarantees a column has no duplicate values but is not the primary key? [1 mark]

• Cue. UNIQUE.

Q2. Why store a price as DECIMAL rather than as a floating-point type? [2 marks]

• Cue. DECIMAL stores the value exactly, avoiding the rounding errors of binary floating point that would corrupt money totals.

Q3. State the two guarantees a PRIMARY KEY constraint provides. [2 marks]

• Cue. The value is unique across rows and is never null.