diff options
| author | muqiuhan <[email protected]> | 2025-09-09 06:17:00 +0000 |
|---|---|---|
| committer | muqiuhan <[email protected]> | 2025-09-09 06:17:00 +0000 |
| commit | d5de65fdb1802cdf498d65d93397f290813c377c (patch) | |
| tree | 92fe61a76d20d1203203665d4b48e7f151f088bd /2025/02/18/Prisma-关系型数据库的-Self-relations | |
| parent | 48efa2dfde7c263f84ee5bb0872747034908d607 (diff) | |
| download | blog-d5de65fdb1802cdf498d65d93397f290813c377c.tar.gz | |
deploy: 9665097f0fa0dae9f92123fac54d26c0818758a5
Diffstat (limited to '2025/02/18/Prisma-关系型数据库的-Self-relations')
| -rw-r--r-- | 2025/02/18/Prisma-关系型数据库的-Self-relations/index.html | 26 |
1 files changed, 12 insertions, 14 deletions
diff --git a/2025/02/18/Prisma-关系型数据库的-Self-relations/index.html b/2025/02/18/Prisma-关系型数据库的-Self-relations/index.html index 0eac083b..0916a06e 100644 --- a/2025/02/18/Prisma-关系型数据库的-Self-relations/index.html +++ b/2025/02/18/Prisma-关系型数据库的-Self-relations/index.html @@ -195,8 +195,8 @@ <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 {</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("BlogOwnerHistory", fields: [successorId], references: [id])</span><br><span class="line"> predecessor User? @relation("BlogOwnerHistory")</span><br><span class="line">}</span><br></pre></td></tr></table></figure> - +<h2 id="一对一"><a class="header-anchor" href="#一对一">¶</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"> successorId Int? @unique</span><br><span class="line"> successor User? @relation("BlogOwnerHistory", fields: [successorId], references: [id])</span><br><span class="line"> predecessor User? @relation("BlogOwnerHistory")</span><br><span class="line">}</span><br></pre></td></tr></table></figure> <p>User 展现了这样一个模型：</p> <ul> <li>User 可以有一个或零个前驱（predecessor）</li> @@ -214,9 +214,8 @@ </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> "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> - +<h2 id="一对多"><a class="header-anchor" href="#一对多">¶</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> <p>User 展现了这样一个模型：</p> <ul> <li>一个 User 只能有零个或一个 teacher</li> @@ -227,27 +226,26 @@ </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> "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> - +<h2 id="多对多"><a class="header-anchor" href="#多对多">¶</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> <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>对于关系型数据库，多对多的关系是隐式的，这意味着 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 {</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 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> - -<h2 id="REFS"><a href="#REFS" class="headerlink" title="REFS."></a>REFS.</h2><ul> +<h2 id="在同一模型上建立多个-self-relations"><a class="header-anchor" href="#在同一模型上建立多个-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> +<h2 id="REFS"><a class="header-anchor" href="#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> |