Make order of introspected foreign keys stable

[janpio]

On tables with 2 foreign keys on the same columns, Prisma does not always choose the same one to represent right now (and it can only represent one of them, see #8976). That is problematic, as it triggers noise in our Introspection CI and also can lead to confusing changes for users.

Take the following (My)SQL:

--
-- Table structure for table `charge`
--

DROP TABLE IF EXISTS `charge`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `charge` (
  `charge_no` int(11) NOT NULL,
  `member_no` int(11) NOT NULL,
  `provider_no` int(11) NOT NULL,
  `category_no` int(11) NOT NULL,
  `charge_dt` datetime NOT NULL,
  `charge_amt` decimal(19,4) NOT NULL,
  `statement_no` int(11) NOT NULL,
  `charge_code` char(2) NOT NULL,
  PRIMARY KEY (`charge_no`),
  KEY `charge_category_no` (`category_no`),
  KEY `charge_statement_no` (`statement_no`),
  KEY `charge_statement_no_2` (`statement_no`),
  KEY `charge_statement_no_3` (`statement_no`),
  KEY `charge_member_no` (`member_no`),
  KEY `charge_statement_no_4` (`statement_no`),
  KEY `charge_member_no_2` (`member_no`),
  KEY `charge_statement_no_5` (`statement_no`),
  KEY `charge_member_no_3` (`member_no`),
  KEY `charge_provider_no` (`provider_no`),
  CONSTRAINT `charge_ibfk_1` FOREIGN KEY (`category_no`) REFERENCES `category` (`category_no`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `charge_ibfk_2` FOREIGN KEY (`member_no`) REFERENCES `member` (`member_no`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `charge_ibfk_3` FOREIGN KEY (`member_no`) REFERENCES `member` (`member_no`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `charge_ibfk_4` FOREIGN KEY (`provider_no`) REFERENCES `provider` (`provider_no`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;
/*!40101 SET character_set_client = @saved_cs_client */;

This is introspected as follows:

model charge {
  charge_no    Int      @id
  member_no    Int
  provider_no  Int
  category_no  Int
  charge_dt    DateTime @db.DateTime(0)
  charge_amt   Decimal  @db.Decimal(19, 4)
  statement_no Int
  charge_code  String   @db.Char(2)
  category     category @relation(fields: [category_no], references: [category_no], onDelete: NoAction, onUpdate: NoAction)
  member       member   @relation(fields: [member_no], references: [member_no], onDelete: NoAction, onUpdate: NoAction)
  provider     provider @relation(fields: [provider_no], references: [provider_no], onDelete: Cascade)

  @@index([category_no], name: "charge_category_no")
  @@index([member_no], name: "charge_member_no")
  @@index([member_no], name: "charge_member_no_2")
  @@index([member_no], name: "charge_member_no_3")
  @@index([provider_no], name: "charge_provider_no")
  @@index([statement_no], name: "charge_statement_no")
  @@index([statement_no], name: "charge_statement_no_2")
  @@index([statement_no], name: "charge_statement_no_3")
  @@index([statement_no], name: "charge_statement_no_4")
  @@index([statement_no], name: "charge_statement_no_5")
}

But sometimes also as:

model charge {
  charge_no    Int      @id
  member_no    Int
  provider_no  Int
  category_no  Int
  charge_dt    DateTime @db.DateTime(0)
  charge_amt   Decimal  @db.Decimal(19, 4)
  statement_no Int
  charge_code  String   @db.Char(2)
  category     category @relation(fields: [category_no], references: [category_no], onDelete: NoAction, onUpdate: NoAction)
  member       member   @relation(fields: [member_no], references: [member_no], onDelete: Cascade)
  provider     provider @relation(fields: [provider_no], references: [provider_no], onDelete: Cascade)

  @@index([category_no], name: "charge_category_no")
  @@index([member_no], name: "charge_member_no")
  @@index([member_no], name: "charge_member_no_2")
  @@index([member_no], name: "charge_member_no_3")
  @@index([provider_no], name: "charge_provider_no")
  @@index([statement_no], name: "charge_statement_no")
  @@index([statement_no], name: "charge_statement_no_2")
  @@index([statement_no], name: "charge_statement_no_3")
  @@index([statement_no], name: "charge_statement_no_4")
  @@index([statement_no], name: "charge_statement_no_5")
}

Note the difference in member:

-   member       member   @relation(fields: [member_no], references: [member_no], onDelete: NoAction, onUpdate: NoAction)
+   member       member   @relation(fields: [member_no], references: [member_no], onDelete: Cascade)

---

Per @pimeys this is probably caused by usage of a HashMap in the code somewhere to represent the Introspection query used to get these (probably https://github.com/prisma/prisma-engines/blob/master/libs/sql-schema-describer/src/mysql.rs#L529). If we make this deterministic, we still can not represent both FKs of course, but at least we will always choose the same one.

[pimeys]

This is also happening on PostgreSQL, reopening.

[pimeys]

https://github.com/prisma/introspection-ci/commit/504a60757daee2173d612146aefab54de36720dd

Introspecting the database a few times, and getting different referential actions. Looking into the schema, and I can find two commands:

ALTER TABLE ONLY public.action_person
    ADD CONSTRAINT action_gestion_ag_id_fk2 FOREIGN KEY (ag_id) REFERENCES public.action_gestion(ag_id);

ALTER TABLE ONLY public.action_person
    ADD CONSTRAINT action_person_ag_id_fkey FOREIGN KEY (ag_id) REFERENCES public.action_gestion(ag_id) ON UPDATE CASCADE ON DELETE CASCADE;

[pimeys]

And on SQL Server.....

....

We should investigate at some point why somebody'd want to do this.