From 3fa50e79f12fe2b66ebd5d643ed597c6a121b198 Mon Sep 17 00:00:00 2001 From: muqiuhan Date: Sat, 3 May 2025 10:47:05 +0000 Subject: deploy: a0dad147ccf1d831c5f48d8d0e868160a23cc92c --- .../index.html" | 6 +++--- 1 file changed, 3 insertions(+), 3 deletions(-) (limited to '2025/02/18/Prisma-关系型数据库的-Self-relations/index.html') diff --git "a/2025/02/18/Prisma-\345\205\263\347\263\273\345\236\213\346\225\260\346\215\256\345\272\223\347\232\204-Self-relations/index.html" "b/2025/02/18/Prisma-\345\205\263\347\263\273\345\236\213\346\225\260\346\215\256\345\272\223\347\232\204-Self-relations/index.html" index fda4a344..b69d4397 100644 --- "a/2025/02/18/Prisma-\345\205\263\347\263\273\345\236\213\346\225\260\346\215\256\345\272\223\347\232\204-Self-relations/index.html" +++ "b/2025/02/18/Prisma-\345\205\263\347\263\273\345\236\213\346\225\260\346\215\256\345\272\223\347\232\204-Self-relations/index.html" @@ -213,7 +213,7 @@

一对一的 self-relation 需要两个端点，即使这两个端点是同一条数据。

而在关系型数据库中，一对一的 self-relation 可以用如下 SQL 描述：

-
1
2
3
4
5
6
7
8
9
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");
+
1
2
3
4
5
6
7
8
9
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");

一对多

1
2
3
4
5
6
7
model User {
  id        Int     @id @default(autoincrement())
  name      String?
  teacherId Int?
  teacher   User?   @relation("TeacherStudents", fields: [teacherId], references: [id])
  students  User[]  @relation("TeacherStudents")
}
@@ -226,7 +226,7 @@

可以通过将 teacher 字段设为 required 来要求每个 User 都有一名 teacher。

用 SQL 描述 User model：

-
1
2
3
4
5
6
7
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);
+
1
2
3
4
5
6
7
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

多对多

1
2
3
4
5
6
model User {
  id         Int     @id @default(autoincrement())
  name       String?
  followedBy User[]  @relation("UserFollows")
  following  User[]  @relation("UserFollows")
}
@@ -243,7 +243,7 @@ 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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 描述:

-
1
2
3
4
5
6
7
8
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
);
+
1
2
3
4
5
6
7
8
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

1
2
3
4
5
6
7
8
9
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")
}
-- cgit v1.2.3