From ad8f9f55ea79a4d587aecc69698808da82de3d62 Mon Sep 17 00:00:00 2001 From: somhairle Date: Tue, 21 Apr 2026 20:40:27 +0800 Subject: 二〇二六年四月二十一日 MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit --- ...\256\345\272\223\347\232\204-Self-relations.md" | 145 +++++++++++++++++++++ 1 file changed, 145 insertions(+) create mode 100644 "source/_posts/Prisma-\345\205\263\347\263\273\345\236\213\346\225\260\346\215\256\345\272\223\347\232\204-Self-relations.md" (limited to 'source/_posts/Prisma-关系型数据库的-Self-relations.md') diff --git "a/source/_posts/Prisma-\345\205\263\347\263\273\345\236\213\346\225\260\346\215\256\345\272\223\347\232\204-Self-relations.md" "b/source/_posts/Prisma-\345\205\263\347\263\273\345\236\213\346\225\260\346\215\256\345\272\223\347\232\204-Self-relations.md" new file mode 100644 index 00000000..75e9ccde --- /dev/null +++ "b/source/_posts/Prisma-\345\205\263\347\263\273\345\236\213\346\225\260\346\215\256\345\272\223\347\232\204-Self-relations.md" @@ -0,0 +1,145 @@ +--- +title: Prisma 关系型数据库的 Self-relations +date: 2025-02-18 13:45:35 +tags: [Technique] +--- + +> A relation field can also reference its own model, in this case the relation is called a _self-relation_. Self-relations can be of any cardinality, 1-1, 1-n and m-n. + +## 一对一 +```prisma +model User { + id Int @id @default(autoincrement()) + name String? + successorId Int? @unique + successor User? @relation("BlogOwnerHistory", fields: [successorId], references: [id]) + predecessor User? @relation("BlogOwnerHistory") +} +``` + +User 展现了这样一个模型： +- User 可以有一个或零个前驱（predecessor） +- User 可以有一个或零个后继（successor） + +注意：不能要求前驱和后继都必须存在，这两个必须有一个是可选的，否则没办法创建第一个 User。 + +要创建一对一的 self-relation： +- 关系的两端都必须定义一个共享相同名称的 `@relation` 属性（`BlogOwnerHistory`） +- 关系字段必须是[完全注释](https://www.prisma.io/docs/orm/prisma-schema/data-model/relations#relation-fields)的。例如 `successor` 字段需要定义 `field` 和 `references` 参数。 +- 关系字段必须由外键支持。`successor` 字段由 `successorId` 外键提供支持，该外键引用 `id` 字段中的值。`successorId` 还需要 `@unique` 属性来保证一对一的关系。 + +> 一对一的 self-relation 需要两个端点，即使这两个端点是同一条数据。 + +而在关系型数据库中，一对一的 self-relation 可以用如下 SQL 描述： +```sql +CREATE TABLE "User" ( + id SERIAL PRIMARY KEY, + "name" TEXT, + "successorId" INTEGER +); + +ALTER TABLE "User" ADD CONSTRAINT fk_successor_user FOREIGN KEY ("successorId") REFERENCES "User" (id); + +ALTER TABLE "User" ADD CONSTRAINT successor_unique UNIQUE ("successorId"); +``` + +## 一对多 +```prisma +model User { + id Int @id @default(autoincrement()) + name String? + teacherId Int? + teacher User? @relation("TeacherStudents", fields: [teacherId], references: [id]) + students User[] @relation("TeacherStudents") +} +``` + +User 展现了这样一个模型： +- 一个 User 只能有零个或一个 teacher +- 一个 User 可以有零个或多个 students + +> 可以通过将 `teacher` 字段设[为 required](https://www.prisma.io/docs/orm/prisma-schema/data-model/models#optional-and-mandatory-fields) 来要求每个 User 都有一名 teacher。 + +用 SQL 描述 User model： + +```sql +CREATE TABLE "User" ( + id SERIAL PRIMARY KEY, + "name" TEXT, + "teacherId" INTEGER +); + +ALTER TABLE "User" ADD CONSTRAINT fk_teacherid_user FOREIGN KEY ("teacherId") REFERENCES "User" (id); +``` + +`teacherId` 没有使用 `UNIQUE` 约束，这代表着多个 students 可以有同一个 teacher + +## 多对多 +``` +model User { + id Int @id @default(autoincrement()) + name String? + followedBy User[] @relation("UserFollows") + following User[] @relation("UserFollows") +} +``` + +- 一个 User 可以被零个或多个 Users 关注 +- 一个 User 可以关注零个或多个 Users + +> 对于关系型数据库，多对多的关系是隐式的，这意味着 Prisma ORM 会在底层数据库中维护一个 [relation table](https://www.prisma.io/docs/orm/prisma-schema/data-model/relations/many-to-many-relations#relation-tables): +> A relation table (also sometimes called a _JOIN_, _link_ or _pivot_ table) connects two or more other tables and therefore creates a _relation_ between them. Creating relation tables is a common data modelling practice in SQL to represent relationships between different entities. In essence it means that "one m-n relation is modeled as two 1-n relations in the database". +> +> We recommend using [implicit](https://www.prisma.io/docs/orm/prisma-schema/data-model/relations/many-to-many-relations#implicit-many-to-many-relations) m-n-relations, where Prisma ORM automatically generates the relation table in the underlying database. [Explicit](https://www.prisma.io/docs/orm/prisma-schema/data-model/relations/many-to-many-relations#explicit-many-to-many-relations) m-n-relations should be used when you need to store additional data in the relations, such as the date the relation was created. + +如果需要需要通过多对多的关系来保存其他字段，也可以创建[显式](https://www.prisma.io/docs/orm/prisma-schema/data-model/relations/many-to-many-relations#explicit-many-to-many-relations)的多对多 self 关系： +```prisma +model User { + id Int @id @default(autoincrement()) + name String? + followedBy Follows[] @relation("followedBy") + following Follows[] @relation("following") +} + +model Follows { + followedBy User @relation("followedBy", fields: [followedById], references: [id]) + followedById Int + following User @relation("following", fields: [followingId], references: [id]) + followingId Int + + @@id([followingId, followedById]) +} +``` + +在关系型数据库中，可以用如下 SQL 描述: + +```sql +CREATE TABLE "User" ( + id integer DEFAULT nextval('"User_id_seq"'::regclass) PRIMARY KEY, + name text +); +CREATE TABLE "_UserFollows" ( + "A" integer NOT NULL REFERENCES "User"(id) ON DELETE CASCADE ON UPDATE CASCADE, + "B" integer NOT NULL REFERENCES "User"(id) ON DELETE CASCADE ON UPDATE CASCADE +); +``` + +## 在同一模型上建立多个 self-relations +```prisma +model User { + id Int @id @default(autoincrement()) + name String? + teacherId Int? + teacher User? @relation("TeacherStudents", fields: [teacherId], references: [id]) + students User[] @relation("TeacherStudents") + followedBy User[] @relation("UserFollows") + following User[] @relation("UserFollows") +} +``` + +## REFS. +- [fully annotated](https://www.prisma.io/docs/orm/prisma-schema/data-model/relations#relation-fields) +- [required](https://www.prisma.io/docs/orm/prisma-schema/data-model/models#optional-and-mandatory-fields) +- [implicit](https://www.prisma.io/docs/orm/prisma-schema/data-model/relations/many-to-many-relations#implicit-many-to-many-relations) +- [relation table](https://www.prisma.io/docs/orm/prisma-schema/data-model/relations/many-to-many-relations#relation-tables) +- [explicit](https://www.prisma.io/docs/orm/prisma-schema/data-model/relations/many-to-many-relations#explicit-many-to-many-relations) -- cgit v1.2.3