2

Let's say I have the following tables describing some vehicles

CREATE TABLE class ( id INT GENERATED ALWAYS AS IDENTITY, label TEXT NOT NULL, PRIMARY KEY (id, label), UNIQUE (id) ); CREATE TABLE maintenance_frequency ( id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, class_id INT REFERENCES class (id), freq FLOAT NOT NULL ); CREATE TABLE function ( id INT GENERATED ALWAYS AS IDENTITY, class_id INT REFERENCES class (id), label TEXT NOT NULL, PRIMARY KEY (id, label), UNIQUE (id) ); CREATE TABLE vehicle ( id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, label TEXT NOT NULL, class_id INT REFERENCES class (id), freq_id INT REFERENCES maintenance_frequency (id), fun_id INT REFERENCES function (id) ); 
INSERT INTO class(label) VALUES ('car'), ('motorbike'); INSERT INTO function(class_id, label) VALUES (1, 'leisure'), (1, 'work'), (2, 'beach trip'); INSERT INTO maintenance_frequency(class_id, freq) VALUES (1, 0.5), (1, 1.0), (2, 1.5), (2, 2.0); 
SELECT * FROM class; SELECT f.id, c.label as class, f.label as function FROM function as f INNER JOIN class as c ON c.id = f.class_id; SELECT mf.id, c.label as class, mf.freq FROM maintenance_frequency as mf INNER JOIN class as c ON c.id = mf.class_id;
id label
1 car
2 motorbike
id class function
1 car leisure
2 car work
3 motorbike beach trip
id class freq
1 car 0.5
2 car 1
3 motorbike 1.5
4 motorbike 2

So, a vehicle can be of class "car" or "motorbike", and based on that, can be assigned 1 maintenance frequency value in (0.5, 1.0) and can be a assigned a function in ("leisure", "work") if it's a car, while for a motorbike the frequencies are (1.5, 2.0) and just ("beach trip") for what concerns function. For simplicity, let's assume a vehicle can be assigned only one "feature" (i.e. can be related to just one row of each "feature table").

I'd like to know how to enforce the right constraints in order to ensure that:

-- This should work INSERT INTO vehicle(label, class_id, freq_id, fun_id) VALUES ('Alice', 1, 1, 1); 
--- This should not work because `freq_id=3` is for motorbikes INSERT INTO vehicle(label, class_id, freq_id, fun_id) VALUES ('Bob', 1, 3, 1) 
--- This should not work because `fun_id=3` is for motorbikes INSERT INTO vehicle(label, class_id, freq_id, fun_id) VALUES ('Charlie', 1, 1, 3) 
--- Third row should not be possible SELECT v.label, c.label as class, f.label as function, mf.freq FROM vehicle as v INNER JOIN class as c ON c.id = v.class_id INNER JOIN maintenance_frequency as mf ON mf.id = v.freq_id INNER JOIN function as f ON f.id = v.fun_id;
label class function freq
Alice car leisure 0.5
Bob car leisure 1.5
Charlie car beach trip 0.5

fiddle

Improve this question
5
  • You're not going to get any meaningful sort of integrity without keys. Get rid of the id columns, start over, use composite keys. Commented Nov 14, 2022 at 15:43
  • Thanks @bbaird, the schema is just hypothetical. Could you maybe elaborate on the alternative schema using composite key as you suggest to encode the same information? That's exactly the real question I'm asking. Commented Nov 14, 2022 at 16:35
  • Ditch the Ids and think about what makes each entity unique - use that as the primary key and migrate to the child entities. For example, a vehicle class can have a set of values for maintenance frequency. So ('Car',0.5),('Car',1.0),('Motorbike',1.5),('Motorbike',2.0) - in this case the primary key is (Class,Frequency), not id. Commented Nov 14, 2022 at 19:06
  • Welcome to dba stackexchange Have a look at this: How to create a Minimal, Complete, and Verifiable Example for database-related questions Commented Nov 14, 2022 at 20:09
  • Edited the answer adding the Fiddle. Commented Nov 15, 2022 at 9:37

1 Answer 1

Reset to default
0

Quick Answer

You can't directly perform foreign-key constraints against a hierarchy. You need to flatten it. Put your data's keys into a table and constrain against that table.

Determining The Key

Let's back up a moment and examine the entities as you've defined them above.

Our hierarchy is like this:

  • Vehicle
    • Label
    • Class
      • Label
      • Maintenance Frequency
      • Function

Now we have some rules:

  1. Class Cars must have a Maintenance Frequency of either 0.5 or 1.0.
  2. Class Motorbikes must have a Maintenance Frequency of either 1.5 or 2.0.
  3. Class Motorbikes must have a Function of 'beach trip'.

So given this setup, what information do we need to uniquely identify any particular vehicle Class, in such a way that we can ensure it complies with the rules? It's all of the attributes of Class:

(label, maintenance_frequency, function)

This represents our key.

(If Rule #3 didn't exist, we could drop function out of the key. Alternatively, if it is the sole exception we could use a check constraint to enforce it, but we're getting ahead of ourselves.)

Since the rules are arbitrary, we will need to define all of the valid keys so new entries can validate against them, I'll call it config:

config

label maintenance_frequency function
car 0.5 leisure
car 0.5 work
car 1.0 leisure
car 1.0 work
motorbike 1.5 beach trip
motorbike 2.0 beach trip

Building The Constraint

Now that we have a table we can validate against, let's revisit the vehicle table:

id label class maintenance_frequency function
1 Alice car 0.5 leisure
2 Bob car 1.0 work

We add a FOREIGN KEY constraint against the config table for our key (label, maintenance_frequency, function) so all of the columns can be validated at insert time.

If we wanted to add a new entry, Charlie, like this:

3 Charlie car 1.5 leisure

The constraint finds that (car, 1.5, leisure) doesn't exist in config, the row is thus rejected.

To convert to PostgreSQL parlance, here are the table & constraint definitions:

CREATE TABLE config ( label text NOT NULL, maintenance_frequency numeric NOT NULL, function text NOT NULL, PRIMARY KEY (label, maintenance_frequency, function) ); INSERT INTO config VALUES ('car', 0.5, 'leisure'), ('car', 0.5, 'work'), ('car', 1.0, 'leisure'), ('car', 1.0, 'work'), ('motorbike', 1.5, 'beach trip'), ('motorbike', 2.0, 'beach trip'); CREATE TABLE vehicle ( name text, label text, maintenance_frequency numeric, function text, FOREIGN KEY (label, maintenance_frequency, function) REFERENCES config(label, maintenance_frequency, function) );

With config loaded, trying to add the Charlie car is met with an error:

> insert into vehicle values ('Charlie', 'car', 1.5, 'leisure'); ERROR: insert or update on table "vehicle" violates foreign key constraint "vehicle_label_maintenance_frequency_function_fkey" DETAIL: Key (label, maintenance_frequency, function)=(car, 1.5, leisure) is not present in table "config".

From here, if you want to introduce surrogate keys, you can do so with the knowledge that the surrogate keys map to the correct primary keys. You can also add any additional attributes you like.

Summary

You can't directly perform foreign-key checks against a hierarchy, you need to flatten it. With a small number of keys its practical to just enumerate all of them and use a composite foreign key constraint.

This is a simple example and my answer is not well normalized, mostly for illustration purposes. It's possible to have config be the result of a query of the other tables and validated by foreign key constraints itself. If you had 500,000 configurations to maintain, you'd likely do it through a mapping table of some kind.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.