Re: postgresql generate ddl returns FK with `<?>()` in it

On Fri, 8 Jul 2022 at 12:37, Roman Gavrilov
<roman(dot)gavrilov(at)projectcanary(dot)com> wrote:
> I have a schema with two tables, where the first table's composite 2-columns FK points to the second table's 2-columns composite PK. But the order of columns is reversed.

Note sql does not look at the NAMES of the foreign key columns or the
primary key columns, it matches them by position.

Also note you will have a higher success probability in this kind of
question if you provide the table definitions postgres is using,
pg_dump with the
-s, --schema-only dump only the schema, no data
-t, --table=PATTERN dump the specified table(s) only
options is a simple way to get those.

You have not even cited which programs has this "generate ddl" command.

> When I use `Generate DDL` on the first table, FK definition comes back as:
> ALTER TABLE eval.rubric_questions ADD CONSTRAINT rubric_questions_fk FOREIGN KEY (rubric_id,rubric_version_id) REFERENCES <?>();
> How can I fix that `<?>();` issue? Is this documented behavior?
> (I realize that I need to fix the schema, but in time-being I need to generate code using entity framework, and it chokes in that).

Ah, you expect people to hunt your link to SO:
> Full details on SO:
> https://stackoverflow.com/questions/72898296/what-is-in-postgresql-ddl

This is frowned upon and lowers your success rate, AAMOF I've only
replied to this because I'm waiting for someone to get out to lunch
and burning some minutes, normally I would have just bitbucketed your
mail.

From the SO one can see you are using DBbeaver, If I copied it right.
This seems to be a bug in it, not in postgres. You can have a pk on
columns (a,b,c,d) and have an fk on columns (w,x,y,z), in fact it is
common, like in the classical tree (pk=id, fk=parent_id references
same table).

But anyway, be careful, postgres does not match columns in keys by
name, but by position, and from the little detail you provided in SO
it appears that aside from trigering a bug in dbbeaver your schema is
totally wrong and will malfunction. Post more details and someone may
be able to help you.

Regards.
Francisco Olarte.