On Fri, 8 Jul 2022 at 12:37, Roman Gavrilov <roman.gavrilov@xxxxxxxxxxxxxxxxx> 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.
