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

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!

Comment From: 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`. https://github.com/go-gorm/gorm/blob/0123dd45094295fade41e13550cd305eb5e3a848/migrator/migrator.go#L218-L226 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](https://github.com/peterjamesmatthews/gorm/tree/6762-sqlite-more-than-one-primary-key) with a [potential fix](https://github.com/peterjamesmatthews/gorm/commit/becf9914cad85da7cf023fac52596c14786a96b4).

Comment From: Kunniii

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