Sequelize foreign key reference composite primary key

Lyofen

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

Anatoly

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.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

Foreign Key Reference Composite Primary Key

Sequelize, foreign keys as composite primary key

Composite primary key, foreign key. Reference to object or key?

Composite foreign key as primary key

Foreign_Key reference to part of Composite Primary_Key

Mapping a composite foreign key to a composite primary key

JPA / Hibernate - Composite primary key with foreign key

Hibernate foreign key with a part of composite primary key

JPA & Hibernate - Composite primary key with foreign key

JPA & Hibernate - Composite primary key with foreign key

JPA composite primary key/foreign key mapping

SQL: Foreign key references a composite primary key

Room composite Primary Key link to Foreign Key

Using Composite Primary Key As Foreign Key

One of Composite primary key as Foreign key Mysql

Mapping composite foreign key to composite primary key where the foreign key is also a primary key

Composite primary key in junction table - Sequelize

JPA how to make composite Foreign Key part of composite Primary Key

hibernate composite Primary key contains a composite foreign key, how to map this

How create composite foreign key to table with composite primary key

Foreign key relationship with composite primary keys in mysql

Spring save composite primary key with foreign keys

Composite primary key of two foreign keys

Only one key from composite primary key as foreign key

Creating Primary and Foreign Key relations in Sequelize

Primary Key and Composite Key

JPA composite primary key with reference chain

Composite foreign key made up of part of primary key in parent table

Can a Foreign Key be part of a Composite Primary Key for another table?