Sequelize: Composite foreign keys

[Zerzio]

Hi,

I found some posts about composite keys and relations but nothing is in the documentation and it doesn't seem to be supported (no relevant test cases)
Is this supported: Entity.hasMany(Other , {foreignKey: ['key1', ' key2', ' key3']});

Thanks,

Serge

[sdepold]

nope. i'm sorry :)

[Zerzio]

I'm too ;-)
I found those:

• https://github.com/innofluence/sequelize/tree/foreignKey
• https://github.com/cliftonc/sequelize/commit/0cccf2c77866e03df140b73085ad3f23ef77cbd1

Without it I guess I have to implement some instanceMethods flavour, right?
The custom get method would use attributes of the object to fetch associated entities.
Another suggestion before I start doing so?

[pilsy]

Hi i was wondering if the code mentioned by Zerzio has been merged in yet? i couldn't see it anywhere

[sdepold]

ahm. nope.

[janmeier]

The innofluence fork is me and mickhansen. But the support is not yet complete, and we have agree not to fully implement it sequelize fully supports non-id primary keys. That is sometime in 1.7

[Domiii]

Any updates on this issue yet? I currently need support for composite foreign keys. I guess, the best alternative is to simply impose a unique index on the composite key and use an auto-increment primary key instead?

[mickhansen]

@Domiii No work has been done as of yet. That is your best alternative yes, personally i think that's the best solution always but that's just me.

[Domiii]

In that case, let me share my index-creating code here for other people running into the same problem. I only tested this with MySQL. It might need some fixing for other SQL formats.

Example usage:

var defaultOptions = {};
// ....
createIndexIfNotExists('some_table', ['column1', 'column2, ...], { indexOptions: 'UNIQUE'}, function(err) { if (err) ... else ... });

Code:

function createIndexIfNotExists(table, columns, options, cb) {
    if (typeof columns === 'string') columns = [columns];
    var indexName = (options && options.indexName) || table + '_' + columns.join('_');

    // check if index exists
    // see: http://stackoverflow.com/questions/2480148/how-can-i-employ-if-exists-for-creating-or-dropping-an-index-in-mysql
    var checkIndexSql = 'SHOW INDEX FROM ' + table + ' WHERE KEY_NAME = \'' + indexName + '\'';
    sequelize.query(checkIndexSql, null, defaultOptions).complete(function(err, res) {
        if (err) {
            cb(new Error("Unable to query index for table `" + table + "` - " + err.message));
            return;
        }
        if (res.length == 0) {
            // index does not exist yet

            // disable logging
            if (options) options.logging = nothing;
            else options = defaultOptions;

            // add index
            // see: https://github.com/sequelize/sequelize/blob/eb034333160867cf21251e51a08057985e1a5c77/lib/dialects/mysql/query-generator.js#L259
            var sql = "CREATE " + (options.indexOptions || "") + " INDEX " + indexName + 
                (options.indexType ? (' USING ' + options.indexType) : "") +
                " ON " + table + ' (' + columns.join(', ') + ')' +
                (options.parser ? " WITH PARSER " + options.parser : "");

            sequelize.query(sql, null, defaultOptions).complete(function(err) {
                if (err) {
                    cb(new Error("Unable to create index for table `" + table + "` - " + err.message));
                }
                else {
                    cb();
                }
            });
        }
        else {
            cb();
        }
    });
}

[kopertop]

I just ran into this problem. Is there still no plan to support multi-column indexes on relations? Is there any workaround known other then using a single key index?

Specifically, I'm trying to use the "findAll" with an "include" filter.

[mickhansen]

@kopertop There's a difference between indexes and constraints, we support multi column indexes on all tables.

[ivanjd]

That feature would be super helpful.

[jamesaspence]

+1 I just ran into this as well, would make my job way easier. For now I guess I'm editing the table structure :/

[ozum]

:+1:

[mickhansen]

For everyone +1'ing this, are you looking to reference tables with multiple primary keys or what are the usecases here exacttly?

[cdupetit]

I want to make association like hasMany, hasOne, belongsTo between tables with multiple primary keys.
Example:

Site.hasMany(Server, { primaryKey: [siteId] });
Server.hasMany(Share, { primaryKey: [siteId, serverId] });
Share.hasMany(Folder, { primaryKey: [siteId, serverId, shareId] });
Folder.hasOne(Share, { primaryKey: [siteId, serverId, shareId] });
Share.hasOne(Server, { primaryKey: [siteId, serverId] });
Server.hasOne(Site, { primaryKey: [siteId] });

SELECT
    SITE_NAME           AS Site.site_name,
    DNS_NAME            AS Server.dns_name,
    CIFS_NAME           AS Share.cifs_name,
    PATH                AS Folder.path,
FROM            FOLDERS AS Folder
LEFT OUTER JOIN SITES   AS Site ON  File.SITE_ID = Site.SITE_ID
LEFT OUTER JOIN SERVERS AS Server ON  File.SITE_ID = Server.SITE_ID AND File.SERVER_ID = Server.SERVER_ID
LEFT OUTER JOIN SHARES  AS Share ON  File.SITE_ID = Share.SITE_ID AND                                                 File.SERVER_ID = Share.SERVER_ID AND File.SHARE_ID = Share.SHARE_ID;

[ozum]

Hi,

I'm developer of pg-generator which has a built-in sequelize template to generate sequelize code for PostgreSQL.

My use case is;
Sometimes, I have tables with composite primary keys (multi column PK), which I can not use surrogate keys without breaking normalization.

Below is a very simplified example (It is not easy to draw a diagram with ASCII)

account                                   action
-------                                   --------
(PK FK) company_id    -------------<      (PK) id
(PK FK) person_id                         (FK) company_id
                                          (FK) person_id

Account has many Actions which should joined like this:

SELECT *
FROM account
INNER JOIN action ON
    (action.company_id = account.company_id AND action.person_id = account.person_id)

Since I cannot relate tables with multiple primary keys properly as far as I know with sequelize, I cannot use sequelize on some projects.

[mickhansen]

@cdupetit Not possible to define primaryKey on associations like that. But in any case, Folder has a composite primary key across siteId, serverId, shareId and Server has a primary key across siteId & serverId?

Composite primary key support is lacking in sequelize too so that would need to be fixed properly first.

[ozum]

@mickhansen. My +1 is for composite foreign key, which requires implicitly composite primary key support. Sequelize is already a great library and IMHO it should support multiple keys which is a fundamental part of database design.

[mickhansen]

@ozum Personally never used composite primary keys in my life (age old habit of having id on everything).

We'd definitely love to have PRs to improve support here. Seeing as we keep getting bumps in here it would be great to be able to dedicate some time to it.

[ozum]

@mickhansen Actually I agree with you about hesitating about using composite primary keys, because DB design becomes complex for future me and it gets harder to explain it to non DB expert developers. However sometimes it is unavoidable, if you want normalized DB design, especially if there are so called diamond shaped relations between tables.

[mickhansen]

@ozum Makes sense.

[eighteight]

My app is critically dependent on this feature. Knowing a timeline/feasibility of its implementation would help enormously. Thanks.

[mickhansen]

@eighteight It's certainly feasible, can't give you a timeline though. Developers do what they can when they can :)

I hope to take a look at this feature early in the new year though.

[mickhansen]

@cdupetit Your example assumes that the primary keys are named the same as the foreign keys, that's not an assumption we make at the moment. Not sure what syntax would look like to support multiple foreignkey -> primarykey matches.

[apsavin]

+1, need this for "subtypes" tables.

[bjrmatos]

We'd definitely love to have PRs to improve support here. Seeing as we keep getting bumps in here it would be great to be able to dedicate some time to it.

hi! @mickhansen @janmeier any feedback that you can give me about this? we need this feature and we would love to PR this. i know that there are many cases here, i will start with the belongsTo/hasOne association, how would you like to have this implemented? how the API should look like? An array seems right foreignKey: ['key1', ' key2', ' key3'] and in case of the belongsTo with targetKey -> foreignKey: ['key1', ' key2', ' key3'], targetKey: ['targetKey1ToMatchWithKey1', 'targetKey2ToMatchWithKey2', 'targetKey3ToMatchWithKey3']

toughts?

[mickhansen]

@bjrmatos A set of arrays where indexes must be equal is a way to go, we could also add a new {[foreignKey]: [targetKey]} matching syntax, although that might conflict with the API of supporting an options object as foreign keys at the moment.

[bjrmatos]

@mickhansen thnks i'll start with a set of arrays and then we can see how to add a new matching syntax, or maybe another option for the matching

[feardarkness]

Is there any news?

[atulhm]

I'd be happy to add my time/support to digging into this as well. when i add hasmany associations, the query to get the children fails wiithout a joining table existing. Still digging into this. If anyone wants to collaborate, let me know.

[atulhm]

I'm still working on the api but here is something I think works for the hasMany case:

SalesOrder.hasMany(SaleOrderItems).on(['customer_id','sales_order_id'])

results in

salesorder.customer_id = saleorderitems.customer_id AND
salesorder.sales_order_id = saleorderitems.sales_order_id

Where the column names don't match

SalesOrder.hasMany(SaleOrderItems).on([{'customer_id':'account_id'},'sales_order_id'])

results in

salesorder.customer_id = saleorderitems.account_id AND
salesorder.sales_order_id = saleorderitems.sales_order_id

What do you guys think?

[janmeier]

@atulhm That API actually looks rather pretty, but personally I'd rather stick to the foreignKey / otherKey / targetKey paradigm we have now, so users don't have to adjust too much

[mickhansen]

Composite foreign keys obviously require defining a target key for each (unless they're the same name, but i'm not sure that's a safe assumption).

So i've been considering something like.

hasMany(Target, {
  foreignKey: {
    key1: 'id1',
    key2: 'id2'
  }
});

Which would also work for single foreign key definitions

[atulhm]

Nice. I’m good with either way. Like the fluent stuff I’ve been seeing lately so was suggesting it. Now we just got to implement. Should get some time later this week to look at it.

On May 10, 2016, at 12:33 AM, Mick Hansen [email protected] wrote:

Composite foreign keys obviously require defining a target key for each (unless they're the same name, but i'm not sure that's a safe assumption).

So i've been considering something like.

hasMany(Target, {
foreignKey: {
key1: 'id1',
key2: 'id2'
}
});
Which would also work for single foreign key definitions

—
You are receiving this because you were mentioned.
Reply to this email directly or view it on GitHub https://github.com/sequelize/sequelize/issues/311#issuecomment-218080573

[vip30]

This sounds very nice if it have such function.
Please let us know the status Thanks!

[atulhm]

Just wanted to give you all an update. I'm afraid I've been yanked onto another project that has been taking up all my time. If someone wants to jump on this, please go ahead. I'll help where I can, but I can't take lead at the moment. :(

[devenv]

BTW, as a workaround if you use MySQL check out generated columns (a relatively new feature), it might work out for your case (it did for mine)...

[jdnichollsc]

I have the same issue with my database normalized :/ http://screencast.com/t/n3FE6fkhDfal

[janmeier]

Since a lot of people are +1'ing and asking for updates - No, noone is currently working on this, but we'd welcome contributions, and more input of API design

[lzerma]

Hey guys, any news on that?

I will make my words as @cdupetit said.

'I want to make association like hasMany, hasOne, belongsTo between tables with multiple primary...'

[felixfbecker]

@lzerma https://github.com/sequelize/sequelize/issues/311#issuecomment-240125269

[felixfbecker]

Please don't +1 issues. It clutters the thread without adding value to the discussion and spams maintainers with notifications. Use GitHub reactions to upvote features.

[abhishek97]

+1

[shtse8]

Any updates on this issue? I want to do the composite foreign keys, but I don't know how to do that in the latest release.

[idris]

I just realized (by trying it) that Sequelize supports composite primary keys. Knowing that, composite foreign keys should not be too difficult to implement, especially if they're explicitly specified on the association.

I'm probably going to take a stab at adding this functionality soon and will post here with updates.

[ivanlebed]

@idris any luck? need any help?

[idris]

Have not started in a significant way yet. Pinging @stevehollaar who is also working on this.

[stevehollaar]

This is working for 2-column composite foreign keys for BelongsTo, HasMany, and HasOne associations, in this branch: https://github.com/sequelize/sequelize/pull/8763
Would appreciate any feedback/suggestions

[muhammadumair-rauf]

This issue can be resolve using scopes in association. see the link below
http://docs.sequelizejs.com/manual/tutorial/associations.html#scopes

[manuelbertelli]

@muhammadumair-rauf really works! Thanks a lot! The issue can be closed I suppose 😄

Case.hasMany(models.Message, { foreignKey: 'caseId', scope: { projectId: { $col: 'Case.projectId' } } })

Turns into:
LEFT OUTER JOIN "Messages" AS "Messages" ON "Case"."caseId" = "Messages"."caseId" AND "Messages"."projectId" = "Case"."projectId"

[dalssoft]

Use case: Composite keys is super important for multi tenant SaaS where it's necessary to have a column (e.g customerId) in order to isolate each customer data. For instance, products like Citus (sharding for Postgres) rely on composite keys (one for customer and other for tables intention itself) to split the data among its nodes.
BTW, thank you @muhammadumair-rauf 👍

[idris]

@manuelbertelli does the scopes method work for every query type? Can it fully replace #8763?

[lbeschastny]

@manuelbertelli @idris and what about constraints? Are they handled correctly with scopes?
Can scopes handle n:m associations with composite keys?

[cdupetit]

In the sequelize documentation, scope is not an option of belongsTo function.
But if you set a scope option to belongsTo function the result is valid.
But the @types/sequelize didn't allow the scope option in the belongsTo function.
Can you change the belongsTo documentation, then the @types/sequelize will change to allow scope ?

[mhyassin]

Well, if I want to join based on an "OR", scopes wouldn't work, would it?!

I have 'Orders' that are related to different types of users, I just want to relate that table with the users table to get orders for each user.

With a simple join it would be
JOIN table_orders ON shop_id = user_id OR sales_id = user_id

I guess it can't be achieved with scopes

[ShaunParsons]

Scopes doesn't quite seem the ~correct~ ~logical~ user friendly way to go about this. I would favour the following because it is then consistent with the SQL declaration (in psql at least)

hasMany(Target, {
  foreignKeys: ['key1', 'key2', ....],
  targetKeys: ['id1', 'id2', ....]
});

as mentioned originally by @sushantdhiman on https://github.com/sequelize/sequelize/pull/8763

[abacha]

@manuelbertelli I cannot reproduce your behaviour:
I have 2 models: EquityValue and EquityCustodyTransfer, I want to create an association combining 2 fields (asset_code and reference_date)

Doing as you said:

   equity_custody_transfer.associate = function(models) {                         
     equity_custody_transfer.hasMany(                                             
       models.EquityValue,                                                        
       {                                                                          
         foreignKey: 'reference_date',                                            
          scope: { asset_code: { $col: 'EquityCustodyTransfer.asset_code' } }      
       }                                                                          
     ) 
   };   

I get:

"detail": "Invalid value { '$col': 'EquityCustodyTransfer.asset_code' }",

I've got a little closer using this:

   equity_custody_transfer.associate = function(models) {                         
     equity_custody_transfer.hasMany(models.EquityValue,                          
       {                                                                          
         constraints: false,                                                      
         foreignKey: 'reference_date',                                            
         sourceKey: 'reference_date',                                             
         scope: { asset_code: 'EquityCustodyTransfer.asset_code' }, 
       }                                                                          
     );                                                                           
   };   

SELECT * FROM `EQUITY_CUSTODY_TRANSFER` AS `EquityCustodyTransfer` 
LEFT OUTER JOIN `EQUITY_VALUE` AS `EquityValues` ON 
`EquityCustodyTransfer`.`reference_date` = `EquityValues`.`reference_date` AND `EquityValues`.`asset_code` = **'EquityCustodyTransfer.asset_code'** 
WHERE `EquityCustodyTransfer`.`reference_date` = '2018-05-15' AND `EquityCustodyTransfer`.`sinacor_account` = '256344';

It compares EquityValues.asset_code to 'EquityCustodyTransfer.asset_code' (as a String)

[mythox]

updates:

@manchicken solution works, the output is basically what I need, only 1 weird thing happened.

{
        "MSType": 1,
        "MSProperty": 1,
        "MaterialNum": "1A-2043D",
        "RefNum": "PE-SZ-009",
        "OptionNum": 49,
        "PSType": 1,
        "createdAt": null,
        "updatedAt": null,
        "MsProp": {
            "MSType": 1,
            "MSProperty": {
                "$col": "MatProp.MSProperty"
            },
            "MSPropDesc": "Vickers Hardness",
            "PSType": 1,
            "createdAt": null,
            "updatedAt": null
        }
    }

MsProp.MSProperty become "$col": "MatProp.MSProperty", however the result is correct.

=======================

i cannot reproduce as well:

MatProp.hasOne(models.MsProp, {
      foreignKey: 'MSType',
      targetKey: 'MSType',
      scope: {
        MSProperty: 'MatProp.MSProperty'
      }
    })

SELECT * FROM `MatProps` AS `MatProp` LEFT OUTER JOIN `MsProps` AS `MsProp` ON `MatProp`.`MSType` = `MsProp`.`MSType` AND `MsProp`.`MSProperty` = 'MatProp.MSProperty';

become a string as 'MatProp.MSProperty'...

[gchokhonelidze]

I just thinking to run sql query on foregn key creation instead of using sequelize hasMany, as composite key is urgent for me.

[xianxingg]

@manuelbertelli it works out that the logging sql shows the expected join-on-and query statement. However, when you sync model, there is an error indicating that the fk violates unique constraint.
It's not required that every one of the compositing foreign keys be unique, while sequelize does. That's a problem.

[simonbrent]

@muhammadumair-rauf I don't think scope works when joining on multiple columns - it only seems to support fixed values.

[abelosorio]

Hi everyone! Another _geek_ using normalized tables here (I could paste here a lot of references explaining why composite PK and FK are needed in a well normalized database).

This issue was opened in 2012, 6 years ago... So I think we should work in some decorator for Sequelize in order to support this feature in our projects. And then, we can continue waiting for a response of Sequelize's mantainers or even to create a PR with our code...

Sequelize mantainers I'd like to know what would be a good approach to implement this. I'd happily spend some hours of my time to work on this.

Thanks again.

[BaalKerner]

@abacha
I have been able to make a proper query.

ZoneDefinition.associate = models => {
    models.ZoneDefinition.hasMany(models.ZonePC, {
      foreignKey: 'zoneName',
      sourceKey: 'zoneName',
      scope: {
        Banner_ID: sequelize.where(sequelize.col('ZoneDefinition.Banner_ID'), '=', sequelize.col('ZonePC.Banner_ID')),
      },
      as: 'ZonePC',
    });
  };

SELECT *
FROM `zone_definition` AS `ZoneDefinition`
LEFT OUTER JOIN `zone_pc` AS `ZonePC`
ON `ZoneDefinition`.`Zone_Name` = `ZonePC`.`Zone_Name`
AND `ZoneDefinition`.`Banner_ID` = `ZonePC`.`Banner_ID`;

But it doesn't seem that constraints are added to table creation query.

Another way to do it is this approach.
https://stackoverflow.com/questions/42226351/sequelize-join-with-multiple-column

[szaudowsky]

What about this feature? Are you going to do something with it? In the near future?

[fabioespinosa]

I know it's not the best solution but if you want to enforce foreign composite key just add this query after sync:

await sequelize.query(
    `
    ALTER TABLE IF EXISTS "TableName" DROP CONSTRAINT IF EXISTS "TableName_otherTableReference_fkey";
    ALTER TABLE IF EXISTS "TableName"
    ADD CONSTRAINT "TableName_otherTableReference_fkey"
    FOREIGN KEY (column1,column2)
    REFERENCES "OtherTable";
`);

[LeandroDoldan]

+1

[IbraheemAlSaady]

So the issue has been open for a while, is there any update on this?

[abelosorio]

I'm still waiting for it... as I said before, I'd spend time on this, but I need some directions. I don't want to spend my time to create a PR that never gets merged or reviewed... :disappointed:

[IbraheemAlSaady]

@abelosorio could you share what you've found in code? files that I can look into, maybe I can jump on this as well if I have the time

[abelosorio]

Hi @IbraheemAlSaady, I haven't dug into the code yet. I just offered a list of references on why composite keys are actually needed because I think Sequelize's maintainers want us to use workarounds and have doubts about the use of composite keys.

The discussion here is not whether composite keys are needed or not (you can jump into any RDMS engine's documentation and figure it out)... this issue's discussion, IMHO, is if RDMS allow this, why are Sequelize's maintainers avoiding to address this...

Anyway, I don't how many would take to find a good solution to this. I've just been avoiding spending time on coding a solution that never gets accepted by the maintainers...

[IbraheemAlSaady]

@abelosorio yeah I understand. In my case I have to have composite keys, migrations work (through a normal query), but in models it doesn't. And I agree, composite keys are important and sometimes you can't escape them, and since RDBMS allow this, I have no idea why its been avoided. The issue has been open since 2012, that's so weird.

[ShaunParsons]

I think it's safe to say people are quite keen to see this implemented. Maybe it would be good if @sushantdhiman could confirm if a PR for this would be considered?

For reference, there is already a similar PR that might help people get started: https://github.com/sequelize/sequelize/pull/8763

[szaudowsky]

I think We should all accept fact that sequelize is only for common tasks. If you'd like to implement advanced case, you should start looking for another ORM ;)
In some specific cases composite keys cannot be ommited..
It's really preposterous that we need to wait (acceptance?) for feature almost 8 years?

[josephbridgwaterrowe]

@BaalKerner thank you for your solution, this works a treat.

Of course we'd prefer "real" support for composite keys, but I prefer this over creating views to sit on top of a legacy MSSQL database.

[scott-wyatt]

@mythox's solution works for me, just be aware, you will need to create multiple associations. One for creating with includes and one for querying with includes.

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. If this is still an issue, just leave a comment 🙂

[scott-wyatt]

Commenting to keep this open. Ill try and submit a PR before to long because i’d love to have a native solution to composite keys.

[MarceloAplanalp]

Any news about this feature request? Are you planning to introduce it in the near future?

[papb]

@MarceloAplanalp There is no plan from maintainers to tackle this in the near future, but if any contributor is willing to help, that would be great. Perhaps @scott-wyatt is working on it, I don't know

[scott-wyatt]

I'm fairly back logged at the moment, but I'm fairly certain it can be done. There is already multiple primary key support for most of Sequelize, add it to associations is tricky, but doable.

[Bitha007]

SELECT * FROM pgPracticeResult JOIN regularQuestions On regularQuestions.id = pgPracticeResult.pg_practice_id AND regularQuestions.answer = pgPracticeResult.check

Any idea how can I make this work

[Bitha007]

SELECT * FROM pgPracticeResult JOIN regularQuestions ON pgPracticeResult.pg_practice_id = regularQuestions.id AND pgPracticeResult.check = regularQuestions.answer WHERE user_id = 14

Any idea how can i perform this query

[kangzj]

+1 for this thanks

[abelosorio]

+1

Totally disappointed by Sequelize. Great package at first, but died because of poor maintenance.

[tienphan1096]

@abacha
I have been able to make a proper query.

ZoneDefinition.associate = models => {
    models.ZoneDefinition.hasMany(models.ZonePC, {
      foreignKey: 'zoneName',
      sourceKey: 'zoneName',
      scope: {
        Banner_ID: sequelize.where(sequelize.col('ZoneDefinition.Banner_ID'), '=', sequelize.col('ZonePC.Banner_ID')),
      },
      as: 'ZonePC',
    });
  };

SELECT *
FROM `zone_definition` AS `ZoneDefinition`
LEFT OUTER JOIN `zone_pc` AS `ZonePC`
ON `ZoneDefinition`.`Zone_Name` = `ZonePC`.`Zone_Name`
AND `ZoneDefinition`.`Banner_ID` = `ZonePC`.`Banner_ID`;

But it doesn't seem that constraints are added to table creation query.

Another way to do it is this approach.
https://stackoverflow.com/questions/42226351/sequelize-join-with-multiple-column

This doesn't work in this specific case. Say you have Car, which has Components, which in turns has Parts. Car.findAll(inlude: both Components and Parts) will break.

I debugged the raw query, and it seems the Part table was aliased as 'Component -> Part'. So 'scope: {part.fk2 = component.fk2)' will cause MSSQL error 'The multi-part identifier 'part.fk2' could not be found. A fix is to put 'scope: {component.part.fk2 = component.fk2)', however, this will break Components.findAll(include: Parts) (since Part is no longer aliased as 'Component -> Part').

[sergioshev]

+1

Totally disappointed by Sequelize. Great package at first, but died because of poor maintenance.

same here

[soryy708]

This is a hard feature to implement. It touches on a lot of sequelize subsystems and has many tricky edge cases.
The scope can be reduced by doing https://github.com/sequelize/sequelize/issues/11830 first, which soft-blocks this.

[metaman]

+1

Totally disappointed by Sequelize. Great package at first, but died because of poor maintenance.

Have you checked with the owner/s of the repo to see if they need any help. Totally understandable for people to get busy with life and find it hard to maintain a free component.

[abhi5658]

I tried to make a solution for one of my use case to make a join condition for two non primary key columns with other table at stackoverflow: https://stackoverflow.com/a/63830718/8133717. Please check if that helps. It might help only in querying data and not creating composite foreign keys.

[osvaldokalvaitir]

I see that the problem has already been solved!

How do I use it?

What version of Sequelize do I use?

[bcdxn]

I see that the problem has already been solved!

How do I use it?

What version of Sequelize do I use?

It looks like the feature was merged into master but has not been merged into the v6 release branch. I would love for this to be available too.

[sauldeleon]

For the moment I think the approach of adding a classic primary key and a unique constraint on the foreign table with the 2 fields is working for me.

Thought will be good to have this feature. Keep the good work!

[daviddossantos]

Any updates?

[Yattabyte]

👀💢 well this is rather frustrating trying to work with an incomplete API with virtually no examples for functionality which I would have thought would have been implemented years ago.

Maybe in another 8 years it will be possible to associate 2 tables together with compound keys.

[proftom]

so does sequelize still not support composite foreign keys? does it support composite (non-foreign) primary keys? I am trying to create 3 foreign keys as the composite primary key for my table. if that doesn't work I do hope I can create a unique constraint over the 3 foreign key fields and then use a surrogate key as the primary key.

[Yattabyte]

so does sequelize still not support composite foreign keys? does it support composite (non-foreign) primary keys? I am trying to create 3 foreign keys as the composite primary key for my table. if that doesn't work I do hope I can create a unique constraint over the 3 foreign key fields and then use a surrogate key as the primary key.

You can definitely to the latter
In my case, where we had a 5-column composite PK (old-fashioned enterprise data structures), we replaced each of the composite pk's columns with allowNull: false, added an autoIncrement integer PK to the table, and added this to the table's definition:

{
    indexes: [{
        unique: true,
        fields: ['column1', 'column2', ..., 'columnN']
}