Restrict “subcategory” based on other “category” column

I’m creating a SQLite database where I have 3 tables:

transactions, categories, sub-categories

in the transactions table, there is a column “category” with a foreign key to the categories table

in the sub-categories table, there is column “parent-category” with a foreign key to the categories table

Now, how can I ensure that the sub-category that I fill in with transactions matches its supposed parent-category also stated in transactions?

I’m sorry for terminology, I’m very new to SQL

Answer

You need to add the “category” column to the “sub-categories” table if you want to enforce the matching via the database. Then you define a composite (“category”, “sub-category”) foreign key from transactions to sub-categories. You could also use “category_id” instead of “category” in both cases, but you need to consider whether you want to deal with the added complexity. Also, do not forget to add the “sub-categories”.”category” -> “categories”.”category” foreign key.