Reputation: 90
I have a particular case where I have a promotion and a survey attached to it. The thing is don't want the promotion to have FKs for surveys as the surveys can be optional, but I also don't want to store the survey data inside the promotion since it's optional, the survey is always singular. I want to create a one-to-one relationship with the sequelize migration file. But I always create a one-to-many relationship instead. Can it be limited within the migration file or only on the application side of things with model associations. Here are my migration files:
migrations/promotion.js
:
'use strict';
/** @type {import('sequelize-cli').Migration} */
module.exports = {
async up(queryInterface, Sequelize) {
await queryInterface.createTable('Promotions', {
id: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: Sequelize.INTEGER
},
establishmentId:{
type: Sequelize.INTEGER,
allowNull: false,
references: {
model: 'Establishments',
key: 'id'
},
},
name: {
type: Sequelize.STRING
},
pointPayOut: {
type: Sequelize.DECIMAL
},
vidLnk: {
type: Sequelize.STRING
},
imgLnk: {
allowNull: true,
type: Sequelize.STRING
},
description: {
type: Sequelize.STRING
},
createdAt: {
allowNull: false,
type: Sequelize.DATE
},
updatedAt: {
allowNull: false,
type: Sequelize.DATE
}
});
await queryInterface.addConstraint('Promotions', {
fields: ['establishmentId'],
type: 'foreign key',
name: 'FK_PROMOTION_ESTABLISHMENT',
references: {
table: 'Establishments',
field: 'id'
},
onDelete: 'CASCADE',
onUpdate: 'CASCADE'
});
},
async down(queryInterface, Sequelize) {
await queryInterface.dropTable('Promotions');
}
};
migrations/survey.js
:
'use strict';
/** @type {import('sequelize-cli').Migration} */
module.exports = {
async up(queryInterface, Sequelize) {
await queryInterface.createTable('Surveys', {
id: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: Sequelize.INTEGER
},
question: {
type: Sequelize.STRING
},
promotionId: {
type: Sequelize.INTEGER,
allowNull: false,
references: {
model: 'Promotions',
key: 'id'
},
},
correctOptionId: {
type: Sequelize.INTEGER,
allowNull: true,
},
createdAt: {
allowNull: false,
type: Sequelize.DATE
},
updatedAt: {
allowNull: false,
type: Sequelize.DATE
}
});
await queryInterface.addConstraint('Surveys', {
fields: ['promotionId'],
type: 'foreign key',
name: 'FK_SURVEY_PROMOTION',
references: {
table: 'Promotions',
field: 'id'
},
onDelete: 'CASCADE',
onUpdate: 'CASCADE'
});
},
async down(queryInterface, Sequelize) {
await queryInterface.dropTable('Surveys');
}
};
The Postgres ERD schema returns this:
Upvotes: 0
Views: 35