EF Core migration existing data

Question

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.

Old table: itemsInBag

ID
name
baglabel

New tables: item, itemsInBag, bag

item
Id
Name

itemsInBag
Id
ItemId
BagId

bag
Id
BagLabel

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.#

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

I am receiving this error when trying to run the migration

$exception  {"23505: could not create unique index \"IX_itemsinbag_bagid_itemid\""} Npgsql.PostgresException

"Key (bagid, itemid)=(0, 0) is duplicated."

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?

UPDATE: if I move the unique constraint on bagid and itemid to after the SQL statements i get this error

$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\"

Answer 1

Figured out the solution to this. It was all a matter of arranging where the Indices and foreign key constraints were in relation to my SQL statements. The Foreign key constraints needed to be created after the tables data had been rearranged. There was also an error after where the items table did not exist on the insert. The schema needed to be included such that the final SQL statements were as follows:

migrationBuilder.Sql(
        "INSERT INTO schema.items (Name)" +
        "SELECT name FROM schema.itemsInBag");

    migrationBuilder.Sql(
        "INSERT INTO schema.bag (baglabel)" +
        "SELECT DISTINCT baglabel FROM schema.itemsInBag");

    migrationBuilder.Sql(
        "UPDATE schema.itemsInBag SET bagid =(SELECT id FROM schema.bag WHERE schema.bag.baglabel = schema.itemsInBag.baglabel)"
    );

    migrationBuilder.Sql(
        "UPDATE schema.itemsInBag SET itemid =(SELECT id FROM schema.items WHERE schema.items.name = schema.itemsInBag.name)"
    );