Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Allow explicitly named primary keys, foreign keys, and indices #1355

Closed
timc13 opened this issue Dec 18, 2017 · 24 comments · Fixed by #8900
Closed

Allow explicitly named primary keys, foreign keys, and indices #1355

timc13 opened this issue Dec 18, 2017 · 24 comments · Fixed by #8900

Comments

@timc13
Copy link

timc13 commented Dec 18, 2017

a constraintName option on @Index, @PrimaryGeneratedColumn, and @JoinColumn would be nice.

example:

@Entity()
class Post {
  @PrimaryGeneratedColumn({ 
    constraintName: 'pk_post_id' 
  })
  id: number

  @ManyToOne(author => Author, author => author.posts)
  @JoinColumn({ 
    constraintName: 'fk_post_author_id'
  })
  author: Author
}
@pleerock pleerock added this to the 0.2.0 milestone Mar 29, 2018
@AlexMesser AlexMesser modified the milestones: 0.2.0, 0.3.0 Mar 31, 2018
pleerock pushed a commit that referenced this issue Sep 13, 2019
…ign key name (#4274)

Feature allows to use foreignkey metadata to generate database foreignkey names.

Closes #3847 and #1355
haggholm added a commit to haggholm/typeorm that referenced this issue Sep 15, 2019
commit 7623e890609610c1ea8cc6f98f70556267d8a8c6
Merge: 038c2edd 13ac222
Author: Petter Häggholm <petter@petterhaggholm.net>
Date:   Sat Sep 14 17:54:03 2019 -0700

    bugfix

commit 038c2edd31def7860e46e8f3c92cfc88d7e66c61
Author: Petter Häggholm <petter@petterhaggholm.net>
Date:   Sat Sep 14 14:22:35 2019 -0700

    fix locking

commit 13ac222
Author: Umed Khudoiberdiev <pleerock.me@gmail.com>
Date:   Fri Sep 13 23:20:36 2019 +0500

    updated changelog

commit ae1f131
Author: Umed Khudoiberdiev <pleerock.me@gmail.com>
Date:   Fri Sep 13 21:22:40 2019 +0500

    version bump

commit d8f1c81
Author: Roman <52884896+roman-acumen@users.noreply.github.com>
Date:   Fri Sep 13 18:12:23 2019 +0300

    fix: sqlite connections don't ignore the schema property (typeorm#4599)

commit 0094f61
Author: Dennie de Lange <dennie@tkvw.nl>
Date:   Fri Sep 13 16:58:20 2019 +0200

    feat: add referenced table metadata to NamingStrategy to resolve foreign key name (typeorm#4274)

    Feature allows to use foreignkey metadata to generate database foreignkey names.

    Closes typeorm#3847 and typeorm#1355

commit 3abe5b9
Author: Toby Hinloopen <toby@bonaroo.nl>
Date:   Fri Sep 13 16:04:12 2019 +0200

    fix: "hstore injection" & properly handle NULL, empty string, backslashes & quotes in hstore key/value pairs (typeorm#4720)

    * Improve HStore object support

    * Add hstore-injection test

commit 644c21b
Author: Muma David Bwalya <davidtheprogrammer42@gmail.com>
Date:   Fri Sep 13 12:32:22 2019 +0200

    docs: explicitly defining the optional nature of the ManyToOne and OneToMany relationship when viewed from the child entity (typeorm#4722)

    * Update many-to-one-one-to-many-relations.md

    Explicitly defining the optional nature of the ManyToOne and OneToMany relationship when viewed from the child entity.

    * Update docs/many-to-one-one-to-many-relations.md

    Punctuation and more accurate semantics.

    Co-Authored-By: Toby Hinloopen <toby@bonaroo.nl>

commit c52b3d2
Author: Evgeniy <evgesh1918@gmail.com>
Date:   Fri Sep 13 13:04:11 2019 +0300

    fix: views generating broken Migrations (typeorm#4726)

    MigrationGenerateCommand didn't apply parameters to query and information about ViewTables couldn't be inserted into typeorm_metadata table. Also added code that creates typeorm_metadata table if ViewTables exists

    Fixed issue typeorm#4123

commit 1d73a90
Author: Ian Mobley <ianmobley@gmail.com>
Date:   Fri Sep 13 00:22:17 2019 -0700

    fix: createQueryBuilder relation remove works only if using ID (typeorm#2632) (typeorm#4734)

commit 81f4b43
Author: Alex Howard <thezanke@gmail.com>
Date:   Fri Sep 13 03:16:07 2019 -0400

    docs: added missing comma in relations.md (typeorm#4739)

commit 7808bba
Author: Junggun Lim <junggun.lim@gmail.com>
Date:   Thu Sep 5 12:44:25 2019 -0700

    feat: UpdateResult returns affected rows in postgresql (typeorm#4432)

    * Added 'affected' field in UpdateResult as well as in DeleteResult.
     * PostgresQueryRunner returns the number of affected rows properly
     * UpdateQueryBuilder retrieves the affected rows returned by PostgresQueryRunner and
       sets the added 'affected' field of UpdateResult properly.

    Closes: typeorm#1308

commit 7a0beed
Author: Mophy Xiong <mophy.xh@gmail.com>
Date:   Fri Sep 6 03:33:39 2019 +0800

    fix: the excessive stack depth comparing types `FindConditions<?>` and `FindConditions<?>` problem (typeorm#4470)

commit dacac83
Author: Michał Wadas <michalwadas@gmail.com>
Date:   Thu Sep 5 21:28:56 2019 +0200

    feat: add materialized View support for Postgres (typeorm#4478)

    feat: add option to synchronize or not to synchronize ViewEntity

    Fixes typeorm#4317
    Fixes  typeorm#3996

commit db8074a
Author: Nicolas Hervé <nicolas.herve.pessac@gmail.com>
Date:   Thu Sep 5 19:42:52 2019 +0200

    feat: add support for ON CONFLICT for cockroach (typeorm#4518)

    Closes: typeorm#4513

commit 19e2179
Author: hauau <hauau@users.noreply.github.com>
Date:   Fri Sep 6 00:38:54 2019 +0700

    feat: add `set` datatype support for MySQL/MariaDB (typeorm#4538)

    Set possible values defined using existing enum column option.
    Sets are implemented as arrays.

    Closes: typeorm#2779

commit 5c311ed
Author: David Chen <42685381+DavidChen-minted@users.noreply.github.com>
Date:   Thu Sep 5 08:50:10 2019 -0700

    feat: add options to input init config for sql.js (typeorm#4560)

    * add options to input init config for sql.js

    * update changelog

    * updated connection-options docs

commit 3cf470d
Author: Aviad Hadad <aviadhadad@gmail.com>
Date:   Thu Sep 5 18:46:19 2019 +0300

    fix: change PrimaryColumn decorator to clone passed options (typeorm#4571)

    Closes: typeorm#4570

commit 9e3d664
Author: Mike Guida <mike@mguida.com>
Date:   Thu Sep 5 08:38:41 2019 -0700

    docs: clarify title of configuration sources (typeorm#4592)

commit 587d534
Author: Thomas Gieling <thomas.gieling@gmail.com>
Date:   Thu Sep 5 17:37:43 2019 +0200

    use remove in stead of delete (typeorm#4574)

    the `remove` method requires the entity itself as input. To remove a record based on it's id, you need to use `delete`

commit a925be9
Author: Michał Wadas <michalwadas@gmail.com>
Date:   Thu Sep 5 16:05:23 2019 +0200

    feat: add postgres pool error handler (typeorm#4474)

    Add option to customize pool error handling. Users can decide to log these errors with higher level (eg. error), crash application or reconnect.

commit f65ecc7
Author: JB Reefer <james.reefer@gmail.com>
Date:   Thu Sep 5 09:10:10 2019 -0400

    docs: grammar and wording fixes in active-record-data-mapper.md (typeorm#4615)

    Grammar and wording fixes in bottom paragraph

commit 445c740
Author: Duckie <7842848+duckies@users.noreply.github.com>
Date:   Thu Sep 5 08:59:50 2019 -0400

    fix: apostrophe in Postgres enum strings breaks query (typeorm#4631)

    * Patch to allow apostrophes in postgres enum string.

    * Make linter happy

    * Testing fix for MySQL

    * Limit to postgres and mysql, fix test description

    * Lets not be greedy.

commit c1406bb
Author: kevindashgit <friedheim.kevin@gmail.com>
Date:   Thu Sep 5 05:30:24 2019 -0700

    docs: documentation for showMigrations() (typeorm#4644)

commit 2478198
Author: azxj <31400828+azxj@users.noreply.github.com>
Date:   Thu Sep 5 20:29:23 2019 +0800

    docs: remove duplicate segment in entities.md (typeorm#4638)

    Remove duplicate segment.

commit 5bd29d5
Author: QoVoQ <674263588@qq.com>
Date:   Thu Sep 5 20:27:30 2019 +0800

    docs: update many-to-many-relations.md, make it easier to understand (typeorm#4680)

    * Update many-to-many-relations.md

    Make the example of the last section `many-to-many relations with custom properties` more clear and general.

    * Update many-to-many-relations.md

commit 92e4270
Author: Coroliov Oleg <1880059+ruscon@users.noreply.github.com>
Date:   Thu Sep 5 15:25:06 2019 +0300

    feat: add mongodb `useUnifiedTopology` config parameter (typeorm#4684)

commit 690e6f5
Author: CavidM <cavid2409@gmail.com>
Date:   Thu Sep 5 05:18:03 2019 -0700

    fix: "database" option error in driver when use "url" option for connection

commit e589fda
Author: Michael Dzjaparidze <michael.dzjaparidze@gmail.com>
Date:   Tue Sep 3 15:05:58 2019 +0200

    feat: export additional schema builder classes (typeorm#4325)

commit 3951b58
Author: David Podhola <david.podhola@naseukoly.cz>
Date:   Sat Aug 31 23:39:04 2019 +0200

    docs: fix missing async (typeorm#4458)

    In the second example the lambda is correctly started with an `async`. It is missing in the first example.

commit d4e2443
Author: Kateile <sylvanuskateile@gmail.com>
Date:   Sat Aug 31 21:26:44 2019 +0300

    docs: update typeorm-model-shim.js comment link (typeorm#4540)

    This link http://webpack.github.io/docs/configuration.html#resolve no longer exist

commit 4c2bffc
Author: dependabot[bot] <49699333+dependabot[bot]@users.noreply.github.com>
Date:   Fri Aug 30 23:43:04 2019 +0200

    chore: bump lodash from 4.17.11 to 4.17.15 (typeorm#4671)

    Bumps [lodash](https://github.com/lodash/lodash) from 4.17.11 to 4.17.15.
    - [Release notes](https://github.com/lodash/lodash/releases)
    - [Commits](lodash/lodash@4.17.11...4.17.15)

    Signed-off-by: dependabot[bot] <support@github.com>

commit 10bac1f
Author: dependabot[bot] <49699333+dependabot[bot]@users.noreply.github.com>
Date:   Fri Aug 30 23:35:40 2019 +0200

    build: bump mixin-deep from 1.3.1 to 1.3.2 (typeorm#4648)

    Bumps [mixin-deep](https://github.com/jonschlinkert/mixin-deep) from 1.3.1 to 1.3.2.
    - [Release notes](https://github.com/jonschlinkert/mixin-deep/releases)
    - [Commits](jonschlinkert/mixin-deep@1.3.1...1.3.2)

    Signed-off-by: dependabot[bot] <support@github.com>

commit e81a77a
Author: dependabot[bot] <49699333+dependabot[bot]@users.noreply.github.com>
Date:   Fri Aug 30 23:33:36 2019 +0200

    chore: bump lodash.template from 4.4.0 to 4.5.0 (typeorm#4416)

    Bumps [lodash.template](https://github.com/lodash/lodash) from 4.4.0 to 4.5.0.
    - [Release notes](https://github.com/lodash/lodash/releases)
    - [Commits](lodash/lodash@4.4.0...4.5.0)

    Signed-off-by: dependabot[bot] <support@github.com>

commit 00d46e1
Author: JB Reefer <james.reefer@gmail.com>
Date:   Sat Aug 24 05:08:26 2019 -0400

    docs: Update using-cli.md (typeorm#4618)

    Fix typo

commit d1594f5
Author: Abhijeet Chakraborty <16278759+abhijeet1403@users.noreply.github.com>
Date:   Wed Aug 14 14:01:48 2019 +0530

    fix: resolve issue with conversion string to simple-json (typeorm#4476)

    Closes: typeorm#4440

commit c321562
Author: Arseny Yankovsky <ArsenyYankovsky@users.noreply.github.com>
Date:   Tue Aug 13 10:12:25 2019 +0200

    feat: Aurora Data API (typeorm#4375)

    * Initial POC Implementation

    * Initial POC Implementation

    * Implemented an interface transformation between typeorm and data api so most of the queries should work, added some tests

    * Fixed lint errors

    * Fixed a regex and added some tests on query transformation

    * Move out to a separate repo

    * Bumped aurora driver version to latest

    * Bumped aurora driver version to latest

    * Delegate transactions to the driver

    * Delegate transactions to the driver

    * WIP

    * WIP

    * Bump the aurora driver version

    * Bump the aurora driver version

    * Fixed aurora driver version

    * removed unused entity

commit b4e9cf0
Author: Marcos <holamarcosaurios@gmail.com>
Date:   Wed Aug 7 11:35:11 2019 +0200

    docs: update typo in separating-entity-definition.md

    Typo in line 190: "Categeory" changed to "Category"

commit 9930283
Author: Charlie Cruzan <35579283+cruzach@users.noreply.github.com>
Date:   Wed Aug 7 05:33:42 2019 -0400

    docs: update outdated Expo SQLite link

    * update Expo SQLite link

    * docs: fix expo-example repo link

commit 5e00e81
Author: Humberto Villalpando <43765965+betov18x@users.noreply.github.com>
Date:   Wed Jul 31 06:47:36 2019 -0500

    chore: Update README.md

    Added some links for nativescript examples with vue and angular

commit 10a5182
Author: markamPL <markam.tg@gmail.com>
Date:   Wed Jul 24 09:00:47 2019 +0200

    docs: update typo in using-ormconfig.md

    Typo in .env configuration: .*js changed to *.js

commit b6d6278
Author: Liau Jian Jie <liaujianjie@gmail.com>
Date:   Sun Jul 21 22:10:53 2019 +0800

    feat: add multi-dimensional cube support for PostgreSQL (typeorm#4378)

commit e12479e
Author: Abdoulaye K. Traoré <abdoulayekt@hotmail.com>
Date:   Sat Jul 13 18:57:16 2019 +0200

    feat: log files loaded from glob patterns (typeorm#4346)

    This new feature logs the files that are loaded using the glob patterns to aid in debugging.

    Closes: typeorm#4162

commit c8dbf09
Author: Mike Harris <mharris717@gmail.com>
Date:   Wed Jul 10 09:17:31 2019 -0400

    docs: Fix Typo (typeorm#4412)

commit a858de1
Author: Vlad Poluch <vlad.poluch@gmail.com>
Date:   Mon Jul 1 16:01:09 2019 +0200

    Revert "docs: fix typographical error in faq (typeorm#4321)" (typeorm#4380)

    This reverts commit 117185b.

commit 39a8e34
Author: Benjamin Dobell <benjamin.dobell+github@glassechidna.com.au>
Date:   Mon Jul 1 01:18:55 2019 +1000

    feat: Added support for DISTINCT queries (typeorm#4109)

commit c8a9ea0
Author: Max Sommer <kontakt@maxsommer.de>
Date:   Sun Jun 30 17:12:07 2019 +0200

    docs: add explanation ManyToMany with custom properties (typeorm#4308)

    * Add explanation ManyToMany with custom properties

    Since I myself ran into the issue and had to rewrite my code based on this solution I hope it may be helpful for others as well 😊

    * Update explanation with more relateable example

    Update explanation with abstract example to be more relateable
    Add cross link to faq

    * Update phrasing, unnecessary explanation and code

    Remove unnecessary explanation upfront mentioning FAQ
    Update phrasing of main explanatory paragraph to be more precise
    Update code example to work correctly

commit 79bf9f7
Author: Kononnable <kononnable@gmail.com>
Date:   Sun Jun 30 17:11:06 2019 +0200

    build: node version upgrade in travis (typeorm#4312)

    * upgrading node CI tested versions

    * sqlite3 version upgrade

commit a6d7ba2
Author: Leonardo Falk <leonardofalk@users.noreply.github.com>
Date:   Sun Jun 30 12:07:19 2019 -0300

    fix: add SaveOptions and RemoveOptions into ActiveRecord (typeorm#4318)

    * Adding SaveOptions to BaseEntity#save

    * Adding RemoveOptions to BaseEntity#remove

commit 684ffd1
Merge: 6429ccd 6a1206e
Author: Umed Khudoiberdiev <pleerock.me@gmail.com>
Date:   Sun Jun 30 14:39:21 2019 +0300

    Merge pull request typeorm#4376 from typeorm/revert-4306-fix-4291

    Revert "fix: improve sql.js v1.0 support in browser environment"

commit 6a1206e
Author: Umed Khudoiberdiev <pleerock.me@gmail.com>
Date:   Sun Jun 30 14:38:54 2019 +0300

    Revert "fix: improve sql.js v1.0 support in browser environment"

commit 6429ccd
Merge: 117185b ed87e34
Author: Umed Khudoiberdiev <pleerock.me@gmail.com>
Date:   Sun Jun 30 14:00:46 2019 +0300

    Merge pull request typeorm#4306 from michaelbromley/fix-4291

    fix: improve sql.js v1.0 support in browser environment

commit 117185b
Author: SriNath <12288245+SrChip15@users.noreply.github.com>
Date:   Wed Jun 19 09:13:47 2019 -0400

    docs: fix typographical error in faq (typeorm#4321)

    change "typedi" to "typed" in how to use TypeORM with a dependency injection tool question

commit ed87e34
Author: Michael Bromley <michael@michaelbromley.co.uk>
Date:   Tue Jun 18 10:25:35 2019 +0200

    Add more documentation on using sql.js in the browser

commit f7bcd8f
Author: Michael Bromley <michael@michaelbromley.co.uk>
Date:   Tue Jun 18 10:05:04 2019 +0200

    Fix sql.js v1.0 support in browser
@jsuryahyd
Copy link

So, is there no way to specify a foreign key constraint_name directly? I could not find anything in the documentation. My use case is to find which foreign key constraint fails by checking mysqlErr.sqlMessage and send specific error messages.

@vegerot

This comment has been minimized.

@vegerot
Copy link
Contributor

vegerot commented Mar 17, 2020

If having unique names is a must, then what about something similar to what you have for migrations: where there is a unique id generated in the front, then a custom name can be given after? Best of both worlds

@hugo-dutra
Copy link

I Saw it on documentation.

@manytoone(type => Category)
@joincolumn({ name: "cat_id" })
category: Category;

@wodka
Copy link
Contributor

wodka commented May 14, 2020

@hugo-dutra this is for the field name, not the name of the foreign key itself

@hugo-dutra
Copy link

hugo-dutra commented May 14, 2020

@hugo-dutra this is for the field name, not the name of the foreign key itself

Strange, I had the impression of seeing a foreign key with the same name created in the database. And a field with that name created in the table with the name of the foreign key. But, thanks for the comment. Thank you.

https://github.com/typeorm/typeorm/blob/master/docs/relations.md#joincolumn-options

@mirkadev
Copy link

Well, it seems I need this feature as well...

@hvpaiva
Copy link

hvpaiva commented Jun 2, 2020

I need as well.
I already can see my DBA complaining...

@wenerme
Copy link

wenerme commented Jun 24, 2020

I need this as well when I see the error message

QueryFailedError: constraint "PK_0669fe20e252eb692bf4d344975" of relation "settings" does not exist

Who knows what's the meaning of PK_0669fe20e252eb692bf4d344975 ?

@amirh715
Copy link

I can't believe I don't have this option. Almost 3 years have passed! Any response?! :-/

@juanjalvarez
Copy link

Can't use auto-migrations for my use-case, which means the FK constraints expected by TypeORM won't match up with what I called them, which means no lazy loading :(

@kaykhancheckpoint
Copy link

kaykhancheckpoint commented Jul 17, 2020

For those struggling with this, it is possible to explicitly set the name of FK constraints by building a naming strategy. If you look at the DefaultNamingStrategy interface it also allows you to set others such as primaryKeyName.

CustomNamingStrategy

The following naming convention is used below: <table1>_<table2>_<columns> then i hash this because it could get long and mysql will throw an identifier too long error.

This method makes the foreign keys deterministic so that i can use the same migrations for both test and production db.

import { DefaultNamingStrategy, Table, NamingStrategyInterface } from "typeorm";
import crypto from "crypto";

export class CustomNamingStrategy extends DefaultNamingStrategy implements NamingStrategyInterface {
    foreignKeyName(tableOrName: Table | string, columnNames: string[], referencedTablePath?: string, referencedColumnNames?: string[]): string {
        
        tableOrName =
            typeof tableOrName === "string" ? tableOrName : tableOrName.name;

        const name = columnNames.reduce(
            (name, column) => `${name}_${column}`,
            `${tableOrName}_${referencedTablePath}`,
        );

        return`fk_${crypto.createHash('md5').update(name).digest("hex")}`
    }
}

ormconfig.ts

Then import the custom naming strategy in your config.

module.exports = {
    "type": "mysql",
    "host": process.env.DB_HOST,
    ...
    namingStrategy: new CustomNamingStrategy()
}

references:

https://github.com/typeorm/typeorm/tree/master/sample/sample12-custom-naming-strategy
#5286
https://github.com/typeorm/typeorm/pull/4274/files

@EPecherkin
Copy link

Guys. I think this issue is very close to being Crucial.
When you get insert or update on table "..." violates foreign key constraint "FK_13d991343c840a2afe2774e0f01 - it is such a bad message that I have a question: who thought it is a good idea to keep it like this?
And it is in that way for 4(!) years...

@eporomaa
Copy link

eporomaa commented Oct 29, 2021

@EPecherkin why is that so bad? You can just look up what look up FK_... in your DB and check what it is. Or even better check the error logs of your database.

@xaviermarchal
Copy link

For people being J in their MBTI profile, i guess being able to name the constraints is the MUST.

And according to me, we should be able to name the constraints the way we want, and easily.

@polosatyi
Copy link

I need this feature as well.

M-TGH added a commit to TradeCast/typeorm that referenced this issue Jan 12, 2022
Add a constraintName to JoinColumn decorators to allow specifying foreignKey name.
Use constraintName when building JoinTable entities as well.

Partially solves: typeorm#1355
M-TGH added a commit to TradeCast/typeorm that referenced this issue Jan 17, 2022
Add a constraintName to JoinColumn decorators to allow specifying foreignKey name.
Use constraintName when building JoinTable entities as well.

Partially solves: typeorm#1355
@jclab-joseph
Copy link

Any progress on this issue?
I ran into this problem while migrating to TypeORM from other framework.

M-TGH added a commit to TradeCast/typeorm that referenced this issue Mar 29, 2022
Add a constraintName to JoinColumn decorators to allow specifying foreignKey name.
Use constraintName when building JoinTable entities as well.

Partially solves: typeorm#1355
M-TGH added a commit to TradeCast/typeorm that referenced this issue Mar 29, 2022
Add a constraintName to JoinColumn decorators to allow specifying foreignKey name.
Use constraintName when building JoinTable entities as well.

Partially solves: typeorm#1355
AlexMesser added a commit that referenced this issue Apr 29, 2022
…8900)

* feat: add constraintName to JoinColumn

Add a constraintName to JoinColumn decorators to allow specifying foreignKey name.
Use constraintName when building JoinTable entities as well.

Partially solves: #1355

* test: add tests for constraintNames on JoinColumn

* docs: add constraintName documentation to JoinColumn and JoinTable

* test: update snapshot in 5444 test

Add constraintName property with correct variable undefined to snapshot in tests for issue 5444.

* prettier

* added support for custom FK name in Sqlite;
added test;

* removed .only

* fixed FK constraint renaming on table/column rename

* minor fix

* fixed @unique and @Index constraints renaming on table/column rename

* working on constraint name support for PK

* replaced `constraintName` with `primaryKeyConstraintName` and `foreignKeyConstraintName`

* fixed failing test

* working on constraint name support for PK

* updated docs

Co-authored-by: Matthijs Hatzmann <matthijs.hatzmann@tradecast.eu>
@CemYil03
Copy link
Contributor

CemYil03 commented Jun 27, 2022

I encountered a problem, where one column should be used for two foreign key references on different tables. TypeORM seems to name them the same which gives me a duplicate foreign key name error.
#5289

hilyafadhilah added a commit to hilyafadhilah/bnmofin-api that referenced this issue Jul 14, 2022
@bertyhell
Copy link

bertyhell commented Jan 8, 2023

Wouldn't a lot of this issue be resolved by a better implementation of the DefaultNamingStrategy.foreignKeyName function so it is more human readable?

eg:
current implementation produces: FK_0669fe20e252eb692bf4d344975
https://github.com/typeorm/typeorm/blob/master/src/naming-strategy/DefaultNamingStrategy.ts#L105-L118

    foreignKeyName(
        tableOrName: Table | string,
        columnNames: string[],
        _referencedTablePath?: string,
        _referencedColumnNames?: string[],
    ): string {
        // sort incoming column names to avoid issue when ["id", "name"] and ["name", "id"] arrays
        const clonedColumnNames = [...columnNames]
        clonedColumnNames.sort()
        const tableName = this.getTableName(tableOrName)
        const replacedTableName = tableName.replace(".", "_")
        const key = `${replacedTableName}_${clonedColumnNames.join("_")}`
        return "FK_" + RandomGenerator.sha1(key).substr(0, 27)
    }

If we changed it to this, it would produce something like: FK_post_author_id_0669fe20e252eb692bf4d344975

    foreignKeyName(
        tableOrName: Table | string,
        columnNames: string[],
        _referencedTablePath?: string,
        _referencedColumnNames?: string[],
    ): string {
        // sort incoming column names to avoid issue when ["id", "name"] and ["name", "id"] arrays
        const clonedColumnNames = [...columnNames]
        clonedColumnNames.sort()
        const tableName = this.getTableName(tableOrName)
        const replacedTableName = tableName.replace(".", "_")

        const key = [replacedTableName, ...clonedColumnNames].join("_");
        const sha = RandomGenerator.sha1(key).substring(0, 27);

        return "FK_" + key + '_' + sha;
    }

The advantage being:

  • The developer can instantly see what foreign key it is without having to go lookup the sha hash in the database
  • most of the time there won't be a need for the developer to create a custom naming function
  • the uniqueness should be identical, since the original sha hash is still included in the name. Although it might even be possible to omit it. Not sure about this.

Disadvantages:

  • the foreign key names will be a lot longer. Especially if a lot of columns are used. Is there a max length in certain databases?
  • The names will not be the same as before this change. Which might mess up migrations for existing databases?

@vegerot
Copy link
Contributor

vegerot commented Jan 12, 2023

I love that idea!

Your disadvantages are both true and a real challenge. This would be incompatible AF :(

@not-steve-jobs
Copy link

For those struggling with this, it is possible to explicitly set the name of FK constraints by building a naming strategy. If you look at the DefaultNamingStrategy interface it also allows you to set others such as primaryKeyName.

CustomNamingStrategy

The following naming convention is used below: <table1>_<table2>_<columns> then i hash this because it could get long and mysql will throw an identifier too long error.

This method makes the foreign keys deterministic so that i can use the same migrations for both test and production db.

import { DefaultNamingStrategy, Table, NamingStrategyInterface } from "typeorm";
import crypto from "crypto";

export class CustomNamingStrategy extends DefaultNamingStrategy implements NamingStrategyInterface {
    foreignKeyName(tableOrName: Table | string, columnNames: string[], referencedTablePath?: string, referencedColumnNames?: string[]): string {
        
        tableOrName =
            typeof tableOrName === "string" ? tableOrName : tableOrName.name;

        const name = columnNames.reduce(
            (name, column) => `${name}_${column}`,
            `${tableOrName}_${referencedTablePath}`,
        );

        return`fk_${crypto.createHash('md5').update(name).digest("hex")}`
    }
}

ormconfig.ts

Then import the custom naming strategy in your config.

module.exports = {
    "type": "mysql",
    "host": process.env.DB_HOST,
    ...
    namingStrategy: new CustomNamingStrategy()
}

references:

https://github.com/typeorm/typeorm/tree/master/sample/sample12-custom-naming-strategy #5286 https://github.com/typeorm/typeorm/pull/4274/files

and how to write a function down for this? crypto.createHash('md5') we can't Repeat

@bertyhell
Copy link

bertyhell commented Jan 31, 2023

@not-steve-jobs

and how to write a function down for this? crypto.createHash('md5') we can't Repeat

Yes you can. if the name in the update call is identical in the up and the down migration, then it will generate the same hash:
https://replit.com/@BertVerhelst/SteepCompatibleInvocation#index.js

hashes always give you the exact same smoothy if you put in the same ingredients and press the same buttons on the hash blender :p

image

@pcnova
Copy link

pcnova commented May 2, 2023

hashes always give you the exact same smoothy [..]

@bertyhell: that is a great analogy, thanks for that! 👍🏼

@gabbrieu
Copy link

A one-to-one relation creates by default a unique index in mysql migration. How can i rename this index?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment