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:
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?