CODEWITHSUNDEEP
postgresqlforeign-keys
gruenkohl
gruenkohl

Reputation: 103

Foreign Key Depending on a Second Foreign Key

my problem is quite simple, but I cannot find any solution. Lets say i have the following:

Table food:
id (key);
category_id;
food_id

Table category:
category_id (key);
category_names [fruits, vegetables]

Table fruits
food_id (key);
fruit [apple, banana]

Table vegetables
food_id (key);
vegetable [bean, carrot]

I now want to build a foreign key constraint from table food to the tables fruits + vegetables, depending on the category, specified in category_id. Is this possible?

Upvotes: 0

Views: 231

Answers (3)

Sean
Sean

Reputation: 10206

If your problem is really that simple, then just use VIEWS:

CREATE TABLE food (
  id SERIAL,
  name TEXT NOT NULL,
  category TEXT NOT NULL,
  PRIMARY KEY(id),
  CHECK(category = 'fruit' OR category = 'veggie')
);
CREATE UNIQUE INDEX food_name_udx ON food(name);

CREATE VIEW fruit AS
  SELECT id, name FROM food WHERE category = 'fruit';

CREATE VIEW veggie AS
  SELECT id, name FROM food WHERE category = 'veggie';

Use DOMAINS or an external table with a FOREIGN KEY CONSTRAINT if category grows beyond 5-10 entries.

Upvotes: 1

pleasedontbelong
pleasedontbelong

Reputation: 20102

I'd change your model to this:

Table food:
id (key);
name;
category_id;

Table category:
category_id (key);
category_names [fruits, vegetables]

i can't see why you need a table for the fruits and vegetables, except if those tables have different relations to other tables..

you could also read this page about modeling subclasses in a database

Good Luck!

Upvotes: 0

Lukas Eder
Lukas Eder

Reputation: 220842

Foreign keys can only reference primary keys or unique keys. Your food.food_id is neither. That's the technical aspect. From the schema design point of view, I'm not sure if you may have too many tables for your purpose.

Upvotes: 0

Related Questions