在Gorm中可以使用复合主键吗?为什么会出现SQLSTATE 42830错误?

huangapple go评论86阅读模式
英文:

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"`
    }

huangapple
  • 本文由 发表于 2021年5月31日 01:36:25
  • 转载请务必保留本文链接:https://go.coder-hub.com/67764075.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定