I know, foreign keys should uniquely identify rows and therefore reference a primary key.
However, consider my example: There are 4 pre-defined categories, and for some combinations of values for these categories, there is a hierarchy of modules. The hierarchical structure is implemented by a parent module column.
My approach would be to implement it in a single table with these columns:
- category 1 (e.g., region)
- category 2 (e.g., kind of school)
- category 3 (e.g., class)
- category 4 (e.g., subject)
- module title
- parent module title
- and a primary key consisting of all categories + the module title.
I would want the parent module title to reference the module title column. Now because a foreign key needs to reference the whole primary key, I would need to insert columns like parent category 1, parent category 2, parent category 3, parent category 4. But the parent module always has the same values as the child modules in all 4 categories, so these columns would be redundant.
It would be ideal if I could write something like this in my table declaration:
--- fictitious SQL! ---
create table modules (
...,
module_title varchar(50),
foreign key parent_module_title varchar(50)
references modules(module_title) where
modules.category1 = category1 and modules.category2 = category2 and
modules.category3 = category3 and modules.category4 = category4
)
Or something like:
--- fictitious SQL! ---
...
parent_module_title varchar(50)
check (exists
(select * from modules where modules.module_title = parent_module_title))
...
These are both in fictitious wishfulSQL (the latter throws cannot use subquery in check constraint
), but is there a way of achieving my aim? I'm using Postgres if that matters.