Building tables

Building a table is where the design becomes real. Each column gets a data type, and the table gets its keys and constraints: rules the database itself enforces, so bad data is refused instead of quietly stored. In SQL this is the CREATE TABLE statement.

Here are the tables of a school clubs database. The “one” side comes first, because a foreign key can only point at a table that already exists.

CREATE TABLE Teacher (TeacherID INT PRIMARY KEY, Name VARCHAR(30) NOT NULL);

CREATE TABLE Club (ClubID INT PRIMARY KEY, Title VARCHAR(30) NOT NULL, TeacherID INT NOT NULL, FOREIGN KEY (TeacherID) REFERENCES Teacher(TeacherID));

CREATE TABLE Member (ClubID INT NOT NULL, StudentID INT NOT NULL, Joined DATE NOT NULL, PRIMARY KEY (ClubID, StudentID), FOREIGN KEY (ClubID) REFERENCES Club(ClubID) ON DELETE CASCADE);

• PRIMARY KEY makes the column unique and never empty. For a composite key, list the columns: PRIMARY KEY (ClubID, StudentID).
• FOREIGN KEY (col) REFERENCES Table(col) says every value must match a primary key in that table. This is the line that creates the relationship.
• NOT NULL forbids an empty value; UNIQUE forbids repeats in a column that is not the primary key.
• ON DELETE CASCADE says that deleting a parent row also deletes the rows that point at it.

The sandbox below has these three tables with a little data in them. Run the examples, and see how the database defends itself: duplicate keys, a club for a teacher who does not exist, a wrong type, and deleting a teacher who still has clubs. Then try the cascade: delete the Robotics club and look at the Member table.