Learn Databases: Lecture 28 – Mini-Project: Creating the Blog Tables.This lecture covers the SQL implementation of the blog database design.
In the last lecture, we designed the schema for our blog database. Now it’s time to bring that design to life by writing the SQL CREATE TABLE statements.
The order in which we create the tables is important. Since the `Posts` table refers to `Authors`, and the `Comments` table refers to `Posts`, we must create the parent tables before the child tables that depend on them. The correct order is: 1. `Authors`, 2. `Posts`, 3. `Comments`.
Step 1: Create the `Authors` Table
This is our first parent table. It has no foreign keys.
CREATE TABLE Authors (
AuthorID INTEGER PRIMARY KEY,
AuthorName TEXT NOT NULL,
Email TEXT NOT NULL UNIQUE
);We’ve added two new constraints here: NOT NULL and UNIQUE.
NOT NULL: This ensures that a column cannot have a NULL value. An author must have a name and an email.UNIQUE: This ensures that all values in the column are different from one another. We don’t want two authors to have the same email address.
Step 2: Create the `Posts` Table
This table has a foreign key that references the `Authors` table.
CREATE TABLE Posts (
PostID INTEGER PRIMARY KEY,
Title TEXT NOT NULL,
Content TEXT,
PublishDate TEXT,
Author_ID INTEGER,
FOREIGN KEY (Author_ID) REFERENCES Authors(AuthorID)
);Step 3: Create the `Comments` Table
Finally, this table has a foreign key that references the `Posts` table.
CREATE TABLE Comments (
CommentID INTEGER PRIMARY KEY,
CommenterName TEXT NOT NULL,
CommentContent TEXT,
CommentDate TEXT,
Post_ID INTEGER,
FOREIGN KEY (Post_ID) REFERENCES Posts(PostID)
);
Our Database is Ready
That’s it! If you run these three commands in order, you will have successfully created the structure for our blog database. All three tables exist and the relationships between them are formally defined with foreign key constraints.
In the Next Lecture…
Our tables are empty. In the next lecture, we will populate our blog database with some sample data. We will insert a couple of authors, a few posts written by them, and some comments on those posts. This will set us up to start writing interesting queries.
