Postgresql – Foreign key reference to column that is no primary key

check-constraintsdatabase-designforeign keyhierarchypostgresql

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.

Best Answer

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.

Related Question