summaryrefslogtreecommitdiff
path: root/2025/02/18/Prisma-关系型数据库的-Self-relations/index.html
blob: b69d43977579bc5427cc840ca1dc79fd90c16b56 (plain)
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
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
<!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="ie=edge">

    <meta name="author" content="韩暮秋">
    
        
            <meta name="subtitle" content="暮秋小屋">
            
                
                    <meta name="description" content="这里是暮秋小屋,思念和灵感的寄存处">
                    
                        
                            <meta name="keywords" content="韩暮秋,MuqiuHan,'Muqiu Han', 'muqiu han', muqiuhan">
                            

                                

                                    <title>
                                        
                                            Prisma 关系型数据库的 Self-relations | 
                                                    暮秋小屋
                                    </title>

                                    
                                        
                                            <link rel="icon" href="/favicon.ico">
                                            

                                                <style>
                                                    @font-face {
                                                        font-family: CarroisSong;
                                                        src: url('/fonts/CarroisSong.ttf');
                                                    }
                                                </style>

                                                
                                                    
                                                        <!-- stylesheets list from _config.yml -->
                                                        
                                                            <link rel="stylesheet" href="/css/style.css">
                                                            
                                                                

                                                                    
                                                                        
                                                                            <!-- scripts list from _config.yml -->
                                                                            
                                                                                <script
                                                                                    src="/js/menu.js"></script>
                                                                                
                                                                                    

                                                                                        
                                                                                            
                                                                                                <script
                                                                                                    src="https://polyfill.alicdn.com/polyfill.js?features=es6"></script>
                                                                                                <script
                                                                                                    id="MathJax-script"
                                                                                                    async
                                                                                                    src="https://lf6-cdn-tos.bytecdntp.com/cdn/expire-1-M/mathjax/3.2.0/es5/tex-mml-chtml.js"></script>
                                                                                                

                                                                                                    
                                                                                                        
  <meta name="generator" content="Hexo 6.3.0"></head>
  <body>
    <div class="mask-border">
    </div>

    <div class="wrapper">

      <div class="header">
  <div class="flex-container">
    <div class="header-inner">
      <div class="site-brand-container">
        <a href="/">
          
              暮秋小屋
                
        </a>
      </div>
      <div id="menu-btn" class="menu-btn" onclick="toggleMenu()">
        Menu
      </div>
      <nav class="site-nav">
        <ul class="menu-list">
          
            
                <li class="menu-item">
                  <a href="/">
                    主页
                  </a>
                </li>
                
                  
            
                <li class="menu-item">
                  <a href="/categories/gallery/">
                    日记本
                  </a>
                </li>
                
                  
            
                <li class="menu-item">
                  <a href="/tags/Medicine/">
                    医学
                  </a>
                </li>
                
                  
            
                <li class="menu-item">
                  <a href="/tags/Technique/">
                    计算机/互联网
                  </a>
                </li>
                
                  
            
                <li class="menu-item">
                  <a href="/tags/Life/">
                    生活
                  </a>
                </li>
                
                  
            
                <li class="menu-item">
                  <a href="/archives">
                    全部
                  </a>
                </li>
                
                  
            
                <li class="menu-item">
                  <a href="/about">
                    关于
                  </a>
                </li>
                
                  
                    
                      <li class="menu-item search-btn">
                        <a href="#">Search</a>
                      </li>
                      
        </ul>
      </nav>
    </div>
  </div>
</div>

      <div class="main">
        <div class="flex-container">
          <article id="post">

  
    <div class="post-head">
    <div class="post-info">
        <div class="tag-list">
            
                
                    <span class="post-tag">
                        <a href="/tags/Technique/">
                            Technique
                        </a>
                    </span>    
                           
            
        </div>
        <div class="post-title">
            
            
                Prisma 关系型数据库的 Self-relations
            
            
        </div>
        <span class="post-date">
            Feb 18, 2025
        </span>
    </div>
    <div class="post-img">
        
            <div class="h-line-primary"></div>
              
    </div>
</div>
    <div class="post-content">
    <blockquote>
<p>A relation field can also reference its own model, in this case the relation is called a <em>self-relation</em>. Self-relations can be of any cardinality, 1-1, 1-n and m-n.</p>
</blockquote>
<h2 id="一对一"><a href="#一对一" class="headerlink" title="一对一"></a>一对一</h2><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">model User &#123;</span><br><span class="line">  id          Int     @id @default(autoincrement())</span><br><span class="line">  name        String?</span><br><span class="line">  successorId Int?    @unique</span><br><span class="line">  successor   User?   @relation(&quot;BlogOwnerHistory&quot;, fields: [successorId], references: [id])</span><br><span class="line">  predecessor User?   @relation(&quot;BlogOwnerHistory&quot;)</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>

<p>User 展现了这样一个模型:</p>
<ul>
<li>User 可以有一个或零个前驱(predecessor)</li>
<li>User 可以有一个或零个后继(successor)</li>
</ul>
<p>注意:不能要求前驱和后继都必须存在,这两个必须有一个是可选的,否则没办法创建第一个 User。</p>
<p>要创建一对一的 self-relation:</p>
<ul>
<li>关系的两端都必须定义一个共享相同名称的 <code>@relation</code> 属性(<code>BlogOwnerHistory</code>)</li>
<li>关系字段必须是<a target="_blank" rel="noopener" href="https://www.prisma.io/docs/orm/prisma-schema/data-model/relations#relation-fields">完全注释</a>的。例如 <code>successor</code> 字段需要定义 <code>field</code> 和 <code>references</code> 参数。</li>
<li>关系字段必须由外键支持。<code>successor</code> 字段由 <code>successorId</code> 外键提供支持,该外键引用 <code>id</code> 字段中的值。<code>successorId</code> 还需要 <code>@unique</code> 属性来保证一对一的关系。****</li>
</ul>
<blockquote>
<p>一对一的 self-relation 需要两个端点,即使这两个端点是同一条数据。</p>
</blockquote>
<p>而在关系型数据库中,一对一的 self-relation 可以用如下 SQL 描述:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE TABLE</span> &quot;User&quot; (</span><br><span class="line">    id SERIAL <span class="keyword">PRIMARY KEY</span>,</span><br><span class="line">    &quot;name&quot; TEXT,</span><br><span class="line">    &quot;successorId&quot; <span class="type">INTEGER</span></span><br><span class="line">);</span><br><span class="line"></span><br><span class="line"><span class="keyword">ALTER TABLE</span> &quot;User&quot; <span class="keyword">ADD CONSTRAINT</span> fk_successor_user <span class="keyword">FOREIGN KEY</span> (&quot;successorId&quot;) <span class="keyword">REFERENCES</span> &quot;User&quot; (id);</span><br><span class="line"></span><br><span class="line"><span class="keyword">ALTER TABLE</span> &quot;User&quot; <span class="keyword">ADD CONSTRAINT</span> successor_unique <span class="keyword">UNIQUE</span> (&quot;successorId&quot;);</span><br></pre></td></tr></table></figure>

<h2 id="一对多"><a href="#一对多" class="headerlink" title="一对多"></a>一对多</h2><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">model User &#123;</span><br><span class="line">  id        Int     @id @default(autoincrement())</span><br><span class="line">  name      String?</span><br><span class="line">  teacherId Int?</span><br><span class="line">  teacher   User?   @relation(&quot;TeacherStudents&quot;, fields: [teacherId], references: [id])</span><br><span class="line">  students  User[]  @relation(&quot;TeacherStudents&quot;)</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>

<p>User 展现了这样一个模型:</p>
<ul>
<li>一个 User 只能有零个或一个 teacher</li>
<li>一个 User 可以有零个或多个 students</li>
</ul>
<blockquote>
<p>可以通过将 <code>teacher</code> 字段设<a target="_blank" rel="noopener" href="https://www.prisma.io/docs/orm/prisma-schema/data-model/models#optional-and-mandatory-fields">为 required</a> 来要求每个 User 都有一名 teacher。</p>
</blockquote>
<p>用 SQL 描述 User model:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE TABLE</span> &quot;User&quot; (</span><br><span class="line">    id SERIAL <span class="keyword">PRIMARY KEY</span>,</span><br><span class="line">    &quot;name&quot; TEXT,</span><br><span class="line">    &quot;teacherId&quot; <span class="type">INTEGER</span></span><br><span class="line">);</span><br><span class="line"></span><br><span class="line"><span class="keyword">ALTER TABLE</span> &quot;User&quot; <span class="keyword">ADD CONSTRAINT</span> fk_teacherid_user <span class="keyword">FOREIGN KEY</span> (&quot;teacherId&quot;) <span class="keyword">REFERENCES</span> &quot;User&quot; (id);</span><br></pre></td></tr></table></figure>

<p><code>teacherId</code> 没有使用 <code>UNIQUE</code> 约束,这代表着多个 students 可以有同一个 teacher</p>
<h2 id="多对多"><a href="#多对多" class="headerlink" title="多对多"></a>多对多</h2><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">model User &#123;</span><br><span class="line">  id         Int     @id @default(autoincrement())</span><br><span class="line">  name       String?</span><br><span class="line">  followedBy User[]  @relation(&quot;UserFollows&quot;)</span><br><span class="line">  following  User[]  @relation(&quot;UserFollows&quot;)</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>

<ul>
<li>一个 User 可以被零个或多个 Users 关注</li>
<li>一个 User 可以关注零个或多个 Users</li>
</ul>
<blockquote>
<p>对于关系型数据库,多对多的关系是隐式的,这意味着 Prisma ORM 会在底层数据库中维护一个 <a target="_blank" rel="noopener" href="https://www.prisma.io/docs/orm/prisma-schema/data-model/relations/many-to-many-relations#relation-tables">relation table</a>:<br>A relation table (also sometimes called a <em>JOIN</em>, <em>link</em> or <em>pivot</em> table) connects two or more other tables and therefore creates a <em>relation</em> 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”.</p>
<p>We recommend using <a target="_blank" rel="noopener" href="https://www.prisma.io/docs/orm/prisma-schema/data-model/relations/many-to-many-relations#implicit-many-to-many-relations">implicit</a> m-n-relations, where Prisma ORM automatically generates the relation table in the underlying database. <a target="_blank" rel="noopener" href="https://www.prisma.io/docs/orm/prisma-schema/data-model/relations/many-to-many-relations#explicit-many-to-many-relations">Explicit</a> m-n-relations should be used when you need to store additional data in the relations, such as the date the relation was created.</p>
</blockquote>
<p>如果需要需要通过多对多的关系来保存其他字段,也可以创建<a target="_blank" rel="noopener" href="https://www.prisma.io/docs/orm/prisma-schema/data-model/relations/many-to-many-relations#explicit-many-to-many-relations">显式</a>的多对多 self 关系:</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><span class="line">model User &#123;</span><br><span class="line">  id         Int       @id @default(autoincrement())</span><br><span class="line">  name       String?</span><br><span class="line">  followedBy Follows[] @relation(&quot;followedBy&quot;)</span><br><span class="line">  following  Follows[] @relation(&quot;following&quot;)</span><br><span class="line">&#125;</span><br><span class="line"></span><br><span class="line">model Follows &#123;</span><br><span class="line">  followedBy   User @relation(&quot;followedBy&quot;, fields: [followedById], references: [id])</span><br><span class="line">  followedById Int</span><br><span class="line">  following    User @relation(&quot;following&quot;, fields: [followingId], references: [id])</span><br><span class="line">  followingId  Int</span><br><span class="line"></span><br><span class="line">  @@id([followingId, followedById])</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>

<p>在关系型数据库中,可以用如下 SQL 描述:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE TABLE</span> &quot;User&quot; (</span><br><span class="line">    id <span class="type">integer</span> <span class="keyword">DEFAULT</span> nextval(<span class="string">&#x27;&quot;User_id_seq&quot;&#x27;</span>::regclass) <span class="keyword">PRIMARY KEY</span>,</span><br><span class="line">    name text</span><br><span class="line">);</span><br><span class="line"><span class="keyword">CREATE TABLE</span> &quot;_UserFollows&quot; (</span><br><span class="line">    &quot;A&quot; <span class="type">integer</span> <span class="keyword">NOT NULL</span> <span class="keyword">REFERENCES</span> &quot;User&quot;(id) <span class="keyword">ON</span> <span class="keyword">DELETE</span> CASCADE <span class="keyword">ON</span> <span class="keyword">UPDATE</span> CASCADE,</span><br><span class="line">    &quot;B&quot; <span class="type">integer</span> <span class="keyword">NOT NULL</span> <span class="keyword">REFERENCES</span> &quot;User&quot;(id) <span class="keyword">ON</span> <span class="keyword">DELETE</span> CASCADE <span class="keyword">ON</span> <span class="keyword">UPDATE</span> CASCADE</span><br><span class="line">);</span><br></pre></td></tr></table></figure>

<h2 id="在同一模型上建立多个-self-relations"><a href="#在同一模型上建立多个-self-relations" class="headerlink" title="在同一模型上建立多个 self-relations"></a>在同一模型上建立多个 self-relations</h2><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">model User &#123;</span><br><span class="line">  id         Int     @id @default(autoincrement())</span><br><span class="line">  name       String?</span><br><span class="line">  teacherId  Int?</span><br><span class="line">  teacher    User?   @relation(&quot;TeacherStudents&quot;, fields: [teacherId], references: [id])</span><br><span class="line">  students   User[]  @relation(&quot;TeacherStudents&quot;)</span><br><span class="line">  followedBy User[]  @relation(&quot;UserFollows&quot;)</span><br><span class="line">  following  User[]  @relation(&quot;UserFollows&quot;)</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>

<h2 id="REFS"><a href="#REFS" class="headerlink" title="REFS."></a>REFS.</h2><ul>
<li><a target="_blank" rel="noopener" href="https://www.prisma.io/docs/orm/prisma-schema/data-model/relations#relation-fields">fully annotated</a></li>
<li><a target="_blank" rel="noopener" href="https://www.prisma.io/docs/orm/prisma-schema/data-model/models#optional-and-mandatory-fields">required</a></li>
<li><a target="_blank" rel="noopener" href="https://www.prisma.io/docs/orm/prisma-schema/data-model/relations/many-to-many-relations#implicit-many-to-many-relations">implicit</a></li>
<li><a target="_blank" rel="noopener" href="https://www.prisma.io/docs/orm/prisma-schema/data-model/relations/many-to-many-relations#relation-tables">relation table</a></li>
<li><a target="_blank" rel="noopener" href="https://www.prisma.io/docs/orm/prisma-schema/data-model/relations/many-to-many-relations#explicit-many-to-many-relations">explicit</a></li>
</ul>

</div> 

<script>
    window.onload = detectors();
</script>
    <div class="post-footer">
    <div class="h-line-primary"></div>
    <nav class="post-nav">
        <div class="prev-item">
           
                <div class="icon arrow-left"></div>
                <div class="post-link">
                    <a href="/2025/02/19/Repository-pattern-in-Typescript/">Prev</a>
                </div>
            
        </div>
        <div class="next-item">
            
                <div class="icon arrow-right"></div>
                <div class="post-link">
                  <a href="/2025/02/11/%E4%BB%8E%E4%BA%8B%E4%BB%B6%E9%A3%8E%E6%9A%B4%E7%9C%8B%E9%A2%86%E5%9F%9F%E9%A9%B1%E5%8A%A8%E8%AE%BE%E8%AE%A1/">Next</a>  
                </div>  
            
        </div>
    </nav>
</div>

    
      <div class="post-comment">

     

     
    
    

</div>
     
  
</article>
        </div>
      </div>
      
      <div class="footer">
    <div class="flex-container">
        <div class="footer-text">
            
            
                韩暮秋 | 
            
            
                希望路过的人可以添点柴火让这里暖和点
                
        </div>
    </div>
</div>

    </div>

    
      <div class="search-popup">
    <div class="search-popup-overlay">  
    </div>
    <div class="search-popup-window" >
        <div class="search-header">
            <div class="search-input-container">
              <input autocomplete="off" autocapitalize="off" maxlength="80"
                     placeholder="Search Anything" spellcheck="false"
                     type="search" class="search-input">
            </div>
            <div class="search-close-btn">
                <div class="icon close-btn"></div>
            </div>
        </div>
        <div class="search-result-container">
        </div>
    </div>
</div>

<script>
    const searchConfig = {
        path             : "/search.xml",
        top_n_per_article: "1",
        unescape         : "false",
        trigger: "auto",
        preload: "false"
    }
</script>
<script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/search.js"></script>
<script src="/js/search.js"></script>
    
    

  </body>
</html>