Hi, I need to support a legacy database that mostly uses clustered primary keys (i.e. every primary key consiststs of multiple fields not just a single one. E.g. if I have tables A,B and C and C depends on tables A and B, then C's primary key will consist of 3 or more fields- the foreign key fields pointing to A and B and a field or fields from C that will make C unique- in combination with the fk fields). This is also a very typical database schema design 'pattern' and implementation. I just want to make sure about the following fact: Rails doesn't support clustered primary keys. (The active record method: 'set_primary_key' accepts a single field name only)Do I have it right? I plan to deal with this by 'converting' the current clustered primary keys to clustered secondary indixes and then add rails' default 'id' field as the primary key. In this way the db engine can still enforce referencial integrity and I can use Rails. I'm not sure the client dba will like this however. It seems that most database persistence frameworks, e.g. Hibernate support clustered primary keys. I've also seen other activerecord implementations that support this. So I'm wondering why rails doesn't support this- it is a very basic/common need. Is this something that might be added in future or is there a good reason to not want it? thanks
on 2006-06-09 11:38
on 2006-06-09 15:59
This has been covered many times before on this list. Rails does not support composite / clustered primary keys. It probably never will be supported natively becuase many feel that they bad design. Your suggested approach of adding the surrogate key on the legacy database is the usual fix for this issue. You'll find that Rails is best suited for new projects where you have control over the database design. That's where it shines. There are some ways to make it work with legacy databases, but I don't currently know of a way to support what you're asking. Members of the core team have said that there are lots of things that won't make it into Rails, but people are perfectly free to create their own plugins to support something like this. I imagine if it's an issue that is as basic or common as you suggest then some nice folks will invest time in creating a plugin to support it. I would also imagine that if that plugin was really successful then it might be considered for inclusion. The idea of multiple foreign keys as primary key can sometimes be dealt with by using has_and_belongs_to_many, but this only works if the join table doesn't have extra attributes. If it does, then it should be a model itself and therefore have its own unique key. Good luck!
on 2006-06-09 17:14
Thanks for your answer. First time I've used this forum, so sorry for not searching it properly. Just by the way: I'm not for or against the rails way for setting up Pk'a and Fk's; it's just that it is a fairly common db design/implementation and, for supporting legacy db's, things would be much simpler if Rails supported clustered Pk's. It would be interesting to understand the reasoning in not supporting clustered pks. (I can understand that the current design was more 'convenient' to implement and that there might be more pressing priorities) Thanks, anyway.