Foreign key reference to column that is no primary key

Question

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.

Answer 1

Your problem is that your database design is not normalized at all. Split the data in several tables, like region, subject, module etc. so that each row in a table describes one of these entities.

Then it will be easy to establish foreign key relationships between the entities so that referential integrity is guaranteed.

Answer 2

First a note about hierarchies. The technique you are currently trying to use for modules is called adjacency list. I would encourage you to look-up few other, more appropriate, SQL techniques like: path enumeration, nested sets, closure table.

However, something seems to be wrong with this sentence:

But the parent module always has the same values as the child modules in all 4 categories, so these columns would be redundant.

This sounds unusual at best, a hierarchy may look like:

1        region/
1.1      region/school/
1.1.1    region/school/class/
1.1.1.1  region/school/class/subject/

I will go out on a limb and claim that the problem here is not about foreign keys, but about implementing a hierarchy in SQL.

To simplify the problem, I would suggest to experiment with path enumeration for your project and explore other techniques if required.

-- Module titled TITLE is categorized as CAT_PATH.
--
module {TITLE, CAT_PATH}
    PK {TITLE}

Where CAT_PATH is one of:

• region/
• region/school/
• region/school/class/
• region/school/class/subject/

This will likely result in a different set of problems and questions, but those are going to be easier to solve.

Answer 3

@ypercubeᵀᴹ gave a simple answer in their comment, which is exactly what I needed: It is possible to specify a foreign key from multiple columns to multiple columns.

You can have a FK from (cat1, cat2, cat3, cat4, parent_module_title) that REFERENCES (cat1, cat2, cat3, cat4, module_title).