0

Problem Statement

I have five tables, a parent, child, options, child_sections, and child_options table (parent-child in the programming sense). For the Problem Statement, I'm going to name them toy_type, model, color, model_part and model_color.

CREATE TABLE "toy_type" (
  "type_id" int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  "type_name" text NOT NULL
);

CREATE TABLE "model" (
  "model_id" int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  "type_id" int REFERENCES "toy_type"("type_id")
);

CREATE TABLE "color" (
  "type_id" int REFERENCES "toy_type"("type_id"),
  "color_name" text,
PRIMARY_KEY ("type_id", "color_name")
);

The color table uses a compound primary key since the blue on the plane toy isn't the same as the blue on the boat toy.

CREATE TABLE "model_part" (
  "model_id" int REFERENCES "model"("model_id"),
  "part_id" int NOT NULL,
  PRIMARY KEY("model_id", "part_id")
);

CREATE TABLE "model_color" (
  "model_id" int,
  "part_id" int,
  "color_name" text,
  PRIMARY KEY ("model_id", "part_id", "color_name"),
   CONSTRAINT "FK_model_part.model_part_id"
    FOREIGN KEY ("model_id", "part_id")
      REFERENCES "model_part"("model_id","part_id"),
   ...
);

A part can have more than one color on it.

The ellipses are where my problem is occurring. How can I get a foreign reference to color_name?

Considered Solutions

  1. The easiest by far is just to add a type_id column to model_color and reference the whole compound key which would be fine and functional but I'm not a fan of the unnecessary information. If I'm painting a part I need to know what colors to get I don't really need to know what type of toy it is just the model and the part. Also, I am asking this question to learn if there's a different way.
  2. The other easy solution would be to assign some sort of UUID to the colors(like a HEX code) and reference that UUID instead. But for my actual data set, there isn't some nice UUID analogy like hex codes, and I would like human readable select statements without having to join to a color_codes table.

Sought-after Solution

As a human, it is pretty easy for me to reason that if I need to make sure the foreign key is unique then I would need to make sure that the type_id associated with a given model_id is part of the unique set with my color name. Is there a way to communicate this to the database through a foreign key constraint?

My thought would be a constraint that looks something along the lines of

  CONSTRAINT "FK_model.type_id"
    FOREIGN KEY "type_id"
      REFERENCES "model("model_id","type_id"),
  CONSTRAINT "FK_color.color_name"
    FOREIGN KEY "color_name"
      REFERENCES (SELECT "color_name" FROM color WHERE type_id="FK_model.type_id")

Where type_id wouldn't be a column in model color, just a reference to the type_id referenced by the model table.

How to do this? It a bad choice?

philipxy
  • 14,867
  • 6
  • 39
  • 83
Braedon
  • 41
  • 1
  • 5
  • This is a faq. The current SQL idiom is to include the "redundant" column to allow declarative vs triggered enforcement. (Current products lack ASSERTION.) But to (re)search effectively you need to compose many concise precise complete generic statements of your problem/goal. Which this post doesn't contain. PS Giving invalid code is not a good way to communicate what you wish it meant. Similarly you use "FK" unclearly & apparently to mean something that is not a FK.PS It is unhelpful & unclear to ask 'A or B' when A & B are not mutually exclusive and/or not the only options. Ask 1 question. – philipxy Aug 16 '22 at 00:11
  • 1
    [Storing "redundant" foreign keys to avoid joins](https://stackoverflow.com/a/40563731/3404097) This is actually a special case of subtype tags. [Group dependency SQL design](https://stackoverflow.com/a/42183218/3404097) (Every FK involves a case of subtypes.) – philipxy Aug 16 '22 at 01:36
  • Don't know what you are trying to say in your comment. I am not sure of what a lot of your post is trying to say, that's why I said "apparently". "How can I get a foreign reference to color_name?" What's stopping you? You seem to mean, how to constrain in a certain way that reminds you of a FK constraint. I don't know what "need to make sure the foreign key is unique" is trying to say. Use enough words, sentences & references to parts of examples to clearly & fully say what you mean. Similarly your comment is unclear--how could a FK constraint not be a FK constraint?--It is one. – philipxy Aug 17 '22 at 01:21
  • My comments are neutral, relevant & helpful. PS When I used F & K in quotes I meant the words "foreign key". My point is illustrated again by "requires foreign keys to have a UNIQUE constraint" because there is no such thing as a SQL FK to a non-unique subrow so you are again using "foreign key" to mean something that isn't a foreign key, and other people don't know your idiosyncratic meaning, although I suppose it is columns whose subrow values appear elsewhere. Abusing terms impedes communication, use terms properly to say what you mean. Please clarify via edits, not comments. Good luck. – philipxy Aug 18 '22 at 23:53

0 Answers0