Difference in foreign keys for sec_group and sec_group_hierarchy tables in postgres

Support
#1

The automatically generated foreigh keys in sec_group and sec_group_hierarchy tbles are different. there is only one fk in sec_group - sec_group_parent, whereas there are two fk in sec_group_parent tables - sec_group_hierarchy_group and sec_group_hierarchy_parent.

This is causing problem is access to these tables form different platform. (I require this).

should i define second fk manually?

#2

These are two different tables. Why do you think they should have the same set of foreign keys? It doesn’t work like this.

#3

second table is sec_group_hierarchy and not sec_group_parent…

#4

When I access the same database from entity framework i am getting this error

[2022-04-27T12:11:07.417Z] Error: System.InvalidOperationException: The number of properties specified for the foreign key {‘group_id’, ‘parent_id’} on entity type ‘SecGroupHierarchy’ does not match the number of properties in the principal key {‘id’} on entity type ‘SecGroup’.

Yep, it is not related to foreign keys and your reply is perfect and my question require rephrasing. But how to solve this issue. No other table from database is causing problem.

Please guide.

#5

I’m not an expert in the EntityFramework.
But it seems that you are trying (somehow?) to create a compound foreign key in the entity definition by using two columns.
While there should be two separate foreign keys, each of them is using one column:

# \d sec_group_hierarchy
                       Table "public.sec_group_hierarchy"
     Column      |            Type             | Collation | Nullable | Default 
-----------------+-----------------------------+-----------+----------+---------
 id              | uuid                        |           | not null | 
 create_ts       | timestamp without time zone |           |          | 
 created_by      | character varying(50)       |           |          | 
 group_id        | uuid                        |           |          | 
 parent_id       | uuid                        |           |          | 
 hierarchy_level | integer                     |           |          | 
 sys_tenant_id   | character varying(255)      |           |          | 
Indexes:
    "sec_group_hierarchy_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "sec_group_hierarchy_group" FOREIGN KEY (group_id) REFERENCES sec_group(id)
    "sec_group_hierarchy_parent" FOREIGN KEY (parent_id) REFERENCES sec_group(id)