Composite foreign keys don't show up anywhere in the table structure.

[dourvaris]

Taking the following tables:

create table a (id1 int, id2 int, primary key (id1, id2));
create table b (id1 int, id2 int, foreign key (id1, id2) references a (id1, id2));

Looking at table b structure in postico doesn't show the foreign key that references table a.

[jas]

I was just about to submit a request for this feature.

In addition to being able to see existing composite foreign keys in the Structure tab, it would be nice if I could even add composite foreign keys in the GUI, just like I can do for single-column foreign keys.

Currently the the Structure tab displays nothing about the composite foreign key. The DDL view does confirm that the composite foreign key exists.

Here's an example of the type of tables I'm using:

CREATE TABLE users_locations (
  user_id integer NOT NULL REFERENCES users(id),
  location_id integer NOT NULL REFERENCES locations(id),
  PRIMARY KEY (user_id, location_id)
);

CREATE TABLE users_notes (
  user_id integer NOT NULL REFERENCES users(id),
  location_id integer REFERENCES locations(id),
  note text,
  created_at timestamptz,
  updated_at timestamptz,
  FOREIGN KEY (user_id, location_id) REFERENCES users_locations(user_id, location_id)
);

This schema ensures that rows can only be associated with valid combinations of the referenced columns.

Here is a mock up of the type of UI I was expecting.

[mashaalmemon]

+1 for this feature. Postico is great and just noticed that it doesn't show composite foreign keys in the interface.

[fabioespinosa]

+1

[JonasEtzold]

+1

[etiennecl]

+1

[vliegenthart]

+1

[CalvinLeGassick]

+1

[bursteways]

+1

[Aseelaldallal]

+10

[Smmaca]

+1

[mgan59]

ugh this hurt -- been trying to track down some migration issues between various deployments/environments. Was trying to unravel a specific FOREIGN_KEY mapping and was relying on the DDLs in postico. Had no IDEA that these FOREIGN_KEY mappings weren't being rendered, ultimately used this stackoverflow: how-to-list-table-foreign-keys article. I've unraveled the situation, but this was truly painful that there is no indication/hint in the Structure or DDL tab(s).

[olivierlacan]

I'll add my two cents here since I'm not able to see simple foreign keys in either the table structure or DLL tabs for a table.

Thankfully it's possible to retrieve them with the following query, which could be leveraged for this feature:

SELECT * FROM information_schema.key_column_usage
WHERE constraint_catalog=current_catalog 
AND table_name='your_table_name'
AND position_in_unique_constraint NOTNULL;

Thanks for your consideration, and thanks again for Postico. 😃

[maxpain]

Any updates?