英文:
Can I use Composite Primary Keys this way in Gorm? Why SQLSTATE 42830 error?
问题
我需要使用复合主键创建这些表:
type Tenant struct {
gorm.Model
Description string
}
type BaseModel struct {
gorm.Model
TenantID uint `gorm:"primaryKey"`
Tenant Tenant
}
type Player struct {
BaseModel
Name string
Teams []Team
}
type Team struct {
BaseModel
Name string
PlayerID uint
}
但是如果我使用:
myDB.AutoMigrate(
&Tenant{},
&Player{},
&Team{},
)
会报错:
C:/go/pkg/mod/gorm.io/driver/postgres@v1.1.0/migrator.go:157 ERROR: there is no unique constraint matching given keys for referenced table "players" (SQLSTATE 42830)
[15.960ms] [rows:0] CREATE TABLE "teams" ("id" bigserial,"created_at" timestamptz,"updated_at" timestamptz,"deleted_at" timestamptz,"tenant_id" bigint,"name" text,"player_id" bigint,PRIMARY KEY ("id","tenant_id"),CONSTRAINT "fk_teams_tenant" FOREIGN KEY ("tenant_id") REFERENCES "tenants"("id"),CONSTRAINT "fk_players_teams" FOREIGN KEY ("player_id") REFERENCES "players"("id"))
panic: ERROR: there is no unique constraint matching given keys for referenced table "players" (SQLSTATE 42830)
如果我从BaseModel
中移除Tenant
字段,它就可以工作。
我可以这样使用复合主键吗?
你能告诉我为什么它不起作用吗?
更新:
问题是它生成了以下内容:
CREATE TABLE "teams" (
"id" bigserial,
"created_at" timestamptz,
"updated_at" timestamptz,
"deleted_at" timestamptz,
"tenant_id" BIGINT,
"name" TEXT,
"player_id" BIGINT NOT NULL,
PRIMARY KEY ( "id", "tenant_id" ),
CONSTRAINT "fk_teams_tenant" FOREIGN KEY ( "tenant_id" ) REFERENCES "tenants" ( "id" ),
CONSTRAINT "fk_players_teams" FOREIGN KEY ("player_id") REFERENCES "players"("id")
)
而不是:
CREATE TABLE "teams" (
"id" bigserial,
"created_at" timestamptz,
"updated_at" timestamptz,
"deleted_at" timestamptz,
"tenant_id" BIGINT,
"name" TEXT,
"player_id" BIGINT NOT NULL,
PRIMARY KEY ( "id", "tenant_id" ),
CONSTRAINT "fk_teams_tenant" FOREIGN KEY ( "tenant_id" ) REFERENCES "tenants" ( "id" ),
CONSTRAINT "fk_players_teams" FOREIGN KEY ( "player_id", "tenant_id" ) REFERENCES "players" ( "id", "tenant_id" )
)
这是区别:
- CONSTRAINT "fk_players_teams" FOREIGN KEY ("player_id") REFERENCES "players"("id")
+ CONSTRAINT "fk_players_teams" FOREIGN KEY ( "player_id", "tenant_id" ) REFERENCES "players" ( "id", "tenant_id" )
这是我的问题吗?
英文:
I need to create these tables with composite primary keys:
type Tenant struct {
gorm.Model
Description string
}
type BaseModel struct {
gorm.Model
TenantID uint `gorm:"primaryKey"`
Tenant Tenant
}
type Player struct {
BaseModel
Name string
Teams []Team
}
type Team struct {
BaseModel
Name string
PlayerID uint
}
but If I use:
myDB.AutoMigrate(
&Tenant{},
&Player{},
&Team{},
)
it throws with:
C:/go/pkg/mod/gorm.io/driver/postgres@v1.1.0/migrator.go:157 ERROR: there is no unique constraint matching given keys for referenced table "players" (SQLSTATE 42830)
[15.960ms] [rows:0] CREATE TABLE "teams" ("id" bigserial,"created_at" timestamptz,"updated_at" timestamptz,"deleted_at" timestamptz,"tenant_id" bigint,"name" text,"player_id" bigint,PRIMARY KEY ("id","tenant_id"),CONSTRAINT "fk_teams_tenant" FOREIGN KEY ("tenant_id") REFERENCES "tenants"("id"),CONSTRAINT "fk_players_teams" FOREIGN KEY ("player_id") REFERENCES "players"("id"))
panic: ERROR: there is no unique constraint matching given keys for referenced table "players" (SQLSTATE 42830)
If I remove Tenant
from BaseModel
it works.
Can I use Composite Primary Key
this way?
Can you suggest me why it's not working?
UPDATE:
The problem it's generating this:
CREATE TABLE "teams" (
"id" bigserial,
"created_at" timestamptz,
"updated_at" timestamptz,
"deleted_at" timestamptz,
"tenant_id" BIGINT,
"name" TEXT,
"player_id" BIGINT NOT NULL,
PRIMARY KEY ( "id", "tenant_id" ),
CONSTRAINT "fk_teams_tenant" FOREIGN KEY ( "tenant_id" ) REFERENCES "tenants" ( "id" ),
CONSTRAINT "fk_players_teams" FOREIGN KEY ("player_id") REFERENCES "players"("id")
)
instead of:
CREATE TABLE "teams" (
"id" bigserial,
"created_at" timestamptz,
"updated_at" timestamptz,
"deleted_at" timestamptz,
"tenant_id" BIGINT,
"name" TEXT,
"player_id" BIGINT NOT NULL,
PRIMARY KEY ( "id", "tenant_id" ),
CONSTRAINT "fk_teams_tenant" FOREIGN KEY ( "tenant_id" ) REFERENCES "tenants" ( "id" ),
CONSTRAINT "fk_players_teams" FOREIGN KEY ( "player_id", "tenant_id" ) REFERENCES "players" ( "id", "tenant_id" )
)
This is the difference:
- CONSTRAINT "fk_players_teams" FOREIGN KEY ("player_id") REFERENCES "players"("id")
+ CONSTRAINT "fk_players_teams" FOREIGN KEY ( "player_id", "tenant_id" ) REFERENCES "players" ( "id", "tenant_id" )
Is this my fault?
答案1
得分: 1
Player需要一个额外的引用作为外键的主键:
type Player struct {
BaseModel
Name string
Teams []Team `gorm:"foreignKey:player_id,tenant_id"`
}
英文:
Player needs an additional reference to the primary keys as the foreign key:
type Player struct {
BaseModel
Name string
Teams []Team `gorm:"foreignKey:player_id,tenant_id"`
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论