Comments (19)
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?
from gorm.
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")
from gorm.
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)
from gorm.
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.
from gorm.
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.
from gorm.
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")
}
from gorm.
Could you give me more details about this? Don't really understand about your question, Thank you.
from gorm.
@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.
from gorm.
Hey guys, sorry i forgot to reply, and thanks @pmorton this is exactly what i wanted to state in this issue.
from gorm.
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 breakAlso 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
from gorm.
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?
from gorm.
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
from gorm.
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.
from gorm.
why not use ALTER TABLE for the constraints? After all CREATE TABLE.
from gorm.
Ah I see that sqlite doens't supoort adding contraints with alter table.
from gorm.
Close this issue first because don't have a perfect solution to resolve it.
from gorm.
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)"`
}
from gorm.
Not working for me either. I mean the "type:int REFERENCES parentTable(id)" thing.
from gorm.
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.
from gorm.
Related Issues (20)
- Support for Many to Many Preload Joins
- GORM可以只创建表不创建索引么? HOT 6
- Noticed a bug with embedded structs not being nil HOT 1
- `unsupported data` error with nested joins/preloads
- Correlate BelongTo HOT 12
- AutoMigrate will not update changed `default` value for primary keys HOT 4
- Unexpected SQL Query Generation with Consecutive First Calls HOT 5
- How to map a `[]int` type to a string type in SQL? HOT 1
- Can't AutoMigrate() tables with uniqueIndex since v1.25.6 on MySQL HOT 2
- GORM type 为int,在tag中指定了type为int类型,自动迁移后还是会创建为bigint的类型的字段。 HOT 2
- Insert record with ID assigned doesn't commit the sequence value HOT 4
- Multiple Records Query:index out of range [0] with length 0
- How to use interfaces type with Gorm?
- The case when syntax of mssql is not supported HOT 2
- GetIndexes not implemented HOT 5
- btree_gist constraint example
- Wrong table creation order causes migration failure (but not in SQLite)
- More efficient preloading
- Add derived column along with default fields
- 查找不到数据的时候,err与gorm.ErrRecordNotFound比较的结果是false HOT 5
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from gorm.