Create foreign key constraint between references

[cihangir]

Starting with your example;

type User struct {
        Id                int64     // Id: Primary key
       ................
        Emails            []Email       // Embedded structs
        BillingAddress    Address       // Embedded struct
        BillingAddressId  sql.NullInt64 // Embedded struct's foreign key
        ShippingAddress   Address       // Embedded struct
        ShippingAddressId int64         // Embedded struct's foreign key
        IgnoreMe          int64 `sql:"-"`
}

you are not creating foreign key constraints between two tables(User-Address), this is useful for data integrity.

[jinzhu]

Could you give me more details about this? Don't really understand about your question, Thank you.

[pmorton]

@Siesta is asking to have foreign key constraints added when you have a relation. In the example above you have a user and the user has one shipping address. A foreign key constraint would tell the database to make sure that ShippingAddressId exists in the shipping_address table. In addition to data integrity, it also helps with tooling that reflects on foreign keys constraints to model the database.

[cihangir]

Hey guys, sorry i forgot to reply, and thanks @pmorton this is exactly what i wanted to state in this issue.

[jinzhu]

Seems it is hard to make it database agnostic especially for postgres, it may break create table flow because of postgres is really strictly about this.

Also for below struct, seems not easy to get the foreign table users.

type Email {
  Id int64
  UserId int64
  Email string
}

So maybe better to do it by yourself for now:

type User {
  ...
  BillingAddressId  sql.NullInt64 `sql:"type:bigint REFERENCES address(id)"`
}

I will think about this more in future, please suggest me if you have any suggestions.

Thank you.

[cihangir]

Hey @jinzhu

Seems it is hard to make it database agnostic especially for postgres

Actually the databases that you are supporting for now, supports foreign keys, on the same way, even definitions are same
mysql = http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html
postgres = http://www.postgresql.org/docs/9.3/static/tutorial-fk.html
sqlite = http://www.sqlite.org/foreignkeys.html

it may break create table flow because of postgres is really strictly about this.
it will not, it should not break

Also for below struct, seems not easy to get the foreign table users.

Because the struct is little wrong i think. You should only add(embed) referenced struct into parent
instead of this one

 type Email {
    Id int64
    UserId int64
    Email string
}

database struct must be

 type Email {
    Id int64
    User User  //<--
    Email string
}

if a developer wants to know what userId is, he/she should find it via User property, like myStruct.User.Id
With this approach it is easy to provide this feature.

So, adding UserId and User into same struct -imo- is not a good approach.
On the other hand this approach will help you on sql.NullInt64 problem. -yes- i think defining this field is a problem and conflicts with your developer friendly orm motto.

So maybe better to do it by yourself for now:

sure, i could do it, but my problem is :) i really really liked your work and i want to make this package better, even with the current status, this is BEST orm package that i have seen so far! Thanks for your efforts.

Meanwhile you can investigate Hibernate for Java, Doctrine for PHP, for defining referenced tables
here is a quick google result http://d.pr/V6lZ

[jinzhu]

Thank you for your reply.

Seems it is hard to make it database agnostic especially for postgres

I means postgres is really strictly, and would throw error if the reference table doesn't exist. but mysql is ok about this.

So you need to adjust the migration order carefully based on relations to avoid issue, and this would be tricky if two tables referenced each other. ;(

for the second question

 type Email struct {
    Id int64
    User User
}

type User struct {
   Email Email
}

I think it is hard to know the foreign key based on above definition? So we have to use tag to declare it if don't have UserId field, like this:

 type Email struct {
    Id int64
    User User `sql:foreign_key("user_id")`
}

if a developer wants to know what userId is, he/she should find it via User property, like myStruct.User.Id
With this approach it is easy to provide this feature.

Get user_id with myStruct.User.Id is not a good implementation because of it is quite confusion.

For example, you are getting user_id with myStruct.User.Id, what about name? also get it with myStruct.User.Name?

Then gorm need to find out related user when query myStruct, this would be unnecessary cost if you don't require any user information.

Anyway, I just give it another thought, maybe we could implement it based on below ideas:

 type Email struct {
    Id int64
    User User
    UserId int64
}

• We keep the UserId here to avoid confusion.
• If exist embed struct, for this example, User, we will find out the foreign key based on current logic and create foreign key references for it.

 type Email struct {
    Id int64
    UserId int64
}

• If no embed struct defined, won't create the references.
• Don't know if it is possible to make auto migrate works with it, will check it out later.

Any other ideas?

[leebrooks0]

For auto migrating, what about if you just list the structs in order

DB.AutoMigrate(Role{})
DB.AutoMigrate(User{}) // User has a role_id field

[jinzhu]

 type Email struct {
    User User
}

type User struct {
   Email Email
}

For above two structs, they relied each other, and for postgres, you can't create any references if the referred table doesn't exist.

[xming]

why not use ALTER TABLE for the constraints? After all CREATE TABLE.

[xming]

Ah I see that sqlite doens't supoort adding contraints with alter table.

[jinzhu]

Close this issue first because don't have a perfect solution to resolve it.

[metral]

has a solution to this been discovered? its been almost 1 year

sql tags of this form don't enforce the constraint in mysql when the table is created:

type User {
  ...
  BillingAddressId  sql.NullInt64 `sql:"type:bigint REFERENCES address(id)"`
}

[codepushr]

Not working for me either. I mean the "type:int REFERENCES parentTable(id)" thing.

[nanocent]

Hi, How about providing the ability to define Foreign References in the struct definition.

Similar to:
db.Model(&User{}).AddForeignKey("city_id", "cities(id)", "RESTRICT", "RESTRICT")

Therefore, if we want to define the foreign key, we can have it in a function which uses the schema from struct and adds constraints via statements like below before it is imported :
db.Model(&User{}).AddForeignKey("city_id", "cities(id)", "RESTRICT", "RESTRICT")

[chuckhacker]

This is a pretty serious problem, as anyone using an ORM would expect proper abstraction of foreign keys with constraint. Has there been any progress at all since the last update?

[khatribharat]

The following works for me with gorm v1.0 and PostgreSQL 9.6.3

type Address struct {
	ID string `gorm:"primary_key"`
}

type UserAddress struct {
	BillingAddressId string `sql:"type:varchar REFERENCES addresses(id)"`
}

Note the use of plural name addresses when defining the foreign key constraint.

psql Output

> \dS user_addresses
           Table "public.user_addresses"
       Column       |       Type        | Modifiers 
--------------------+-------------------+-----------
 billing_address_id | character varying | 
Foreign-key constraints:
    "user_addresses_billing_address_id_fkey" FOREIGN KEY (billing_address_id) REFERENCES addresses(id)

[chrisjpalmer]

Here's a way I found to do it. The process is not automatic and you must call a function manually but its a good solid solution:

type User struct {
	gorm.Model
	Username string
	Email string
	Profiles []Profile       `gorm:"many2many:user_profiles;"`
	Groups []Group        `gorm:"many2many:user_groups;"`
}

type Profile struct {
	gorm.Model
	ProfileName string
}

type Group struct {
	gorm.Model
	GroupName string
}

func UserExample1(db *gorm.DB) {
	db.AutoMigrate(&User{})
	db.AutoMigrate(&Group{})
	db.AutoMigrate(&Profile{})

	Many2ManyFIndex(db, &User{}, &Profile{})
	Many2ManyFIndex(db, &User{}, &Group{})
}

func Many2ManyFIndex(db *gorm.DB, parentModel interface{}, childModel interface{}) {
	table1Accessor := ReduceModelToName(parentModel)
	table2Accessor := ReduceModelToName(childModel)

	table1Name := inflection.Plural(table1Accessor)
	table2Name := inflection.Plural(table2Accessor)

	joinTable := fmt.Sprintf("%s_%s", table1Accessor, table2Name)

	db.Table(joinTable).AddForeignKey(table1Accessor+"_id", table1Name+"(id)", "CASCADE", "CASCADE")
	db.Table(joinTable).AddForeignKey(table2Accessor+"_id", table2Name+"(id)", "CASCADE", "CASCADE")
	db.Table(joinTable).AddUniqueIndex(joinTable+"_unique", table1Accessor+"_id", table2Accessor+"_id")
}

func ReduceModelToName(model interface{}) string {
	value := reflect.ValueOf(model)
	if value.Kind() != reflect.Ptr {
		return ""
	}

	elem := value.Elem()
	t := elem.Type()
	rawName := t.Name()
	return gorm.ToDBName(rawName)
}

This would set the foreign indexes for the join table and add a unique index to the join table so that duplicate joins are not possible.

[koenig-dominik]

A even better solution is to use the built in method "TableName(*db)"

type User struct {
   ID uint         `gorm:"primary_key;AUTO_INCREMENT"`
   Referrer *User
   ReferrerID *uint
}

func Migrate(db *gorm.DB) {
   db.AutoMigrate(&User{})
   userTableName := db.NewScope(&User{}).GetModelStruct().TableName(db)
   db.Model(&User{}).AddForeignKey("referrer_id", userTableName + "(id)", "RESTRICT", "RESTRICT")
}

[roobre]

@jinzhu @xming

why not use ALTER TABLE for the constraints? After all CREATE TABLE.

For above two structs, they relied each other, and for postgres, you can't create any references if the referred table doesn't exist.

Correct me if I'm wrong, but this problem could be approached from different angles depending on which backend is being used. So, wouldn't be possible to insert references for yet to exist tables on databases which support it (mysql, sqlite?) and fall back to alter table and add the references afterwards for postgres? As long as the user calls DB.AutoMigrate() with all their models at once it shouldn't be a problem, I think.