SQL Schema Definitions | Databases

Translating entity tables to SQL

An entity table from the E/R model becomes a CREATE TABLE statement. The primary key is declared with PRIMARY KEY.

Simple entity with a synthetic key

CREATE TABLE genres (
  genre_id INTEGER NOT NULL,
  genre     TEXT    NOT NULL,
  PRIMARY KEY (genre_id)
);

INTEGER / TEXT are SQL types. The course uses varchar(n) for variable-length strings up to $n$ characters.
NOT NULL ensures the column cannot be NULL.
PRIMARY KEY (genre_id) declares genre_id as the primary key. This implies both UNIQUE and NOT NULL.

Entity with multiple attributes

CREATE TABLE movies (
  movie_id VARCHAR(16)  NOT NULL,
  title    VARCHAR(255) NOT NULL,
  year     INTEGER      NOT NULL,
  PRIMARY KEY (movie_id)
);

CREATE TABLE people (
  person_id VARCHAR(16)  NOT NULL,
  name      VARCHAR(255) NOT NULL,
  birthYear INTEGER,
  PRIMARY KEY (person_id)
);

Translating relationship tables to SQL

Many-to-many (all-key) relationship

CREATE TABLE directed (
  movie_id  VARCHAR(16) NOT NULL REFERENCES movies (movie_id),
  person_id VARCHAR(16) NOT NULL REFERENCES people (person_id),
  PRIMARY KEY (movie_id, person_id)
);

REFERENCES movies (movie_id) declares a foreign key constraint: every movie_id in directed must exist in movies.movie_id.
PRIMARY KEY (movie_id, person_id) declares a composite key spanning both columns.
Both columns are foreign keys. Together they form the primary key. This is the canonical all-key pattern.

Relationship with a foreign key to a single-column key

CREATE TABLE has_genre (
  movie_id VARCHAR(16) NOT NULL REFERENCES movies (movie_id),
  genre_id INTEGER     NOT NULL REFERENCES genres (genre_id),
  PRIMARY KEY (movie_id, genre_id)
);

Short form: REFERENCES movies (movie_id) is equivalent to FOREIGN KEY (movie_id) REFERENCES movies (movie_id). The short form can only be used when the foreign key is a single column, and only as a column constraint. The long form is needed for multi-column foreign keys:

CREATE TABLE some_table (
  col_a INTEGER NOT NULL,
  col_b INTEGER NOT NULL,
  FOREIGN KEY (col_a, col_b) REFERENCES other_table (col_c, col_d)
);

Combined example: a small schema

A schema for a film database with movies, people, genres, directors, and genre assignments:

CREATE TABLE movies (
  movie_id VARCHAR(16)  NOT NULL,
  title    VARCHAR(255) NOT NULL,
  year     INTEGER      NOT NULL,
  PRIMARY KEY (movie_id)
);

CREATE TABLE people (
  person_id VARCHAR(16)  NOT NULL,
  name      VARCHAR(255) NOT NULL,
  birthYear INTEGER,
  PRIMARY KEY (person_id)
);

CREATE TABLE genres (
  genre_id INTEGER NOT NULL,
  genre    TEXT    NOT NULL,
  PRIMARY KEY (genre_id)
);

CREATE TABLE directed (
  movie_id  VARCHAR(16) NOT NULL REFERENCES movies  (movie_id),
  person_id VARCHAR(16) NOT NULL REFERENCES people  (person_id),
  PRIMARY KEY (movie_id, person_id)
);

CREATE TABLE has_genre (
  movie_id VARCHAR(16) NOT NULL REFERENCES movies  (movie_id),
  genre_id INTEGER     NOT NULL REFERENCES genres  (genre_id),
  PRIMARY KEY (movie_id, genre_id)
);

Naming conventions

Foreign key column names typically match the primary key column names they reference. This convention makes schemas self-documenting:

directed.movie_id references movies.movie_id
has_genre.genre_id references genres.genre_id

When a table has two foreign keys to the same target table, distinguish them by role:

CREATE TABLE transfers (
  id            INTEGER     NOT NULL PRIMARY KEY,
  from_account  VARCHAR(16) NOT NULL REFERENCES accounts (account_id),
  to_account    VARCHAR(16) NOT NULL REFERENCES accounts (account_id),
  amount        DECIMAL     NOT NULL
);

Here from_account and to_account both reference accounts(account_id) but their column names reflect their distinct roles.

Enforcing constraints

The DBMS enforces the declared constraints at statement boundaries:

Attempted operation	Constraint violated	Result
`INSERT INTO directed VALUES ('m99', 'p01')`	FK: `m99` not in `movies`	Rejected
`INSERT INTO directed VALUES ('m01', 'p01')`	PK: duplicate row	Rejected
`DELETE FROM movies WHERE movie_id = 'm01'`	FK: `m01` referenced in `directed`	Rejected (if `RESTRICT`) or cascaded (if `CASCADE`)
`INSERT INTO movies VALUES (NULL, 'Film', 2024)`	PK: `NOT NULL` on `movie_id`	Rejected

Overline notation

In some schema diagrams, foreign keys are indicated visually with overlines. For example, if table $W$ has attributes $A, B, Z, C$ , and $Z$ is a foreign key to table $R$ , we might write $W = \overline{A}.B.\overline{Z}.C$ , where the overline on $Z$ marks it as a foreign key and the overline on $A$ marks $A$ as part of the primary key.

Composite foreign keys

A foreign key can span multiple columns if the referenced table has a composite key:

CREATE TABLE enrolment (
  student_id  INTEGER NOT NULL,
  course_id   INTEGER NOT NULL,
  year        INTEGER NOT NULL,
  PRIMARY KEY (student_id, course_id),
  FOREIGN KEY (course_id, year) REFERENCES course_offerings (course_id, year)
);

Here (course_id, year) references the composite key of course_offerings.

Summary

CREATE TABLE defines a relation with columns, types, and constraints.
PRIMARY KEY declares the key; $X$ is a minimal superkey.
REFERENCES (or FOREIGN KEY ... REFERENCES) declares a foreign key constraint.
NOT NULL prevents NULL values.
The DBMS enforces all constraints, rejecting invalid operations.
Naming conventions make foreign key relationships obvious.