I have an empty Postgres database and I want to declare two tables, one for clients and the other for statuses. The clients table will have a foreign key of client_status_id, which tracks the id field of the statuses table. I also want to make sure that when creating a new client, it always has a default of 1 for client_status_id, which is the id in statuses which will be linked to the "new client" status name.
export default (sequelize: Sequelize) => {
const Clients = sequelize.define(
'Clients',
{
client_id: {
type: DataTypes.INTEGER,
primaryKey: true,
autoIncrement: true,
allowNull: false
},
client_status_id: {
type: DataTypes.INTEGER,
defaultValue: 1,
allowNull: false,
references: {
model: 'client_statuses',
key: 'client_status_id'
}
},
My statuses table looks like this
export default (sequelize: Sequelize) => {
const ClientStatuses = sequelize.define(
'ClientStatuses',
{
client_status_id: {
type: DataTypes.INTEGER,
primaryKey: true,
autoIncrement: true,
allowNull: false
},
status_name: {
type: DataTypes.TEXT,
allowNull: false
}
},
{
tableName: 'client_statuses',
timestamps: true
}
);
return ClientStatuses;
};
My associations look like this:
Clients.belongsTo(ClientStatuses, {
foreignKey: 'client_status_id'
});
ClientStatuses.hasMany(Clients, { foreignKey: 'client_status_id' });
When I try to run sequelize.sync({force: true}) I get the following error:
name: 'SequelizeDatabaseError',
parent: error: syntax error at or near "REFERENCES"
for sql statement
sql: 'ALTER TABLE "clients" ALTER COLUMN "client_status_id" SET NOT NULL;ALTER TABLE "clients" ALTER COLUMN "client_status_id" SET DEFAULT 1 REFERENCES "client_statuses" ("client_status_id") ON DELETE NO ACTION ON UPDATE CASCADE;ALTER TABLE "clients" ALTER COLUMN "client_status_id" TYPE INTEGER;
Why is sequelize generating invalid sql syntax? As far as I understand this shouldn't be possible.
How can I fix this is issue? Do I need to ensure that a row is first created in with id of 1 in the client_statuses table before I can define a default value in my clients table?