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