Thread: Cannot alter columns and add constraints in one alter statement since11.4 update
Cannot alter columns and add constraints in one alter statement since11.4 update
From
Maarten Jacobs
Date:
Since the update of my PostgreSQL server to 11.4 some of my automated migrations of a web app (Phoenix on Elixir) startedto fail. I’ve narrowed it down to not being able to do the following: GIVEN THESE TABLES: ==================== Table "public.users" Column | Type | Collation | Nullable | Default --------+--------+-----------+----------+----------------------------------- id | bigint | | not null | nextval('users_id_seq'::regclass) Indexes: "users_pkey" PRIMARY KEY, btree (id) Referenced by: TABLE "likes" CONSTRAINT "likes_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id) ------------------------ Table "public.posts" Column | Type | Collation | Nullable | Default --------+--------+-----------+----------+----------------------------------- id | bigint | | not null | nextval('posts_id_seq'::regclass) Indexes: "posts_pkey" PRIMARY KEY, btree (id) Referenced by: TABLE "likes" CONSTRAINT "likes_post_id_fkey" FOREIGN KEY (post_id) REFERENCES posts(id) ------------------------ Table "public.likes" Column | Type | Collation | Nullable | Default -------------+-----------------------------+-----------+----------+----------------------------------- id | bigint | | not null | nextval('likes_id_seq'::regclass) user_id | bigint | | | post_id | bigint | | | inserted_at | timestamp without time zone | | not null | updated_at | timestamp without time zone | | not null | Indexes: "likes_pkey" PRIMARY KEY, btree (id) "unique_user_post_index" UNIQUE, btree (user_id, post_id) "likes_post_id_index" btree (post_id) "likes_user_id_index" btree (user_id) Foreign-key constraints: "likes_post_id_fkey" FOREIGN KEY (post_id) REFERENCES posts(id) "likes_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id) ==================== I am not able to run the (generated) alter statement: ALTER TABLE "likes" DROP CONSTRAINT "likes_user_id_fkey", ALTER COLUMN "user_id" TYPE bigint, ADD CONSTRAINT "likes_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE CASCADE, DROP CONSTRAINT "likes_post_id_fkey", ALTER COLUMN "post_id" TYPE bigint, ADD CONSTRAINT "likes_post_id_fkey" FOREIGN KEY ("post_id") REFERENCES "posts"("id") ON DELETE CASCADE But the following statement does work: ALTER TABLE "likes" DROP CONSTRAINT "likes_user_id_fkey", ADD CONSTRAINT "likes_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE CASCADE, DROP CONSTRAINT "likes_post_id_fkey", ADD CONSTRAINT "likes_post_id_fkey" FOREIGN KEY ("post_id") REFERENCES "posts"("id") ON DELETE CASCADE Is this how it is supposed to work or is it a bug?