sqlite driver got error table "xxx" has more than one primary key

[Kunniii]

Your Question

Got error saying table "users" has more than one primary key

2023/12/22 22:02:05 [...] table "users" has more than one primary key
[0.031ms] [rows:0] CREATE TABLE `users` (`id` integer PRIMARY KEY AUTOINCREMENT,`created_at` datetime,`updated_at` datetime,`deleted_at` datetime,`user_name` text,`email` text UNIQUE,PRIMARY KEY (`id`))
2023/12/22 22:02:05 table "users" has more than one primary key
exit status 1

Here is my code

package main

import (
	"log"

	"gorm.io/driver/sqlite"
	"gorm.io/gorm"
)

type User struct {
	gorm.Model
	UserName string
	Email    string `gorm:"unique"`
	Posts    []Post
}

type Post struct {
	gorm.Model
	UserID  uint
	Content string
}

func main() {
	DB, _ := gorm.Open(sqlite.Open("gorm.sqlite"), &gorm.Config{})

	if err := DB.AutoMigrate(&User{}, &Post{}); err != nil {
		log.Fatal(err)
	}
}

Here is my go.mod

module local/go_gorm_test

go 1.21.3

require (
	gorm.io/driver/sqlite v1.5.4
	gorm.io/gorm v1.25.2-0.20230530020048-26663ab9bf55
)

require (
	github.com/jinzhu/inflection v1.0.0 // indirect
	github.com/jinzhu/now v1.1.5 // indirect
	github.com/mattn/go-sqlite3 v1.14.17 // indirect
)

The Model structure above works fine when I migrate using the PostgreSQL driver. I don't know if there is any special config for the SQLite driver to work. Please help!

The document you expected this should be explained

I don't know if this is a real problem or just my mis-config.

But if there are some special configs for SQLite driver to work, this should be included in either Migration or Drivers docs.

Expected answer

I appreciate your answer, please let me know if I have any mistakes!

[peterjamesmatthews]

EDIT: Looks like my repro was fixed with #6624.

Upgrading my gorm to 1.25.5 fixed this for me.

Thanks, @jinzhu & @samuelncui!

---

My Issue

I'm also hitting this error on some of my work.

Looks like L222 is looking for PRIMARY KEY in the gorm.schema.Field's DataType.

gorm/migrator/migrator.go

Lines 218 to 226 in 0123dd4

	for _, dbName := range stmt.Schema.DBNames {
	field := stmt.Schema.FieldsByDBName[dbName]
	if !field.IgnoreMigration {
	createTableSQL += "? ?"
	hasPrimaryKeyInDataType = hasPrimaryKeyInDataType || strings.Contains(strings.ToUpper(m.DataTypeOf(field)), "PRIMARY KEY")
	values = append(values, clause.Column{Name: dbName}, m.DB.Migrator().FullDataTypeOf(field))
	createTableSQL += ","
	}
	}

It does seem to be present in m.DB.Migrator().FullDataTypeOf(field).SQL.

Unsure if this behavior is consistent across dialects.

I've forked peterjamesmatthews:6762-sqlite-more-than-one-primary-key with a potential fix.

[Kunniii]

Great! An update to version 1.25.5 fixed the problem! Thanks for the response @peterjamesmatthews .