sql - 将表拆分为多对多关系 : Data Migration

Question

我想知道在将表拆分为多对多关系时如何最好地迁移我的数据。我已经做了一个简化的例子，我也会发布一些我想出的解决方案。我正在使用 Postgresql 数据库。

迁移前

table 上人

ID       Name        Pet        PetName
1        Follett     Cat        Garfield
2        Rowling     Hamster    Furry
3        Martin      Cat        Tom
4        Cage        Cat        Tom

迁移后

table 上人

ID       Name
1        Follett
2        Rowling
3        Martin
4        Cage

table 上宠物

ID       Pet        PetName
6        Cat        Garfield
7        Hamster    Furry
8        Cat        Tom
9        Cat        Tom

表人宠物

FK_Person     FK_Pet
1             6
2             7
3             8
4             9

注意事项:

• 我将特别复制宠物表中的条目(因为在我的情况下 - 由于其他相关数据 - 其中一个可能仍可由客户编辑，而另一个可能不能)。
• 没有唯一标识“宠物”记录的列。
• 对我来说，3-8 和 4-9 在 PersonPet 表中链接还是 3-9 和 4-8 链接并不重要。
• 此外，我省略了所有处理表模式更改的代码，因为据我所知，这与这个问题无关。

我的解决方案

1. 在创建宠物表时临时添加一列，其中包含用于创建此条目的人员表的 ID。

    ALTER TABLE Pet ADD COLUMN IdPerson INTEGER;

    INSERT INTO Pet (Pet, PetName, IdPerson)
    SELECT Pet, PetName, ID
    FROM Person;

    INSERT INTO PersonPet (FK_Person, FK_Pet)
    SELECT ID, IdPerson
    FROM Pet;

    ALTER TABLE Pet DROP Column IdPerson;

1. 避免临时修改 Pet 表

    INSERT INTO Pet (Pet, PetName)
    SELECT Pet, PetName
    FROM Person;

    WITH
      CTE_Person
      AS
      (SELECT
        Id, Pet, PetName
        ,ROW_NUMBER() OVER (PARTITION BY Pet, PetName ORDER BY Id) AS row_number
      FROM Person
      )
      ,CTE_Pet
      AS
      (SELECT
        Id, Pet, PetName
        ,ROW_NUMBER() OVER (PARTITION BY Pet, PetName ORDER BY Id) AS row_number
      FROM Pet
      )
      ,CTE_Joined
      AS
      (SELECT
        CTE_Person.Id AS Person_Id,
        CTE_Pet.Id AS Pet_Id
      FROM
        CTE_Person
        INNER JOIN CTE_Pet ON
        CTE_Person.Pet = CTE_Pet.Pet
        CTE_Person.PetName = CTE_Pet.PetName
        AND CTE_Person.row_number = CTE_Pet.row_number
      )
      INSERT INTO PersonPet (FK_Person, FK_Pet)
      SELECT Person_Id, Pet_Id from CTE_Joined;

问题

1. 两种解决方案都正确吗？ (我已经测试了第二种解决方案，结果似乎是正确的，但我可能遗漏了一些极端情况)
2. 这两种解决方案的优点/缺点是什么？
3. 是否有更简单的方法来执行相同的数据迁移？ (出于好奇，我也会对稍微修改我的约束的答案感兴趣(例如，宠物表中没有重复的条目)，但请指出是哪些:))。

Answer 1

实现您描述的效果的另一种解决方案(在我看来是最简单的；没有任何 CTE-s 或额外的列):

create table Pet as
    select
        Id,
        Pet,
        PetName
    from 
        Person;

create table PersonPet as
    select
        Id as FK_Person,
        Id as FK_Pet
    from
        Person;

create sequence PetSeq;
update PersonPet set FK_Pet=nextval('PetSeq'::regclass);
update Pet p set Id=FK_Pet from PersonPet pp where p.Id=pp.FK_Person;

alter table Pet alter column Id set default nextval('PetSeq'::regclass);
alter table Pet add constraint PK_Pet primary key (Id);
alter table PersonPet add constraint FK_Pet foreign key (FK_Pet) references Pet(Id);

除非我们使用序列生成一个，否则我们只是使用现有的人 ID 作为宠物的临时 ID。

编辑

也可以使用我的方法已经完成架构更改:

insert into Pet(Id, Pet, PetName)
    select
        Id,
        Pet,
        PetName
    from
        Person;

insert into PersonPet(FK_Person, FK_Pet)
    select
        Id,
        Id
    from
        Person;

select setval('PetSeq'::regclass, (select max(Id) from Person));