alembic: adding id field to existing table

Question

This is a follow on to my previous question. Basically I have a table that has 2 foreign key links into two other table. But I think the best way is to add a primary key to the table itself so that it becomes:

id, int, primary
foreign_id_1, int, primary
foreign_id_2, int, primary

The problem is that there are already a lot of items in the table. So when I ran alembic upgrade head, it add 0 as the value of all the existing items. This apparently breaks my code.

Is there a way to retrospectively add ids to the existing items?

score 1 · Answer 1 · edited May 23 '17 at 11:50

1

You shouldn't have a multiple primary keys in one table. See this question. What you want to do? Perhaps, your problem may be solved with two column index

edited May 23 '17 at 11:50

Community

1
1

answered Mar 30 '16 at 08:05

Danil

4,781
1
35
50

So in a N to N link table, it's essentially wrong to only have the two foreign keys, right? – lang2 Mar 30 '16 at 09:07
You can have two foreign key, but primary key should be one. – Danil Mar 30 '16 at 09:12

score 1 · Answer 2 · answered Mar 30 '16 at 17:57

What you want is not an additional id column as the primary key. What you want instead is to keep your existing composite primary key and add a surrogate id column (not a primary key column) that auto-increments to keeps track of the order of insertion. But because you've already inserted all your rows into the table, you've lost the order, so it makes no sense to "recover" that order. You can simply assign 0 to that column without issue.

alembic: adding id field to existing table

2 Answers2