In this post, I want to dive a bit deeper into SQL – I wrote about this in a few other posts, but lately I’ve been working with Flask (a framework for building back-end python apps) and realize that I want to make sure I’m solid in this foundation. It’s going to be important going forward, so I’ll be writing my notes here for future refererence.

Foreign Key Constraints

Summary

SQL Scripting

Here’s the ER diagram, we are going to build a script to build the tables.

CREATE TABLE categories (
    id SERIAL,
    name TEXT NOT NULL UNIQUE,
    description TEXT,
    picture TEXT,
    PRIMARY KEY (id)
);

CREATE TABLE products (
    id SERIAL,
    name TEXT NOT NULL,
    discontinued BOOLEAN NOT NULL,
    category_id INT,
    PRIMARY KEY (id)
);


ALTER TABLE products
ADD CONSTRAINT fk_products_categories
FOREIGN KEY (category_id)
REFERENCES categories;

SQL Dump in Container

docker exec pg_container pg_dump my_blog > blog.sql