sql - 如何提取一列数据并用外键替换？

Question

如何将数据的列(varchar，允许为空)提取到新表中并用外键替换它？

我所知道的步骤:

1. Create a new table with an incrementing PK column and data varchar column.
2. Use insert-into-select to copy the data into the new table.
3. Update/add a foreign key column in the original table???

如何制作外键，第 3 步？

Answer 1

已经存在的表t:

create table t (s text);
insert into t (s) values ('a'), ('a'), ('b'), (null)

要引用的表:

create table s (
    s_id serial primary key,
    s text
)

将不同的非空值复制到引用表中:

insert into s (s)
select distinct s
from t
where s is not null

创建外键 ID 列:

alter table t
add column s_id int

用外键 ID 填充新列:

update t
set s_id = s.s_id
from s
where t.s = s.s

创建约束:

alter table t
add foreign key (s_id) references s (s_id)

删除现在不需要的列:

alter table t
drop column s

最终结果:

\d t
    Table "public.t"
 Column |  Type   | Modifiers 
--------+---------+-----------
 s_id   | integer | 
Foreign-key constraints:
    "t_s_id_fkey" FOREIGN KEY (s_id) REFERENCES s(s_id)

\d s
                        Table "public.s"
 Column |  Type   |                    Modifiers                     
--------+---------+--------------------------------------------------
 s_id   | integer | not null default nextval('s_s_id_seq'::regclass)
 s      | text    | 
Indexes:
    "s_pkey" PRIMARY KEY, btree (s_id)
Referenced by:
    TABLE "t" CONSTRAINT "t_s_id_fkey" FOREIGN KEY (s_id) REFERENCES s(s_id)

测试约束:

insert into t (s_id) values (3);
ERROR:  insert or update on table "t" violates foreign key constraint "t_s_id_fkey"
DETAIL:  Key (s_id)=(3) is not present in table "s".

示例查询:

select s_id, coalesce(s, 'null') as s
from t left join s using(s_id);
 s_id |  s   
------+------
      | null
    2 | a
    2 | a
    1 | b