Create Tables

Intro

[0:00 Video timestamp]

This is the third in a series of tutorials on how to use a relational database.
We are using Postgres, and the PG Admin graphical interface.
We have a database named bookstore with two tables in it, authors and books.
In this tutorial we will discuss tables.

Read it, watch it, do it, review it:

There is both a written and accompanying video version of this tutorial. There are timestamps under the headings that align with the video version of the topic.

Read it: For each topic heading, First read the topic.
Watch it: Then watch the associated section of the video.
Do it: Then follow the instructions to replicate the steps in your own version of the bookstore database.

The Database CheatSheet has a major category that aligns with this tutorial.

Review it: When you are done with the whole tutorial, open the CheatSheet and review the Tables category from this tutorial. Make sure you understand everything, and can refer back to the CheatSheet when you are working on your own projects in the future.

Delete Tables

[0:18 Video timestamp]

We already added two tables to our database, authors and books. Let's delete them. Then we'll recreate them with default values and constraints.

Use any one of the three ways to delete the table that are listed below:

1. Using the PG Admin Query Tool

In the Browser side panel, click on the bookstore database. Then click Tools menu > Query Tool.
Enter the SQL statements in the Query Editor window:

DROP TABLE authors; 
    DROP TABLE books;

Click the Execute toolbar ▶

2. Using the PG Admin GUI

In the PG Admin Browser side panel, drill down to the table you want to drop:

Servers > PostgreSQL > Databases > bookstore > Schemas > public > Tables > table name.
Then right click on the table name > Delete/Drop

3. Using the PSQL Shell

In the PG Admin Browser side panel, click on the bookstore database. Then click Tools menu > PSQL Tool.

List all the tables in the database:
\dt
You should see authors and books.

Delete/drop the tables:
DROP TABLE authors;
DROP TABLE books;

Confirm the tables are gone by listing all the tables in the database.
\dt
It should be empty.

Create tables with default values and constraints

[1:02, 1:26, and 1:45 Video timestamps]

We will recreate the books table with defaults and constraints. Below is the SQL statement we will use:

CREATE TABLE books (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
      isbn CHAR(13) NOT NULL UNIQUE,
      title VARCHAR(255) NOT NULL,
      author VARCHAR(100) NOT NULL,
      description TEXT,
      quantity INTEGER NOT NULL CHECK(quantity >= 0),
      price NUMERIC(5,2) NOT NULL CHECK(price >= 0),
  on_sale BOOLEAN DEFAULT false,
  published_date DATE,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Constraints:

After the field name and data type, we added constraints to most of the fields including not null, unique and check constraints.

NOT NULL constraint means there must be a value in that field or an error will be thrown.

The isbn, title, author, quantity, and price fields all require a value.

UNIQUE constraint means there are no other rows with the same value for that field. If there are, it will throw an error.

The book isbn number must be unique.

CHECK(expression) constraints allow you to add custom constraints. Put an expression in the parentheses. If the result is false, it will throw an error.

We put checks on the price and quantity that they must be greater than or equal to zero.

Note:
Database constraints should not be the only place you check the data. In a web application for example, you would validate the data on the server before saving it to the database. It does not hurt to check it twice and adds more security, especially for critical data.

Default Values:

[2:05 Video timestamp]

We can add default values for fields. If the row is created with no value for that field, it will be set to the default value.

We set a default value of false to the on_sale field:

on_sale BOOLEAN DEFAULT false,

For the created_at field we are adding a default value of the current timestamp. Postgres will then automatically add the current date and time value when the row is added:

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

Primary Key

[3:15 Video timestamp]

Primary Key is a column, or combination of columns, in a table designated to uniquely identify the table row.

The value must be unique.
The value must not be null.
There can only be one primary key in a table.

The primary key can be a field already present in the data model (e.g., ISBN number for books).

But generally it will be a field created specifically to be the primary key.

In the CREATE TABLE statement we added the id field as follows:

id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

Id is an integer field.
GENERATED ALWAYS AS IDENTITY tells Postgres to make it auto-incrementing starting from 1. So every new row created will automatically be assigned an id value one higher than the last row created.

Note: MySQL and SQLite use different keywords than Postgres for this.

The id field is set with the PRIMARY KEY constraint.

Test the constraints

To test out the constraints, try to create a new book that fails some of the constraints.

The below insert statement creates a book without a title. Title has a not null constraint.
And the quantity fails the check constraint because it is below 0.

-- Fail:

INSERT INTO books(isbn, author, description, quantity, price, on_sale) 
VALUES('1234567891011', 'Joey R.', 'Lorem ipsum.', -3, 0, true);

If you open the Query Tool and try to execute it you should get an error.

The below insert statement passes all the constraints.

INSERT INTO books(isbn, title, author, description, quantity, price, published_date) 
VALUES('1234567891011', 'Learn Databases', 'Joey R.', 'Lorem ipsum.', 43, 19.99, '2022-07-01');

Index

[6:36 Video timestamp]

An index is an additional data structure that speeds up queries at the cost of additional writes and storage space. The UNIQUE keyword enforces uniqueness. The index does not have to use the unique constraint.

CREATE [UNIQUE] INDEX index_name 
ON table_name(column_name);

Let's add an index on the isbn number in our books table. If we frequently search for books by their ISBN number and we have a large database of books, then making that an index may improve performance.

CREATE UNIQUE INDEX books_index_isbn 
ON books(isbn);

There is no consensus on naming convention for indexes, but tablename_index_fieldname is a useful format.

Remove Index:

We will leave the index in place, but if you wanted to delete the index, use the below format.

DROP INDEX [IF EXISTS] index_name;

Using IF EXISTS is optional.

The actual command to drop the index is:

DROP INDEX IF EXISTS books_index_isbn;

Foreign Key

[9:05 Video timestamp]

In the Schema for our bookstore database we noted that there is a one-to-many association between the books and authors tables.

One-to-many association:

Authors is the parent table. One author can have zero, one or multiple books.
Books is the child table. Every book must have exactly one author.

Add the authors table with some data

Use the Query Tool to recreate the authors table as before:

CREATE TABLE authors (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
      name VARCHAR(255),
      bio TEXT,
  date_of_birth DATE
);

And insert two authors into the table:

INSERT INTO authors(name, bio, date_of_birth) 
VALUES('Joey R.', 'Lorem ipsum.', '1990-05-19'),
          ('Sheena R.', 'Lorem ipsum.', '1993-06-28');

To see the new rows for either:

Click on the authors table, then the View Data toolbar
Or in the Query Tool or PSQL Tool execute the below query (covered in a separate tutorial):

SELECT * FROM authors;

Let's delete the books table and recreate it with the association.

Recreate the books table:

Drop the books table:

DROP TABLE books;

Recreate the "books" table with a foreign key constraint:

CREATE TABLE books (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
      isbn CHAR(13) NOT NULL UNIQUE,
      title VARCHAR(255) NOT NULL,
      author_id INTEGER NOT NULL,
      description TEXT,
      quantity INTEGER NOT NULL CHECK(quantity >= 0),
      price NUMERIC(5,2) NOT NULL CHECK(price >= 0),
  on_sale BOOLEAN DEFAULT false,
  published_date DATE DEFAULT CURRENT_DATE,
      FOREIGN KEY (author_id) REFERENCES authors (id)
);

We made the following changes to the table:

Replaced the author VARCHAR NOT NULL field with the author_id INTEGER NOT NULL field. This will be the id of the author from the authors table. The NOT NULL constraint makes this field required.
Added a FOREIGN KEY constraint on the author_id field and associated that with the id field in the authors table. FOREIGN KEY (author_id) REFERENCES authors (id)

This constraint ensures that the author_id number is present in the authors table, otherwise it throws an error.
It also prevents an author row from being deleted if the author has a book in the books table.

Add a book and test the foreign key constraint

Let's insert a book in the new books table:

INSERT INTO books(isbn, title, author_id, description, quantity, price, published_date) 
VALUES('1234567891011', 'Learn Databases', 1, 'Lorem ipsum.', 43, 19.99, '2022-07-01'),
    ('1234567891012', 'Learn Node.js', 2, 'Lorem ipsum.', 26, 24.99, '2022-05-01');

Now let's try to insert a book that fails the foreign key constraint. The author id of 3 does not exist in the authors database:

INSERT INTO books(isbn, title, author_id, description, quantity, price, published_date) 
VALUES('1234567891013', 'Learn JavaScript', 3, 'Lorem ipsum.', 18, 23.99, '2022-08-01');

Now let's try to delete the author with id 1. This should fail because that author's id is present in the book we added:

DELETE FROM authors WHERE id = 1;

Foreign Key Constraints: On Delete actions

The default foreign key constraint prevents an author from being deleted if there are one or more books associated with that author. You can change this behavior:

To delete the associated books when the author is deleted, append: ON DELETE CASCADE

In the CREATE TABLE statement, the foreign key constraint would be:
FOREIGN KEY (author_id) REFERENCES authors (id) ON DELETE CASCADE

To set the associated book author_id values to null, append: ON DELETE SET NULL

In the CREATE TABLE statement, the foreign key constraint would be:
FOREIGN KEY (author_id) REFERENCES authors (id) ON DELETE SET NULL

We'll leave our table with the default On Delete action.

Create Tables (Video Tutorial)