Schred Posts - Answer Overflow

Schred

•Created by Schred on 12/8/2023 in #help

How to use cascade with a multi-column foreign key?

When using references I can tell a foreign key to cascade on a delete easily. However: I have a table with a composite primary key:

const items = pgTable(
    'items',
    {
        id: integer('id').notNull(),
        hotelId: integer('hotel_id')
            .references(() => hotels.id, CASCADE)
            .notNull(),
                // ...other fields
    },
    (table) => ({
        pk: primaryKey(table.id, table.hotelId)
    })
);

const items = pgTable(
    'items',
    {
        id: integer('id').notNull(),
        hotelId: integer('hotel_id')
            .references(() => hotels.id, CASCADE)
            .notNull(),
                // ...other fields
    },
    (table) => ({
        pk: primaryKey(table.id, table.hotelId)
    })
);

and a table referencing that table with a composite foreign key:

const addresses = pgTable(
    'client_item_addresses',
    {
        itemId: integer('item_id').notNull(),
        hotelId: integer('hotel_id').notNull(),
        // ...other fields
    },
    (table) => ({
        pk: primaryKey(table.itemId, table.hotelId),
        fk: foreignKey({
            columns: [table.itemId, table.hotelId],
            foreignColumns: [items.id, items.hotelId]
        })
    })
);

const addresses = pgTable(
    'client_item_addresses',
    {
        itemId: integer('item_id').notNull(),
        hotelId: integer('hotel_id').notNull(),
        // ...other fields
    },
    (table) => ({
        pk: primaryKey(table.itemId, table.hotelId),
        fk: foreignKey({
            columns: [table.itemId, table.hotelId],
            foreignColumns: [items.id, items.hotelId]
        })
    })
);

How do I activate cascade on delete in this scenario?

9 replies

Gaming

Programming