title: EF Core migration existing data
body: <p>I am attempting to relocate information in my postgresql database during a migration. data stored in one table is being separated into a many with a table linking them via foreign keys.</p>
  
  <p>Old table: itemsInBag</p>
  
  <pre><code>ID
  name
  baglabel
  </code></pre>
  
  <p>New tables: item, itemsInBag, bag</p>
  
  <pre><code>item
  Id
  Name
  
  itemsInBag
  Id
  ItemId
  BagId
  
  bag
  Id
  BagLabel
  </code></pre>
  
  <p>Currently I have these SQL statements to try to link them together. This is done after the new tables and fields have been added and before the itemsInBag fields are dropped.#</p>
  
  <pre><code>        migrationBuilder.Sql(
              "INSERT INTO items (Name)" +
              "SELECT (name) FROM itemsInBag");
  
          migrationBuilder.Sql(
              "INSERT INTO bag baglabel" +
              "SELECT DISTINCT baglabel FROM itemsInBag");
  
          migrationBuilder.Sql(
              "UPDATE itemsInBag SET bagid =(SELECT id FROM bag WHERE bag.baglabel = itemsInBag.baglabel)"
          );
  
          migrationBuilder.Sql(
              "UPDATE itemsInBag SET itemid =(SELECT id FROM items WHERE items.name = itemsInBag.name)"
          );
  </code></pre>
  
  <p>I am receiving this error when trying to run the migration</p>
  
  <pre><code>$exception  {"23505: could not create unique index \"IX_itemsinbag_bagid_itemid\""} Npgsql.PostgresException
  
  "Key (bagid, itemid)=(0, 0) is duplicated."
  </code></pre>
  
  <p>From everything I have read it would seem this is the right way to do it. Is there a better way of doing this? Is there something I am missing?</p>
  
  <p>UPDATE: if I move the unique constraint on bagid and itemid to after the SQL statements i get this error </p>
  
  <pre><code>$exception  {"23503: insert or update on table \"itemsinbag\" violates foreign key constraint \"FK_itemsinbag_bag_bagid\""} Npgsql.PostgresException
  "Key (bagid)=(0) is not present in table \"bag\"
  </code></pre>