In this SO answer: PostgreSQL: default constraint names
It would appear that my foreign key (not named at time of table creation) should have defaulted to the suffix _fkey
...
But it seems to have instead defaulted to the suffix _foreign
instead. Is this normal?
I am worried about creating a migration where I assume the constraint name and it works in my environment and then breaks everywhere else...
Is there way to ensure I reference the constraint correctly in any environment?
posgres version: 16.4
client that created the key: knex
The complete FK command used: table.foreign('userId').references('users.id')
psql with \d:
I'm sorry but I am not allowed to post code from our codebase, so here it is with redactions:
Indexes:
"***_pkey" PRIMARY KEY, btree (id)
"***" UNIQUE CONSTRAINT, btree ("***Id", "***Id")
Foreign-key constraints:
"***_***id_foreign" FOREIGN KEY ("***Id") REFERENCES ***(id)
"***_userid_foreign" FOREIGN KEY ("userId") REFERENCES users(id) <=== the column of interest
https://knexjs.org/guide/schema-builder.html#foreign shows the syntax:
table.foreign(columns, [foreignKeyName])
[.onDelete(statement).onUpdate(statement).withKeyName(foreignKeyName).deferrable(type)]
This appears to show an optional method withKeyName()
you can use to specify the constraint name.
I'm not a user of knex, so I'm not going to test this. Give it a try!
Edit: If you don't specify the constraint name, knex chooses one, and it may not be the same as PostgreSQL's own default. That is, the knex framework does specify a constraint name, whether it's one you specify or not.
You noticed the documentation states this:
A default key name using the columns is used unless foreignKeyName is specified.