I am in the process of setting up table relationships, using a relational diagram provided by our vendor, in PostgreSQL 9.5.3 and am seeking clarification on how to relate a child table that serves two parent tables using the same column. I am using the same PostgreSQL version and database on both Windows 10 Desktop (development) and Amazon RDS (production).

I had no problems using the format

ALTER TABLE schema.childTable
  ADD CONSTRAINT foreignKey_name
  FOREIGN KEY (cColumn)
  REFERENCES schema.parentTable (pColumn);

to establish many-to-one table relationships.

As mentioned above, the problem I have has to do with establishing foreign keys for a child table that serves two parent tables. When I try that using the above formula to set up foreign keys for each of the tables I get an error from PostgreSQL stating the key cannot be generated because values in the child table are missing from the parent table and constitute a foreign key constraint violation. A simple example of the structure is the following excerpt from the relational diagram: enter image description here

I checked and there are values for both WA and WL in both WXEA and WXNM.

The diagram takes into account the potential for missing values in the parent tables by displaying many (non mandatory) to one (non mandatory) relations between the tables. My understanding is lines in relational diagrams represent foreign keys, but that obviously does not work as there are no inner/outer/full foreign keys.

What I am wondering is the following: Are the lines in the above image actually joins (I thought joins were a more ad hoc query mechanism) or does the diagram assume the DBA will create two intermediate tables from the child, with each intermediate table serving as the child to one of the parent tables?

  • 1
    Can you show us the actual statement that produces the error message? Actually not only that. Having all the tables' definitions would be good, too. – ypercubeᵀᴹ Nov 16 '17 at 21:02
  • 1
    So, you have the wxnm (id) column to be used for the Foreign Key to both WA and the WL table? – ypercubeᵀᴹ Nov 16 '17 at 21:07
  • 1
    No, that's the issue then. If the WXNM.id is used for 2 foreign keys, then any of its values should exist in both WA and WL tables. That is unlikely, unless there is also relationship between those tables (either an FK between them or they have bot FK to another table, i.e. are both child tables of a parent table). – ypercubeᵀᴹ Nov 16 '17 at 21:10
  • 1
    So the question that needs an answer (probably from the vendor) is "what is the relationship between Water Areas and Water Lines?". – ypercubeᵀᴹ Nov 16 '17 at 21:15
  • 1
    Most probably, you'll need 2 columns in each (WXEA and WXNM), one to be the FK to WA and another for WL. – ypercubeᵀᴹ Nov 16 '17 at 21:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.