CODEWITHSUNDEEP
mysqlruby-on-railsruby
Alexandre Butynski
Alexandre Butynski

Reputation: 6746

Ruby on Rails migration, two primary keys but just one auto-incremented

I am building a RoR multi-tenant application. So the application database could be hosted on diverse places and could be split or re-merged. That's why I need a couple of primary keys: a classic auto-incremented id and another one which is the foreign key of my main model (which couldn't be divise).

I want to do something like this:

create_table :table_name do |t|
  t.primary_key :id
  t.primary_key :second_id, :auto_increment => false
  # ...
  t.timestamps
end

But I don't find the right syntax, any idea?

Edit : I want to do it with a migration!

Upvotes: 1

Views: 2731

Answers (3)

Alexandre Butynski
Alexandre Butynski

Reputation: 6746

After lot of researches and tests, I think it's impossible to do it with a classic migration. But I find the solution to continue to manage my database with migrations. It's possible to include pieces of SQL with the execute command.

So, my creation look like that :

def self.up

  execute "CREATE TABLE `table_name` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `org_id` int(11) NOT NULL,
    `created_at` datetime DEFAULT NULL,
    `updated_at` datetime DEFAULT NULL,
    PRIMARY KEY (`id`, `org_id`)
  ) ENGINE=InnoDB AUTO_INCREMENT=151 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;"

end

def self.down
  drop_table :table_name
end

It's not really pretty but it do the job.

Upvotes: 0

Martin
Martin

Reputation: 1448

CREATE TABLE test.tst (

id INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,

second_id INT NOT NULL ,

timest DATE NOT NULL ,

UNIQUE ( second_id )

) ENGINE = MYISAM ;

is this it?

Upvotes: 0

Conrad Frix
Conrad Frix

Reputation: 52645

You can't have two Primary keys on a table.

You can have two Candidate Keys (Called a Key in MySQL Unique indexes/constraints in other DBs)

You can have a composite primary key made up of two fields if you'd like. I think this is the syntax in mySQL primary key (fieldA,fieldB)

However it makes little sense to do a composite key when one of the fields is an autoincrement. Since the autoincrement alreayd make a unique by itself and every attribute other than then id would have a functional dependency on it. Including second_id in the primary key won't help you any.

Upvotes: 2

Related Questions