3

I've got two tables that relate to one another in a 1:M relationship: parent and child. I've constrained the parent_id column in the child table to match only parent's table id values, by using the Foreign Key constraint. Now I want a third table - called another_table - to have two FK columns that references the id column in the parent and child tables.

How can I restrict the child_id FK column in the third table, only to values from the child table that match parent_id value?

enter image description here

EDIT

E.G: As I mentioned above I've got three tables - parent, child and another_table. Their content looks like this:

SELECT * FROM public.parent

id  name
1   A
2   B
3   C

SELECT * FROM public.child

id name parent_id 
1   A1  1
2   A2  1
3   A3  1
4   B1  2
5   C1  3
6   C2  3

SELECT * FROM public.another_table

enter image description here

I want to find a way within the DB structure (at the mean time), to prevent from inserting or updating a value in the third table's child_id column, that do not comply with the child's table data.

EranGeo
  • 81
  • 4
  • If you FK on the Child ID, you don't need to FK on the Parent ID in your third table since child has FK on parent. Parent ID > Child ID > Third Table. Unless you are saying you want a parent ID other than the one on the Child record. – interesting-name-here Nov 30 '18 at 17:57
  • I want to use the parent as some sort of a filter to narrow down possible values of the child table. e.g: group of animals - mammal, insects etc; specie - monkey, elephant, dolphin etc. – EranGeo Nov 30 '18 at 23:20
  • then my first comment applies. you are already constraining the parent on the child. since the child already has a constraint on the parent, parent mammal is with child monkey and monkey is with baboon, third table, you already have the parent because baboon is tied to monkey which is tied to mammal – interesting-name-here Nov 30 '18 at 23:55
  • Thank you for the reply. I think you are right for some cases but for others there's a need for both columns. For example: I've observed several animals, some of them I'm sure to be flies but I'm confused about the others to between mosquitoes and flies. In that case, all the animals I've observed are for sure insects ('parent') but for some I know the exact specie ('child') and for some not. I think that for this cases I need both columns. I'll be glad if you can tell me otherwise or resolve the issue from a different point of view. – EranGeo Dec 01 '18 at 08:38
  • Hi. This is a faq. Please always google error messages & many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & 'site:stackoverflow.com' & tags & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using 1 variant search as title & keywords for tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on [mcve]. – philipxy Dec 01 '18 at 10:44
  • If SQL DBMSs implemented the relational model or standard SQL ASSERTIONs then you could declaratively constrain 2 tables to what you want. Meanwhile you need to use triggers or use an idiom that adds a column (essentially, a type/variant tag) to an intermediate table so that the 2 can be constrained declaratively via FKs. PS [What are the options for storing hierarchical data in a relational database?](https://stackoverflow.com/q/4048151/3404097) – philipxy Dec 01 '18 at 10:49
  • It is not clear what you mean by "restrict": Do you want to know how to write a constraint or trigger for this design, modify this design so that you can enforce via declarative constraints & minmal triggers or are you talking about writing a query using this design? Also--[mcve] please. – philipxy Dec 03 '18 at 18:45
  • Thank you @philipxy for your replies. As you mentioned, there's more than one way to design an hierarchical data. I haven't saw one that fits the one I've gave in this example. I'll be glad if you can address this design in your answer though a suggestion for a better design is really welcomed. And by 'restrict' I mean using any of the DB structure objects to prevent from inserting or updating incorrect data. – EranGeo Dec 03 '18 at 21:04

1 Answers1

1

Since the parent is linked to the child, the child has the parent link. You do not need to link both parent and child to your child's children. You only need to link the child's children to the child which already has the parent.

    Parent
      /\
child1  child2
           /\
   pc_child  pc_child2

Visual Explanation

interesting-name-here
  • 1,851
  • 1
  • 20
  • 33