我如何使用GORM创建复合主键? [英] How do I create a composite primary key using GORM?
问题描述
我有三个域类:Beer,Review和Reviewer。
我希望Review表能够在Beer和Reviewer之间创建多对多的关系,所以我希望Review的主键是来自Beer和Reviewer的id字段的组合。我遵循这个Grails文档。
以下是我的网域类别。
class Beer {
字符串名称
字符串类型
啤酒厂breweryId
static hasMany = [评论:评论]
静态约束= {
}
}
类审稿人{
字符串screenName
static hasMany = [评论:评论]
静态约束= {
}
}
class Review实现Serializable {
int分数
Beer啤酒
评论评论者
静态约束= {
}
静态映射= {
id复合:['beer ','reviewer']
}
}
我收到编译错误,但在stackoverflow上的另一个答案表示,我需要添加 implements Serializable
。这照顾到了错误,但是当我查看数据库时,我仍然没有得到一个复合主键。
这是当我看到看看表格的定义。
表格public.review
列|类型|修饰符
------------- + --------- + -----------
id | bigint |不为空
版本| bigint |不为空
beer_id | bigint |不为空
reviewer_id | bigint |非空
得分|整数|非空
索引:
review_pkeyPRIMARY KEY,btree(id)
外键约束:
fkc84ef75823f39326FOREIGN KEY(beer_id)REFERENCES beer(id)
fkc84ef7587c483106FOREIGN KEY(reviewer_id)参考文献reviewer(id)
只是一个具有唯一约束的复合索引,但我无法弄清楚如何做到这一点。我已经能够创建一个非唯一的组合索引,但是这有两个问题。一,它是非独特的。二,列在索引中按字母顺序指定(beer_id,reviewer_id)。我想指定索引中列的顺序。
我接受了Grails的授权,我不应该使用复合主键作为明智的建议,并避免它。如果是这样,我相信解决您的问题的一个可行的替代方案是复合唯一约束。
ref: http://grails.org/doc/1.1 .x / ref / Constraints / unique.html
Jacco的答案似乎不正确,尽管它的外观看起来非常接近正确,下面是你'd为这个问题写一个复合唯一约束:
pre $ static constraints = {
beer(unique:'reviewer' )
}
然而,如果程序员想要将3个数据库字段连接为唯一,形成如下:
static constraints = {
beer(unique:['anotherField','reviewer'])
}
其中看起来像Jacco的答案,但类名不是作为约束的第一个字符串,使用第一个字段名称。
我在我自己的项目应用程序中使用了这种代码结构,它似乎要正确表现,另请参阅如何进行单元测试独特的限制:
http://www.ibm。 com / developerworks / java / library / j-grails10209 / index.html
(参见清单11)
I have three domain classes: Beer, Review, and Reviewer.
I want the Review table to create a many to many relationship between Beer and Reviewer, so I want the primary key of Review to be a composite of the id fields from Beer and Reviewer. I'm following this Grails documentation.
Here are my domain classes.
class Beer {
String name
String type
Brewery breweryId
static hasMany = [ reviews : Review ]
static constraints = {
}
}
class Reviewer {
String screenName
static hasMany = [ reviews : Review ]
static constraints = {
}
}
class Review implements Serializable {
int score
Beer beer
Reviewer reviewer
static constraints = {
}
static mapping = {
id composite:['beer', 'reviewer']
}
}
I was getting compilation errors, but another answer here on stackoverflow said I needed to add implements Serializable
. That took care of the error, but when I look in the database, I'm still not getting a composite primary key.
Here is what I'm seeing when I look at the table definition. I'm using Postgres.
Table "public.review"
Column | Type | Modifiers
-------------+---------+-----------
id | bigint | not null
version | bigint | not null
beer_id | bigint | not null
reviewer_id | bigint | not null
score | integer | not null
Indexes:
"review_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"fkc84ef75823f39326" FOREIGN KEY (beer_id) REFERENCES beer(id)
"fkc84ef7587c483106" FOREIGN KEY (reviewer_id) REFERENCES reviewer(id)
I'd be happy with just a composite index with a unique constraint, but I can't figure out how to do that, either. I've been able to make a non-unique composite index, but this has two problems. One, it's non-unique. Two, the columns are specified in alphabetical order in the index (beer_id, reviewer_id). I'd like to specify the order of the columns in the index.
I took the Grails mandate that I shouldn't use Composite Primary keys as wise advice and am avoiding it. If so, I believe a viable alternative to solve your problem is the Composite Unique Constraint. ref: http://grails.org/doc/1.1.x/ref/Constraints/unique.html
Jacco's answer seems to be not correct, although it looks visually very close to correct, here is how you'd write a composite unique constraint for this problem:
static constraints = {
beer(unique: 'reviewer')
}
whereas if the programmer wanted to link 3 db fields as unique, the correct formation is:
static constraints = {
beer(unique: ['anotherField','reviewer'])
}
which looks like Jacco's answer, but the class name is not used as the first string of the constraint, the first field name is used.
I've just used this code structure in my own project app, and it seems to be behaving correctly, see also this on how to unit test unique constraints: http://www.ibm.com/developerworks/java/library/j-grails10209/index.html (see listing 11)
这篇关于我如何使用GORM创建复合主键?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!