Doc request(introspection): document opinionation about foreign key type validations

[tomhoule]

At the database level, there can be foreign keys on columns that do not exactly match the referenced column's types. To illustrate this, in the database, the following situation can happen:

model Cat {
	name String @db.VarChar(200)
}

model Box {
	material BoxMaterial
	width Float
	height Float
	length Float
    catName String @db.VarChar(140)
    occupiedBy Cat @relation(fields: [catName], references: [name])
}

The important bit is that Box.catName is a VARCHAR(140), but Cat.name is a VARCHAR(200). The types could be even more different. The problem is that the PSL/the schema parser doesn't allow this, it validates that the types at both ends of the relation match. This makes a lot of sense for greenfield projects and avoids basic errors and bad schemas. But since it can happen in the database, when we introspect this, an invalid schema is produced.

We haven't done enough research to say whether any of the databases we support is strict in that regard.

Discussion: https://github.com/prisma/prisma/discussions/6700 Issue about having a tool to "autofix" the database schema in that scenario: https://github.com/prisma/prisma/issues/6205

[tomhoule]

How to fix this?

• If you are using introspect only, manually aligning the types in the Prisma schema after introspection if they are not completely different should work well with client
• If you are using migrate, manually aligning the types in the Prisma schema should produce a migration that aligns them in the database, resolving the problem

[janpio]

Migrate use case was e.g. here: https://github.com/prisma/prisma/discussions/6700 What I told the user there should optimally be available via docs.

[jharrell]

👋 we're cleaning up our backlog of issues and are closing everything older than two years. If you still feel that this issue is relevant, please feel free to re-open and our team will take a look. Thank you!