diff options
Diffstat (limited to '2025/02/18')
| -rw-r--r-- | 2025/02/18/Prisma-关系型数据库的-Self-relations/index.html | 6 |
1 files changed, 3 insertions, 3 deletions
diff --git a/2025/02/18/Prisma-关系型数据库的-Self-relations/index.html b/2025/02/18/Prisma-关系型数据库的-Self-relations/index.html index fda4a344..b69d4397 100644 --- a/2025/02/18/Prisma-关系型数据库的-Self-relations/index.html +++ b/2025/02/18/Prisma-关系型数据库的-Self-relations/index.html @@ -213,7 +213,7 @@ <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</span> <span class="keyword">TABLE</span> "User" (</span><br><span class="line"> id SERIAL <span class="keyword">PRIMARY</span> KEY,</span><br><span class="line"> "name" TEXT,</span><br><span class="line"> "successorId" <span class="type">INTEGER</span></span><br><span class="line">);</span><br><span class="line"></span><br><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> "User" <span class="keyword">ADD</span> <span class="keyword">CONSTRAINT</span> fk_successor_user <span class="keyword">FOREIGN</span> KEY ("successorId") <span class="keyword">REFERENCES</span> "User" (id);</span><br><span class="line"></span><br><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> "User" <span class="keyword">ADD</span> <span class="keyword">CONSTRAINT</span> successor_unique <span class="keyword">UNIQUE</span> ("successorId");</span><br></pre></td></tr></table></figure> +<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> "User" (</span><br><span class="line"> id SERIAL <span class="keyword">PRIMARY KEY</span>,</span><br><span class="line"> "name" TEXT,</span><br><span class="line"> "successorId" <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> "User" <span class="keyword">ADD CONSTRAINT</span> fk_successor_user <span class="keyword">FOREIGN KEY</span> ("successorId") <span class="keyword">REFERENCES</span> "User" (id);</span><br><span class="line"></span><br><span class="line"><span class="keyword">ALTER TABLE</span> "User" <span class="keyword">ADD CONSTRAINT</span> successor_unique <span class="keyword">UNIQUE</span> ("successorId");</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 {</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("TeacherStudents", fields: [teacherId], references: [id])</span><br><span class="line"> students User[] @relation("TeacherStudents")</span><br><span class="line">}</span><br></pre></td></tr></table></figure> @@ -226,7 +226,7 @@ <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</span> <span class="keyword">TABLE</span> "User" (</span><br><span class="line"> id SERIAL <span class="keyword">PRIMARY</span> KEY,</span><br><span class="line"> "name" TEXT,</span><br><span class="line"> "teacherId" <span class="type">INTEGER</span></span><br><span class="line">);</span><br><span class="line"></span><br><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> "User" <span class="keyword">ADD</span> <span class="keyword">CONSTRAINT</span> fk_teacherid_user <span class="keyword">FOREIGN</span> KEY ("teacherId") <span class="keyword">REFERENCES</span> "User" (id);</span><br></pre></td></tr></table></figure> +<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> "User" (</span><br><span class="line"> id SERIAL <span class="keyword">PRIMARY KEY</span>,</span><br><span class="line"> "name" TEXT,</span><br><span class="line"> "teacherId" <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> "User" <span class="keyword">ADD CONSTRAINT</span> fk_teacherid_user <span class="keyword">FOREIGN KEY</span> ("teacherId") <span class="keyword">REFERENCES</span> "User" (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 {</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("UserFollows")</span><br><span class="line"> following User[] @relation("UserFollows")</span><br><span class="line">}</span><br></pre></td></tr></table></figure> @@ -243,7 +243,7 @@ <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 {</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("followedBy")</span><br><span class="line"> following Follows[] @relation("following")</span><br><span class="line">}</span><br><span class="line"></span><br><span class="line">model Follows {</span><br><span class="line"> followedBy User @relation("followedBy", fields: [followedById], references: [id])</span><br><span class="line"> followedById Int</span><br><span class="line"> following User @relation("following", 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">}</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</span> <span class="keyword">TABLE</span> "User" (</span><br><span class="line"> id <span class="type">integer</span> <span class="keyword">DEFAULT</span> nextval(<span class="string">'"User_id_seq"'</span>::regclass) <span class="keyword">PRIMARY</span> KEY,</span><br><span class="line"> name text</span><br><span class="line">);</span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> "_UserFollows" (</span><br><span class="line"> "A" <span class="type">integer</span> <span class="keyword">NOT</span> <span class="keyword">NULL</span> <span class="keyword">REFERENCES</span> "User"(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"> "B" <span class="type">integer</span> <span class="keyword">NOT</span> <span class="keyword">NULL</span> <span class="keyword">REFERENCES</span> "User"(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> +<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> "User" (</span><br><span class="line"> id <span class="type">integer</span> <span class="keyword">DEFAULT</span> nextval(<span class="string">'"User_id_seq"'</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> "_UserFollows" (</span><br><span class="line"> "A" <span class="type">integer</span> <span class="keyword">NOT NULL</span> <span class="keyword">REFERENCES</span> "User"(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"> "B" <span class="type">integer</span> <span class="keyword">NOT NULL</span> <span class="keyword">REFERENCES</span> "User"(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 {</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("TeacherStudents", fields: [teacherId], references: [id])</span><br><span class="line"> students User[] @relation("TeacherStudents")</span><br><span class="line"> followedBy User[] @relation("UserFollows")</span><br><span class="line"> following User[] @relation("UserFollows")</span><br><span class="line">}</span><br></pre></td></tr></table></figure> |