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"

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

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);