1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
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)
|
