How do I convert an existing column to Foreign Key?

Question

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"

Pily

Answer 1

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

Acutance

Are you sure about this? This answer with some upvotes above seems to say its possible: https://mcmap.net/q/781860/-how-do-i-convert-an-existing-column-to-foreign-key – Ovum

Answer 2

This will add the constraint as long as there isn't any rogue data in the tables:

ALTER TABLE TableName
ADD CONSTRAINT fk_Name
FOREIGN KEY (ColumnName) 
REFERENCES TableName(ColumnName);

Burglarious

Sorry, I edited my question to fix a typo and clarify it. Does your answer still apply? – Pily

you cant have a foreign key on columns that don't have the same data in (well you can but its never a good idea and would be pointless). You would have to match id to id or tractce to tractFIPS. the syntax would be the same – Burglarious

Am I right in assuming any existing data in the updated column would be lost ? – Varietal

Answer 3

You can convert it as you have asked:

UPDATE a SET tractce = b.id FROM b WHERE TRIM(a.tractce) = TRIM(b.tractfips);
ALTER TABLE a ALTER COLUMN tractce TYPE INTEGER USING (a.tractce::integer);
ALTER TABLE a ADD CONSTRAINT a_tractce_b_fk FOREIGN KEY (tractce) 
  REFERENCES b (id) ON UPDATE RESTRICT ON DELETE SET NULL;

but better to rename a.tractce into a.tractce_id

ALTER TABLE a RENAME tractce TO tractce_id;

Metabolism