数据库重构到多对多关系

在一些数据库重构过程中，我需要引入未来的多对多关系，我偶然发现了一些我认为一定是可能的事情。

给定一个原始表格：

CREATE TABLE a (
  id serial NOT NULL,
  field1 varchar, 
  field2 varchar, 
  field3 varchar 
);

和一个新创建的表：

CREATE TABLE b (
  id serial NOT NULL,
  field1 varchar, 
  field2 varchar 
);

我想将字段1和字段2的内容从a移动到b。为了适应多对多关系，还提供了一个关联表：

CREATE TABLE a_b (
  aid serial NOT NULL,
  bid serial NOT NULL
);

在这里，原始的a-id和新创建的b-id应该结束。

因此，如果我从一个像这样的a表开始

| 33 | John | Jane | Juli |
| 34 | Fred | Carl | Josh |

我想看到它最终出现在b表中：

| 1 | John | Jane
| 2 | Fred | Carl

和关联a_b

| 33 | 1 |
| 34 | 2 |

原始表格将删除字段1和字段2，因此我看起来像这个

| 1 | Juli |
| 2 | Josh |

我立刻想到了与查询一起使用，但没有找到让它发挥作用的方法。这显然不起作用：

WITH new AS (
    INSERT INTO b (field1, field2) 
    SELECT field1, field2 FROM a
    RETURNING id
  )
INSERT INTO a_b (???, bid) SELECT * FROM new;

但说明了这个问题。我找不到在与相应的a-id保持关系的同时插入b的方法。我认为这一定是可能的。

有什么想法吗？

瑟伦

附言：为了简洁起见，我省略了外键之类的东西。

Brevity不是你的朋友。当某件东西丢失时，我们无法判断你是在简短还是犯了一个大错误。

CREATE TABLE a (
  id serial primary key,
  field1 varchar, 
  field2 varchar, 
  field3 varchar 
);
CREATE TABLE b (
  id serial primary key,
  field1 varchar, 
  field2 varchar 
);

"关联"表存储外键；您应该存储"a"中的id号和"b"中的id号。所以应该这样声明。

CREATE TABLE a_b (
  aid integer NOT NULL,
  bid integer NOT NULL,
  primary key (aid, bid),
  foreign key (aid) references a (id),
  foreign key (bid) references b (id)
);

让我们在"a"中添加几行。仔细看下面第二行和第三行的重复，"弗雷德"one_answers"卡尔"。

insert into a (field1, field2, field3) values
('John', 'Jane', 'Juli'),
('Fred', 'Carl', 'Josh'),
('Fred', 'Carl', 'Abby'),
('Art', 'Carl', 'Abby');

要填充"b"，请插入来自a.的不同（唯一）组合

insert into b (field1, field2)
select distinct field1, field2 
from a;
select * from b;

id字段1字段2--1约翰·简2艺术卡尔3弗雷德·卡尔

"关联"表需要来自"a"的id号，以及来自"b"的相应id号。你需要加入"a"one_answers"b"。

insert into a_b
select a.id aid, b.id bid
from a
inner join b on a.field1 = b.field1 and a.field2 = b.field2

现在，您可以连接这三个表来查看完整的关联。基于这样的查询创建视图通常很有用。

select b.id bid, b.field1, b.field2, a.id aid, a.field3
from a_b
inner join a on a.id = a_b.aid
inner join b on b.id = a_b.bid
order by a.id;

投标场地1场地2援助场地3--1约翰·简1朱莉3弗雷德·卡尔2乔希3弗雷德·卡尔3艾比2艺术卡尔4艾比

当您对数据正确性感到满意时，可以从a中删除列"field1"one_answers"field2"。

相关内容

最新更新

热门标签：