Jeffrey L. Taylor wrote in post #960038:
Quoting Marnen Laibow-Koser <email@example.com>:
Composite *primary keys* are not (but there's a plugin). Composite
*indices* are. You can specify multiple columns in add_index.
Sorry I left out a constraint that turned out to be critical. This is
primary and only index on this table. I've tried the plug-in. It was
reliable in my use scenario.
Then here's what I would suggest. Keep the composite index, but do not
make it the primary key. Use a surrogate key (that is, a Rails-style id
field) instead. You'll be happier. Rails will be happier. Composite
primary keys tend to be annoying (yes, I've worked with them
successfully, but I'm glad I no longer have to), and anyway surrogate
keys are nicer because you can guarantee that they'll never ever change
over the life of the record.
> And I will not switch
> servers next week, so having the index creation in portable form is not
> a necessity.
Never write SQL in Rails unless there's no other way.
If it is feasible in Rails, I do it that way. However, I was explicitly
lifting the constraint of doable in Rails for this usecase. If the
included dozens of servers and multiple DB server software, then staying
Rails would make sense. I have one production server (with test
one or two development setups (with test databases). Setup in the
client program is very doable.
But it's a bad idea since it defeats automation. The right way to do
this -- if you *must* -- is to put raw SQL in the migration and set the
schema dumper to use SQL. That way you can still use migrations and the
Fighting to shoehorn a usecase that
not handle into Rails is not productive.
On the contrary, it's very productive. That's how many of the best
Rails plugins got developed.
But in this case, all you need to do is add a surrogate key.