New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Create foreign key constraint between references #23

Closed
cihangir opened this Issue Dec 2, 2013 · 19 comments

Comments

Projects
None yet
@cihangir
Contributor

cihangir commented Dec 2, 2013

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

This comment has been minimized.

Show comment
Hide comment
@jinzhu

jinzhu Dec 2, 2013

Owner

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

Owner

jinzhu commented Dec 2, 2013

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

@pmorton

This comment has been minimized.

Show comment
Hide comment
@pmorton

pmorton Dec 4, 2013

@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.

pmorton commented Dec 4, 2013

@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

This comment has been minimized.

Show comment
Hide comment
@cihangir

cihangir Dec 4, 2013

Contributor

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

Contributor

cihangir commented Dec 4, 2013

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

@jinzhu

This comment has been minimized.

Show comment
Hide comment
@jinzhu

jinzhu Dec 5, 2013

Owner

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.

Owner

jinzhu commented Dec 5, 2013

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.

@jinzhu jinzhu closed this Dec 5, 2013

@cihangir

This comment has been minimized.

Show comment
Hide comment
@cihangir

cihangir Dec 5, 2013

Contributor

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

Contributor

cihangir commented Dec 5, 2013

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

This comment has been minimized.

Show comment
Hide comment
@jinzhu

jinzhu Dec 6, 2013

Owner

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?

Owner

jinzhu commented Dec 6, 2013

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?

@jinzhu jinzhu reopened this Dec 6, 2013

@leebrooks0

This comment has been minimized.

Show comment
Hide comment
@leebrooks0

leebrooks0 Jan 5, 2014

Contributor

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
Contributor

leebrooks0 commented Jan 5, 2014

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

This comment has been minimized.

Show comment
Hide comment
@jinzhu

jinzhu Jan 5, 2014

Owner
 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.

Owner

jinzhu commented Jan 5, 2014

 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

This comment has been minimized.

Show comment
Hide comment
@xming

xming Feb 25, 2014

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

xming commented Feb 25, 2014

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

@xming

This comment has been minimized.

Show comment
Hide comment
@xming

xming Feb 25, 2014

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

xming commented Feb 25, 2014

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

@jinzhu

This comment has been minimized.

Show comment
Hide comment
@jinzhu

jinzhu Feb 26, 2014

Owner

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

Owner

jinzhu commented Feb 26, 2014

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

@jinzhu jinzhu closed this Feb 26, 2014

@metral

This comment has been minimized.

Show comment
Hide comment
@metral

metral Jan 13, 2015

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)"`
}

metral commented Jan 13, 2015

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

This comment has been minimized.

Show comment
Hide comment
@codepushr

codepushr May 22, 2015

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

codepushr commented May 22, 2015

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

@nanocent

This comment has been minimized.

Show comment
Hide comment
@nanocent

nanocent Mar 7, 2016

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")

nanocent commented Mar 7, 2016

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 comment has been minimized.

Show comment
Hide comment
@chuckhacker

chuckhacker Jan 18, 2017

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?

chuckhacker commented Jan 18, 2017

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

This comment has been minimized.

Show comment
Hide comment
@khatribharat

khatribharat Jul 16, 2017

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)

khatribharat commented Jul 16, 2017

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

This comment has been minimized.

Show comment
Hide comment
@chrisjpalmer

chrisjpalmer Oct 31, 2017

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.

chrisjpalmer commented Oct 31, 2017

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

This comment has been minimized.

Show comment
Hide comment
@koenig-dominik

koenig-dominik Nov 4, 2017

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")
}

koenig-dominik commented Nov 4, 2017

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

This comment has been minimized.

Show comment
Hide comment
@roobre

roobre Nov 9, 2017

@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.

roobre commented Nov 9, 2017

@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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment