Describe the bug
This problem affects sqlite DB, tested on MySQL and it was fine.
When adding a foreign key to existing table, the table is dropped and created again with incorrect NOT NULL setting on column. If there is no foreign key, standard ALTER is used and this issue won't happen.
To Reproduce
Setup sqlite database
Create migration with nullable column
Create another migration, add new nullable column to the table created before, reference some foreign key
Migrate
The original column is now NOT NULL
Expected behavior
Table should be recreated as was defined before, NULL column should not change to NOT NULL.
Screenshots or code snippets
Migration:
with self.schema.create("users") as table:
table.increments('id')
with self.schema.create("posts") as table:
table.string('title').nullable()
with self.schema.table("posts") as table:
table.string('user_id').nullable()
table.foreign('user_id').references('id').on('users')
Notice that the title column should be nullable ("NULL"). The SQL code generated by this migration is:
CREATE TABLE "users" ("id" INTEGER NOT NULL, CONSTRAINT users_id_primary PRIMARY KEY (id)), (). Executed in 0.01ms
CREATE TABLE "posts" ("title" VARCHAR(255) NULL), (). Executed in 0.00ms
PRAGMA table_info(posts), (). Executed in 0.00ms
ALTER TABLE "posts" ADD COLUMN "user_id" VARCHAR NULL REFERENCES "users"("id"), (). Executed in 0.00ms
CREATE TEMPORARY TABLE __temp__posts AS SELECT title FROM posts, (). Executed in 0.00ms
DROP TABLE "posts", (). Executed in 0.00ms
CREATE TABLE "posts" ("title" VARCHAR(255) NOT NULL, "user_id" VARCHAR(255) NULL, CONSTRAINT posts_user_id_foreign FOREIGN KEY ("user_id") REFERENCES "users"("id")), (). Executed in 0.00ms
INSERT INTO "posts" ("title") SELECT title FROM __temp__posts, (). Executed in 0.00ms
DROP TABLE __temp__posts, (). Executed in 0.00ms
The title column is now recreated as NOT NULL, which is incorrect behaviour.
Desktop (please complete the following information):
Describe the bug This problem affects sqlite DB, tested on MySQL and it was fine. When adding a foreign key to existing table, the table is dropped and created again with incorrect NOT NULL setting on column. If there is no foreign key, standard ALTER is used and this issue won't happen.
To Reproduce
Expected behavior Table should be recreated as was defined before, NULL column should not change to NOT NULL.
Screenshots or code snippets Migration:
Notice that the title column should be nullable ("NULL"). The SQL code generated by this migration is:
The title column is now recreated as NOT NULL, which is incorrect behaviour.
Desktop (please complete the following information):
What database are you using?