Go 语言编程 — gorm 的数据完整性约束

目录

前言

本文基于 PostgreSQL 和 GORM 1.9 版本。GORM v2 对下文中的不足进行了优化。

全新的 Migrator:允许为关系创建数据库外键,更智能的 AutoMigrate,支持约束、检查器,增强索引支持。

实体完整性(主键约束)

每个关系(表)至少存在一个主键(Primary Key),主键值必须唯一,且不允许为 NULL。

type Product struct {
	gorm.Model
    Code 	string `gorm:"primary_key"`
    Price 	uint
    ...
}

grom.Model 是 GORM 内建的 Struct,用于实现软删除,如下:

type Model struct {
    ID uint `gorm:"primary_key"`
    CreatedAt time.Time
    UpdatedAt time.Time
    DeletedAt *time.Time `sql:"index"`
}

可见,Model Struct Product 具有两个 primary_key:CONSTRAINT products_pkey PRIMARY KEY (code, id)

因此,GORM 实现了完全的实体完整性支持,即可以支持字段主键,也可以支持联合主键。

用户定义完整性(非空约束、唯一约束、检查约束和默认值)

又称为域完整性。指数据库表中的列必须满足某种特定的数据类型或约束,包括:字段类型、值域、小数位数、CHECK、FOREIGN KEY 约束和 DEFAULT、 NOT NULL。它们有的定义在字段上,有的定义在表上。例如:FOREIGN KEY 约束在 PostgresSQL 中,就是在表级别定义的;而字段类型、长度、小数位数就是在字段上定义的。

GORM 通过 Struct Tag 来支持用户定义完整性:

`gorm:"xxx"`

xxx 可以使用 type、size、precision、not null、default 等 Tags 类型。

其中 Check 约束需要使用到 sql tag,例如:

UserID uint `sql:"type:integer check(code!='')"`

它会被定义到表上:

ALTER TABLE public.products
    ADD CONSTRAINT products CHECK (code <> ''::text);

参照完整性(外键约束)

通过定义 Model Struct 创建了一个 products belongs to user 的 Belong to 一对一关系。

// 主表
type User struct {
    gorm.Model
    Code string `gorm:"primary_key"`
    Name string
}

// 从表
type Product struct {
    gorm.Model
    Code 	string `gorm:"primary_key"`
    Price 	uint
    UserID 	uint
    User 	User
}

AutoMigrate 的时候会执行 SQL 语句创建 products(从)表:

CREATE TABLE "products" 
(
	"code" text,
	"price" integer,
	"user_id" integer,
	"id" serial,
	"created_at" timestamp with time zone,
	"updated_at" timestamp with time zone,
	"deleted_at" timestamp with time zone , 
	PRIMARY KEY ("id")
)

可见,GORM 没有添加任何约束。按照 GORM 的文档,这就是 belongs to 的标准定义,它不添加外键约束

尝试显式的指定 foreignkey Tag:

type Product struct {
    Code 	string 	`gorm:"primary_key"`
    Price 	uint
    UserID 	uint
    User 	User	`gorm:"foreignkey:UserID;association_foreignkey:ID"`
    gorm.Model
}

type User struct {
    Code 	string `gorm:"primary_key"`
    Name 	string
    gorm.Model
}

执行的 SQL 是:

CREATE TABLE "products" 
(
	"code" text,
	"price" integer,
	"user_id" integer,
	"id" serial,
	"created_at" timestamp with time zone,
	"updated_at" timestamp with time zone,
	"deleted_at" timestamp with time zone , 
	PRIMARY KEY ("id")
)	 

可见,GORM 还是没有添加任何外键约束。

因此,可以确定 GORM 的 foreignkey、association_foreignkey tag 并不会添加外键约束

尝试显式指定 GORM 的 sql tag 来添加外键约束:

type Product struct {
    Code 	string	`gorm:"primary_key"`
    Price 	uint
    UserID 	uint 	`sql:"type:integer REFERENCES users(id) on update no action on delete no action"` // no action 模式外键约束
    User 	User 	`gorm:"foreignkey:UserID;association_foreignkey:ID"`
    gorm.Model
}

type User struct {
    Code string `gorm:"primary_key"`
    Name string
    gorm.Model
}

执行的 SQL 语句:

CREATE TABLE "products"
(
	"code" text,
	"price" integer,
	"user_id" integer REFERENCES users(id) on update no action on delete no action,
	"id" serial,"created_at" timestamp with time zone,
	"updated_at" timestamp with time zone,
	"deleted_at" timestamp with time zone , 
	PRIMARY KEY ("id")
)

可见,从表的外键约束被定义了。也就是说 GORM 1.9 版本如果希望创建表时定义外键(References,参照),那么就需要使用到 sql tag

注意,sql tag 与 gorm tag 有区别,前者需要硬编码相应的数据库 TableName和 ColumnName,而后者就只需要你使用结构体和其成员名即可。

除了 no action 模式之外,sql tag 同样支持:

  • CASCADE(级联)约束方式
UserID uint `sql:"type:integer REFERENCES users(id) on update cascade on delete cascade"`
  • SET NULL(设空)约束方式
  • RESTRICT(禁止)方式:在 PostgreSQl 中与 no action 具有类似的语义。

另外,使用 sql tag 还可以使用 constraint xxx 自定义外键约束名,即引用名称:

UserID uint `sql:"type:integer constraint ref_name REFERENCES users(id) on update no action on delete no action"`

同样的,GORM 也支持联合外键,这时候就需要使用到 GORM 提供的接口了:

db.Model(&Product{}).AddForeignKey( "user_id,user_code", "users(id,code)", "no action", "no action")

执行 SQL 语句:

CONSTRAINT products_user_id_user_code_users_id_code_foreign FOREIGN KEY (user_code, user_id)
	REFERENCES public.users (code, id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION

关联关系

一对一、一对多关联,多对多关联不属于完整性范畴,即:RDBMS 不会自动完成数据完整性检查,包括引用的可用性检查,数据的一致性检查等,这些工作都需要有应用层业务逻辑来实现。所以,在逻辑代码中也不需要实现任何完整性约束定义,因此 Model Struct 里也无需添加额外的约束。

一对一、一对多关联

type User struct {
    gorm.Model
    Code 	 string `gorm:"primary_key"`
    Name 	 string
    Products []Product
}

type Product struct {
    gorm.Model
    Code 	string `gorm:"primary_key"`
    Price 	uint
    UserID uint
}

这是典型的一对多定义,users 表无需添加约束字段,product 表也只需要添加 user_id 字段作为外键。这里可以省略,也可以显式的定义 gorm tag:foreignkey 或 association_foreignkey,例如:

type User struct {
    gorm.Model
    Code 	 string 	`gorm:"primary_key"`
    Name 	 string
    Products []Product  `gorm:"foreignkey:UserID"`
}

多对多关联

在关系型数据库中,多对多关系需要一张中间表。

type User struct {
    gorm.Model
    Code 	 string 	`gorm:"primary_key"`
    Name 	 string
    Products []Product  `gorm:"many2many:user_language"`
}

type Product struct {
    gorm.Model
    Code 	string `gorm:"primary_key"`
    Price 	uint
}

会执行 SQL:

CREATE TABLE "user_language"
(
	"user_id" integer,
	"product_id" integer,
	PRIMARY KEY ("user_id","product_id")
)

GORM 会自动创建一张 user_language 连接表(Join Table)。products、users 表的主键,被联合作为 user_language 表的主键。GORM 也会自动的完成 user_id 和 product_id 作为外键的关联。但正如上述所言,外键约束是不会自动完成的。

示例

// 文章表
type Article struct {
	ID 			int 		`json:"id"`
	Title 		string 		`json:"title"`
	CategoryId 	int 		`json:"category_id"`
	Category 	Category 	`json:"category";gorm:"foreignkey:CategoryID"` // 一对多关系
	Tag 		[]Tag 		`gorm:"many2many:article_tag" json:"tag"` // 多对多关系
}

// 文章_标签多对多中间表
// 默认的,article_id 字段对应 article 表 id,tag_id 字段对应 tag 表 id
type ArticleTag struct {
	ID 			int 	`json:"id"`
	ArticleId 	string 	`json:"article_id"`
	TagId 		string 	`json:"tag_id"`
}

// 标签表
type Tag struct {
	ID 		int 	`json:"id" `
  	TagName string 	`json:"tag_name"`
}

// 分类表
type Category struct {
	ID 			 int 	`json:"id"`
	CategoryName string `json:"category_name"`
	Status 		 int 	`json:"status"`
}
  • 查一列:
func (a *Article) ListArticle(title string) (Article, error) {
	query := database.GormPool

	var article Article
	query.Where("title like ?", "%"+title+"%").First(&article)
	fmt.Println(article)

	err := query.Model(&article).
		Related(&article.Category).
		Related(&article.Tag, "tag").
		Find(&article).Error

	if err != nil && err != gorm.ErrRecordNotFound {
		return article, nil
	}
	
	return article, err
}

通过 Related 方法,可以查找 belongs to、has one、has many、many to many 关系。

查找一列时,首先是需要先把特定的一条 Article 查询到,然后根据 Article 定义中指定的 CategoryID 去查找 Category 和 Tag。

  • 查多列表:
func (a *Article) ListArticle(title string) (articles []Article, err error) {
	query := database.GormPool
    
    err = query.Model(articles).
        Where("title like ?", "%"+title+"%").
        Preload("Category").
        Preload("Tag").Find(&articles).Error
    
    if err != nil && err != gorm.ErrRecordNotFound {
        return
    }
    return
}

查看多列时,使用 Preload 方法可以完成多表关系的预加载,然后再自动执行选择(WHERE)运算。

<div class="post-text" itemprop="text"> <p>I am trying to create a rest API with golang. Each time a user is created, I would like to create a 'Profile' which is associated with that user. </p> <p>My initial thought was to first create the user, and then separately create the profile referencing the user ID and inserting that into the database. </p> <p>I'm not sure if this kind of thinking aligns with the way go should be used as i'm just starting with the language. </p> <p>Using below code, I create the user, but cannot create the profile. I receive this error: <code>using unaddressable value</code></p> <pre><code>var db *gorm.DB func GetDB() *gorm.DB { return db } type User struct { gorm.Model Email string `gorm:"type:varchar(100);unique_index"` Password string `json:"password"` Name string `json:"name"` Token string `json:"token";sql:"-"` } type Profile struct { gorm.Model User User `gorm:"foreignkey:UserRefer"` // use UserRefer as foreign key UserRefer uint FirstName string `gorm:"default:'John'"` LastName string `gorm:"default:'Doe'"` Description string `gorm:"default:'Mysterious'"` } func (user *User) Create() (map[string]interface{}) { if resp, ok := user.Validate(); !ok { return resp } hashedPassword, _ := bcrypt.GenerateFromPassword([]byte(user.Password), bcrypt.DefaultCost) user.Password = string(hashedPassword) GetDB().Create(user) profile := Profile{} profile.UserRefer = user.ID GetDB().Create(profile) if user.ID <= 0 { return u.Message(false, "Failed to create account, connection error.") } response := u.Message(true, "Account has been created") response["user"] = user return response } </code></pre> <p>I'm hoping someone will be able to help me understand what is going wrong here?</p> </div>
<div class="post-text" itemprop="text"> <p>I'm testing out Go's GORM lib. I find this lib particularly useful and, step by step, I play with more and more complicated notions.</p> <p>I'm facing the problem of cascading operation management.</p> <p>On certain issues, the creator suggests to use the AfterDelete. The problem is : in the After/BeforeDelete functions, nested items are not present.</p> <p>Is everyone have a good way to implement this ?</p> <p>Here is my code (almost working if someone is discovering Gorm) :</p> <pre><code>package main import ( "time" "github.com/jinzhu/gorm" _ "github.com/jinzhu/gorm/dialects/sqlite" "fmt" "github.com/satori/go.uuid" ) type Company struct { ID string `gorm:"primary_key;column:ID"` Name string `sql:"size:255;unique;index" gorm:"column:Name"` Employees []Employee // one-to-many relationship Address Address // one-to-one relationship } func (u Company) TableName() string { return "Company" } func (u Company) String() string { return fmt.Sprintf("ID: %s | Name: %s | Employees: %v | Address: %v ", u.ID, u.Name, u.Employees, u.Address) } func (u *Company) BeforeCreate(scope *gorm.Scope) error { scope.SetColumn("ID", uuid.NewV4().String()) return nil } func (u *Company) BeforeDelete(scope *gorm.Scope) error { fmt.Println("BeforeDelete") fmt.Println(u) return nil } func (u *Company) AfterDelete(scope *gorm.Scope) error { fmt.Println("AfterDelete") fmt.Println(u) return nil } type Employee struct { ID string `gorm:"primary_key;column:ID"` FirstName string `gorm:"column:FirstName"` LastName string `gorm:"column:LastName"` SocialSecurityNo string `gorm:"column:SocialSecurityNo"` DateOfBirth time.Time `sql:"DEFAULT:current_timestamp" gorm:"column:DateOfBirth"` Deleted bool `sql:"DEFAULT:false" gorm:"column:Deleted"` CompanyID string `gorm:"column:Company_ID"` Roles []Role // one-to-many relationship } func (u Employee) TableName() string { return "Employee" } func (u Employee) String() string { return fmt.Sprintf("ID: %s | FirstName: %s | Roles: %v ", u.ID, u.FirstName, u.Roles) } func (u *Employee) BeforeCreate(scope *gorm.Scope) error { scope.SetColumn("ID", uuid.NewV4().String()) return nil } type Role struct { Name string `gorm:"column:Name"` Code string `gorm:"column:Code"` EmployeeID string `gorm:"column:Employee_ID"` } func (u Role) TableName() string { return "Role" } func (u Role) String() string { return fmt.Sprintf("Name: %s | Code: %s", u.Name, u.Code) } type Address struct { Country string `gorm:"column:Country"` City string `gorm:"column:City"` PostCode string `gorm:"column:PostCode"` Line1 string `gorm:"column:Line1"` Line2 string `gorm:"column:Line2"` CompanyID string `gorm:"column:Company_ID"` } func (u Address) TableName() string { return "Address" } func main() { db := getDBConnection() //If needed, you can create the file and schemas with the line below createTables(db) testCRUD(db) } func getDBConnection() (db *gorm.DB) { //Change the file location for your needs db, err := gorm.Open("sqlite3", `C:\Users\jbricout\Desktop\TestORM.db`) if err != nil { panic(err) } // Ping function checks the database connectivity err = db.DB().Ping() if err != nil { panic(err) } return db } func createTables(db *gorm.DB) { if err := db.CreateTable(&Company{}).Error; err != nil { checkErr(err) } if err := db.CreateTable(&Address{}).Error; err != nil { checkErr(err) } if err := db.CreateTable(&Employee{}).Error; err != nil { checkErr(err) } if err := db.CreateTable(&Role{}).Error; err != nil { checkErr(err) } } func testCRUD(db *gorm.DB) { sampleCompany := getInitializedCompany() fmt.Println("Insert...") if err := db.Create(&sampleCompany).Error; err != nil { checkErr(err) } fmt.Println("Insert done with id : ", sampleCompany.ID) fmt.Println("Find Only Company (Lazy load)...") var firstComp Company if err := db.Where("ID = ?", sampleCompany.ID).First(&firstComp).Error; err != nil { checkErr(err) } fmt.Println("Company : ", firstComp) fmt.Println("Find done") fmt.Println("Find Only Company (Eager load)...") var fullComp Company db.Preload("Employees.Roles").Preload("Address").First(&fullComp) if err := db.Where("ID = ?", sampleCompany.ID).First(&fullComp).Error; err != nil { checkErr(err) } fmt.Println("Company : ", fullComp) fmt.Println("Find done") fmt.Println("Update...") firstComp.Name = "Google Plus" if len(firstComp.Address.Country) > 0 { firstComp.Address.Country = "France" } if err := db.Save(&firstComp).Error; err != nil { checkErr(err) } fmt.Println("Update done") transaction := db.Begin() fmt.Println("Delete...") if err := transaction.Delete(&firstComp).Error; err != nil { checkErrTransaction(err, transaction) } transaction.Commit() fmt.Println("Delete done") } func getInitializedCompany() Company { return Company{ Name: "Google", Address: Address{ Country: "USA", City: "Moutain View", PostCode: "1600", Line1: "Cloverfield Lane, 32", Line2: "Apt 64", }, Employees: []Employee{ Employee{ FirstName: "John", LastName: "Doe", SocialSecurityNo: "00-000-0000", Roles: []Role{ Role{ Name: "Metier 1", Code: "MET1", }, Role{ Name: "Metier 2", Code: "MET2", }, }, }, Employee{ FirstName: "James", LastName: "Dean", SocialSecurityNo: "00-000-0001", Roles: []Role{ Role{ Name: "Metier 1", Code: "MET1", }, }, }, Employee{ FirstName: "Joan", LastName: "Dutsch", SocialSecurityNo: "00-000-0002", Roles: []Role{ Role{ Name: "Metier 2", Code: "MET3", }, }, }, }, } } func checkErr(err error) { if err != nil { panic(err) } } func checkErrTransaction(err error, transaction *gorm.DB) { transaction.Rollback() if err != nil { panic(err) } } </code></pre> <p>Thanks</p> </div>
©️2020 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页
实付 49.00元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值