Introspection migration adds ON UPDATE CASCADE ON DELETE SET NULL to foreign key constraints

[lastmjs]

Bug description

This is all using postgres by the way, postgres 12.3. I am trying to use prisma migrate in an existing app that we are refactoring away from Rails. I have run introspection and generated an initial migration. Everything seems to be working very well. But, just to make sure that the introspected initial migration matched my old Rails migrations, I ran pg_dump --schema-only on the database with the Rails migrations, and the database with just the initial introspected prisma migration. There were a few differences with data types, but the biggest difference was that the foreign key constraints in prisma had ON UPDATE CASCADE ON DELETE SET NULL added to them. These were not present in the Rails schema dump file.

How to reproduce

Difficult to reproduce, but you could create a Rails project, create a simple schema, and migrate using Active record. Make sure to include at least one foreign key constraint. use pg_dump --schema-only after migrating the database. Run prisma introspect on the database after running the Rails migrations.

After that, completely wipe the database and start over. Use prisma migrate deploy to recreate the database from the previously generated introspection initial migration. Now run pg_dump --schema-only again.

If you run a diff on those files, you should see that ON UPDATE CASCADE ON DELETE SET NULL has been added to foreign key constraints...unexpected.

Expected behavior

I do not expect ON UPDATE CASCADE ON DELETE SET NULL to be added to foreign key constraints, if they are not already present. Introspection should create a migration that is identical to the current postgres schema.

Prisma information

Here's an example from the Rails pg_dump: ALTER TABLE ONLY public.voicemails ADD CONSTRAINT fk_rails_fa0980bd48 FOREIGN KEY (contact_id) REFERENCES public.contacts(id);

Here's the same info after instrospection, wiping the database, and deploying the migration: ALTER TABLE ONLY public.voicemails ADD CONSTRAINT voicemails_contact_id_fkey FOREIGN KEY (contact_id) REFERENCES public.contacts(id) ON UPDATE CASCADE ON DELETE SET NULL;

Environment & setup

• OS: Ubuntu 16
• Database: Postgres 12.3
• Node.js version: 12.13.1
• Prisma version: 2.22.1

[tomhoule]

Hi @lastmjs — this is something we are working on at the moment. There is an issue open for feedback on the current proposal: #6996 — your input there would be very appreciated :)

[pimeys]

Fixed in prisma/prisma-engines#1947
Please read the proposal in #6996

Instructions on how to use this, and a place to give feedback: #7816

[janpio]

This has now been released as a preview feature flag. You can read about it in the release notes for 2.26.0: https://github.com/prisma/prisma/releases/tag/2.26.0 If you have any feedback, please use this issue: #7816