Search code examples
postgresqlforeign-keys

How do I convert an existing column to Foreign Key?

I have a two tables.

Table a looks something like this -- first row is column names, both contain characters:

id |  tractce   | someString
1  |  "0011900" | "Label here"

Table b:

id | tractFIPS
1  | "0011900"

How do I convert a.tractce to a foreign key column referencing b.id?

So that it's:

id |  tractce | someString
1  |  1       | "Label here"
Solution

  • You can't do this in one step. You need to first add a new column that can hold the primary key of table b, then update table a and then add the foreign key and drop the old column:

    alter table a add b_id int;

update a
   set b_id = b.id
from b 
where a.tractce = b.tractfips;


alter table a drop column tractce;
alter table a add constraint fk_a_b foreign key (b_id) references b;

    Online example: http://rextester.com/LPWNHK59722