New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Database migrations fail to add foreign key #966
Comments
I try to add new column and foreign key migration.addColumn('user', 'level_id', {
type: DataTypes.INTEGER.UNSIGNED,
references: 'level',
referenceKey: 'id',
onUpdate: 'cascade',
onDelete: 'restrict'
}); The column will be successfully created but without foreign key. |
@theoptz No, this issues is not resolved yet. The current code only handles foreign keys in the context of table creation, not column updates. |
👍 👀 Waiting for this issue |
👍 |
2 similar comments
👍 |
👍 |
👍 Would be great to be able to remove foreign keys as well. I am converting a 1:many relationship to a many:many and need to remove the original foreign key constraint. |
👍 |
2 similar comments
👍 |
👍 |
Also fail on |
Not related to original issue but |
I'm using v2.0-rc1, and This is the migration code:
|
The issue is still open, so yes, it's still an issue :) |
:) That was in reference to the comment above : Not related to original issue but addColumn should now support foreign key constraints as of 0c1ec1c. Original issue was about |
Well the unit test added in that commit passes, so |
Waiting too |
Me too. |
+1 |
4 similar comments
+1 |
+1 |
+1 |
+1 |
+1 +1 |
I'm using sequelize "^2.0.0-rc2" and sequelize-cli "^0.3.3". I just added foreign key constrain on my table using migration.createTable. |
@corbanb Hmm, although the original issue deals with changeColumn not addColumn, so still might not work. |
@mickhansen nope,
|
Annoyingly this applies to any change to a column with a foreign key, not just addition/removal of a foreign key from a column. So it's impossible to create |
Ok, I've come to a realization. Me and everyone else here are wrong. Neither pg nor MySQL actually support You can modify a column that has a reference, all you do is exclude the reference information from the column options. The The proper way to modify foreign key references is using So the real result for this bug should be:
|
Is there anyone working on a pull request for this, or should I work on a PR myself? |
@legomind No work is currently being done, a PR would be great! |
nothing yet? |
+1 |
1 similar comment
+1 |
@corbanb Thanks for that code snippet. I tried to associate a new model with my User model, like you did with Post and it only worked when I changed User to the plural form "Users". |
+1 still broken in |
Can anyone chime in with what dialects they had issues with specifically? We hit this in MySQL (and related issues in SQLite which has no alter table support). I'd like for a pull request to tackle each affected dialect. |
@jocull All dialects fail. Constraints can't be added in a single add column call - So the logic needs to be changed to do two calls internally, or we add addConstraint methods as we've planned and force users to use that :) |
I submitted pull request #5014 in an attempt to deal with this. Can anyone else help me finally fix this? |
+1 |
Not sure if this should be reopened or if a new ticket should be made, but the constraint isn't properly removed when a migration undo is done:
The up-migration here properly adds the keys, but the down migration doesn't remove them. |
I have a same problem as @jamespedid. I use version 3.24.11 and mysql |
@jamespedid open a new ticket |
No need to open ticket @jamespedid , Its already in milestone v4 , #5212 |
@sushantdhiman I'm not sure if it solves my problem too. I have this code: module.exports = {
up: function(queryInterface, Sequelize) {
return queryInterface.changeColumn('accounts', 'user_id', {
type: Sequelize.INTEGER,
onDelete: 'CASCADE',
onUpdate: 'SET NULL'
});
},
down: function(queryInterface, Sequelize) {
return queryInterface.changeColumn('accounts', 'user_id', {
type: Sequelize.INTEGER,
onDelete: 'CASCADE',
onUpdate: 'CASCADE'
});
}
}; Change is only on |
@sushantdhiman thanks, you are right. Can you help me with this problem now? Exist any options how do this now? I can of course remove column and add again with correct properties, but I need keep data in table during migrations and this would be unpleasantly. |
@sushantdhiman thanks a lot. |
After reading through this thread, it doesn't seem this has been implemented yet or am I incorrect? |
@flouet-company should be implemented based on #7108 |
I have an integer column called
orderId
.Now I want to add a foreign key to it:
but the migration fails with this:
The error is caused by this query:
which is invalid SQL. It should add a constraint to the column.
The text was updated successfully, but these errors were encountered: