When to use SQL Foreign key using peewee?

Question

I'm currently using PeeWee together with Python and I have managed to create a decent beginner

CREATE TABLE stores (
    id SERIAL PRIMARY KEY,
    store_name TEXT
);


CREATE TABLE products (
    id SERIAL, 
    store_id INTEGER NOT NULL,
    title TEXT,
    image TEXT,
    url TEXT UNIQUE, 
    added_date timestamp without time zone NOT NULL DEFAULT NOW(),
    PRIMARY KEY(id, store_id)
);


ALTER TABLE products
ADD  CONSTRAINT "FK_products_stores" FOREIGN KEY ("store_id")
        REFERENCES stores (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE RESTRICT;

which has been converted to peewee by following code:

# ------------------------------------------------------------------------------- #
class Stores(Model):
    id = IntegerField(column_name='id')
    store_name = TextField(column_name='store_name')

    class Meta:
        database = postgres_pool
        db_table = "stores"

    @classmethod
    def get_all(cls):
        try:
            return cls.select(cls.id, cls.store_name).order_by(cls.store)
        except Stores.IntegrityError:
            return None




   # ------------------------------------------------------------------------------- #
class Products(Model):
    id = IntegerField(column_name='id')
    store_id = TextField(column_name='store_id')
    title = TextField(column_name='title')
    url = TextField(column_name='url')
    image = TextField(column_name='image')
    store = ForeignKeyField(Stores, backref='products')

    class Meta:
        database = postgres_pool
        db_table = "products"

    @classmethod
    def get_all_products(cls, given_id):
        try:
            return cls.select().where(cls.store_id == given_id)
        except Stores.IntegrityError:
            return None

    @classmethod
    def add_product(cls, pageData, store_id):
        """
        INSERT
        INTO
        public.products(store_id, title, image, url)
        VALUES((SELECT id FROM stores WHERE store_name = 'footish'), 'Teva Flatform Universal Pride',
               'https://www.footish.se/sneakers/teva-flatform-universal-pride-t51116376',
               'https://www.footish.se/pub_images/large/teva-flatform-universal-pride-t1116376-p77148.jpg?timestamp=1623417840')
        """
        try:
            return cls.insert(
                store_id=store_id,
                title=pageData.title,
                url=pageData.url,
                image=pageData.image,
            ).execute()
        except Products.DoesNotExist:
            return None
        except peewee.IntegrityError as err:
            print(f"error: {err}")
            return None

My idea is that when I start my application, I would have a constant variable which a store_id set already e.g. 1. With that it would make the execution of queries faster as I do not need another select to get the store_id by a store_name. However looking at my code. I have a field that is: store = ForeignKeyField(Stores, backref='products') where I am starting to think what do I need it in my application.

I am aware that I do have a FK from my ALTER query but in my application that I have written I cannot see a reason why I would need to type in the the foreign key at all but I would like some help to understand more why and how I could use the value "store" in my applciation. It could be as I think that I might not need it at all?

Answer 1

Hello! By reading your initial idea about making "the execution of queries faster" from having a constant variable, the first thing that came to mind was the hassle of always having to manually edit the variable. This is poor practice and not something you'd want to do on a professional application. To obtain the value you should use, I suggest running a query programmatically and fetching the id's highest value using SQL's MAX() function.

As for the foreign key, you don't have to use it, but it can be good practice when it matters. In this case, look at your FK constraint: it has an ON DELETE RESTRICT statement, which cancels any delete operation on the parent table if it has data being used as a foreign key in another table. This would require going to the other table, the one with the foreign key, and deleting every row related to the one on the previous table before being able to delete it.

In general, if you have two tables with information linked in any way, I'd highly suggest using keys. It increases organization and, if proper constraints are added, it increases both readability for external users and reduces errors.

When it comes to using the store you mentioned, you might want to have an API return all products related to a single store. Or all products except from a specific one.

I tried to keep things simple due to not being fully confident I understood the question. I hope this was helpful.