Sequelize foreign key reference composite primary key

i’m trying to create with sequelize (postgre) ‘Ingredient’ table, with columns normalizedName/userId where normalizedName is unique per userId.

The second table is ‘IngredientQuantity’, with columns ingredientId/userId/quantity.

I tried to set in ‘Ingredient’ normalizedName and userId as primaryKey, and to foreign key this composite PK from ‘IngredientQuantity’ table with ingredientId, but i saw that was impossible with sequelize, only normalizedName is used for reference in foreign key.

Whats is the best approach to do that ? I thought about id auto increment, but all id are shared among all users. For example user1 create his first ingredient with id = 1, when user2 create his first ingredient he will have id = 2. So. i don’t know if it’s good idea, if all users have lot of ingredients i should use bigint etc..and if they delete/add/delete/add id will grow up.

Ingredient table

module.exports = (sequelize, DataTypes) => {
    var Ingredient = sequelize.define('ingredient', {
        name: DataTypes.STRING,
        normalizedName: { type: DataTypes.STRING, primaryKey: true },
        userId: { type: DataTypes.INTEGER, primaryKey: true }
    }, {
        freezeTableName: true
    });

    Ingredient.associate = function (models) {
        models.ingredient.hasMany(models.ingredientQuantity);
        models.ingredient.belongsTo(models.user, {
            onDelete: "CASCADE",
            foreignKey: {
                allowNull: false
            }
        });
    };

    return Ingredient;
};

IngredientQuantity table

module.exports = (sequelize, DataTypes) => {
    var IngredientQuantity = sequelize.define('ingredientQuantity', {
        quantity: DataTypes.FLOAT,
    }, {
        freezeTableName: true
    });

    IngredientQuantity.associate = function (models) {
        models.ingredientQuantity.belongsTo(models.ingredient);
        models.ingredientQuantity.belongsTo(models.user, {
            onDelete: "CASCADE",
            foreignKey: {
                allowNull: false
            }
        });
    };

    return IngredientQuantity;
};

Whats is the best approach if i consider lot of data with lot of users ? Is there an other solution ? Thanks

Answer

It’s totally normal to use SERIAL as autoincremented integer surrogate PK. Also you can use UUID as autogenerated PKs (in such case you should set default value as uuid_generate_v4()) if you somehow afraid that integer value range will not be enough.

Because it’s a service field there is no need it to be unique only for a certain user. Usually you shouldn’t rely on a PK value.

Advertisement

Answer