Gorm: composite primary keys which are both uint adds auto_increment to both fields

[yanfali]

In unit test multi_primary_keys_test.go you test for a composite primary key which is uint/string and this seems to work correctly. However if you attempt to use uint/uint it adds auto_increment to both fields which is not legal on mysql/mariadb using innodb.

type Content struct {
DrawerID uint gorm:"primary_key" sql:"not null" json:"drawerId,omitempty"
ArtifactID uint gorm:"primary_key" sql:"not null" json:"artifactId"
State int sql:"not null" json:"state"
}

Produces:

CREATE TABLE content (drawer_id int unsigned AUTO_INCREMENT NOT NULL ,artifact_id int unsigned AUTO_INCREMENT NOT NULL ,state int NOT NULL , PRIMARY KEY (drawer_id,artifact_id))

Is there a way to tell it not to use auto_increment on a primary key? My current work around is to use raw sql after the tables have been created to add the primary keys.

[li3p]

sounds like you should use unique key , not primary key.

[yanfali]

Hi @li3p actually that wouldn't work. I need the combination (composite) of both keys to be unique. Having each column be unique would only prevent me using it as a many to many relationship. The issue as it stands is that since I'm also using gorm to generate the tables, these two use cases are interfering with each other.

If I didn't use Gorm to generate my schema and just used the markup, I believe it would work. Not having gorm recognize that these 2 fields are a key means that inserts are being instead of updates on save and this causes the constraints I set up on the table to fail.

[yanfali]

So I came up with a horrible work around:

@@ -349,6 +353,11 @@ func (scope *Scope) generateSqlTag(field *StructField) string {
                if field.IsPrimaryKey {
                        autoIncrease = true
                }
+               _, noAutoIncrease := sqlSettings["NO_AUTO_INCREMENT"]
+               if noAutoIncrease {
+                       println("not auto")
+                       autoIncrease = false
+               }

which patches model_struct.go. What I would prefer to do, is add a field to modelStruct.IsComposite and use this to change the behavior of autoIncrease and not set it to true if this primary key field is part of a composite key. I couldn't quite figure out how to do that from within generateSqlTag.

What's a good way to do this from within createTable? It seems like the signature for generateSqlTag needs to change or the StructField needs a field. Any advice you have on the cleanest way to do this would be appreciated.

[alecha]

I have the same issue, I think that this is a bug that should be addressed as soon as possible... Relating to this issue, when you call db.Delete(&content) it seems to don't make the right delete query, but a generic "DELETE from TABLE"....

[jinzhu]

Hi @yanfali

Yes, I noticed mysql doesn't support auto increment for more than one fields for the innodb engine (myISAM engine does support that), but postgres support it.

For now, if you want to use multi integer primary keys with mysql, you might need to set the field's sql type.

Thank you.

[yanfali]

Hi Jinzhu,

anybody seriously using mysql/mariadb/percona is NOT using myisam. The problem is, if you want to use gorm for SQL schema generation this is not the correct behavior. You're optimizing for a degenerate use case. The default engine for mysql has been innodb for quite some time.

[yanfali]

In the meantime, if people want to patch around this behavior, I've done the following

diff --git a/model_struct.go b/model_struct.go
index 10423ae..8d6032d 100644
--- a/model_struct.go
+++ b/model_struct.go
@@ -350,6 +350,12 @@ func (scope *Scope) generateSqlTag(field *StructField) string {
                        autoIncrease = true
                }

+               gormSettings := parseTagSetting(field.Tag.Get("gorm"))
+               _, composite := gormSettings["COMPOSITE"]
+               if composite {
+                       autoIncrease = false
+               }
+
                sqlType = scope.Dialect().SqlTag(reflectValue, size, autoIncrease)
        }

Just use the gorm keyword "composite".

[jinzhu]

Hi @yanfali

I am not suggesting you to use MyISAM, just talking what I know, InnoDB engine doesn't support multiple auto increment fields, so if you want to use that with mysql, you could just simply use sql type to do the job, for example:

type User struct {
  ID int `gorm:"primary_key" sql:"type:int"`
  MemberID int `gorm:"primary_key" sql:"type:int"`
}

[yanfali]

Ah, I see. I'd also argue, you should probably make this the default behavior and make coders using myisam just provide the auto_increment keyword. Just saying, it's going to the least surprising behavior for the vast majority of mysql users. Thanks for the workaround. I will give it a try.

[alecha]

The solution provided by jinzhu works, but I agree with yanfali to make this the default...

[yanfali]

The other downside to this is we now have to explicitly specify the types, which we could be inferred before from the code.

[yanfali]

 type RelatedArtifact struct {
       DrawerID           uint             `sql:"not null;type:int(10) unsigned" json:"drawerID" gorm:"primary_key"`
       FirstID            uint             `sql:"not null;type:int(10) unsigned" json:"firstID" gorm:"primary_key"`
       SecondID           uint             `sql:"not null;type:int(10) unsigned" json:"secondID" gorm:"primary_key"`
       RelationshipTypeID uint             `sql:"not null" json:"relationshipTypeID"`
}

As you can see, now this is super verbose, and if one changes the type Go type and forgets to update the sql struct tag, much fun could result.

[jinzhu]

I don't want to make it default as it will surprise users that don't know mysql can't support that.

Which means you have to set at least one of those primary keys by yourself if you are using mysql, mysql can't auto generate them automatically for you.

[yanfali]

Hi Jinzhu, typically for composite keys, the keys are foreign, and you'd never want to autogenerate a key.

[jinzhu]

Hi @yanfali

I understand that, but someone might use that for other cases (not for foreign keys), this might lead their systems go wrong, if they don't know this.

So it would be better for everyone would like to use it like this know the limitation on mysql.

[yanfali]

@jinzhu Would it be helpful to add something to readme specifically for MySQL to work around this limitation?