Define a unique primary key based on 2 columns

Question

I would like to define a unique key for records based on 2 columns : 'id' and 'language'

to let the user submits the following strings : id=1 language=en value=blabla english id=1 language=fr value=blabla french

I tried to use set_primary_key and also add_index but it didn't work ( add_index :words, ["id", "language_id"], :unique => true )

I have the following model :

class Word < ActiveRecord::Base
  belongs_to :dictionnary
  belongs_to :language

  attr_accessible :lang, :rev, :value, :dictionnary_id, :language_id

  validates :value, :uniqueness => true

end

and this

class Language < ActiveRecord::Base
    has_many :words

    attr_accessible :lang
end

if the word belongs to a Language why do you need to store the lang with it? You can just delegate it to the Language Model. That being said you will be fighting rails. I would just use the ID key as the primary key. delegate :lang to Language and not worry about the composite key — Doon, Oct 05 '12 at 12:34
indeed, thanks. :lang is coming from ald scaffilding. I need to remove it from here since I use the Language model. Thx — Stéphane V, Oct 05 '12 at 18:20

rogal111 · Accepted Answer · 2012-10-05T12:45:05.260

53

add_index :words, ["id", "language_id"], :unique => true

It should work. Maybe you have already some non-unique data in your db and index can't be created? But (as @Doon noticed it will be redundant since id is always unique). So you need create primary key on two columns.

To define 2 column primary key in rails use:

create_table :words, {:id => false} do |t|
  t.integer :id
  t.integer :language_id
  t.string :value
  t.timestamps
end
execute "ALTER TABLE words ADD PRIMARY KEY (id,language_id);"

And set primary_key in your model with this gem: http://rubygems.org/gems/composite_primary_keys:

class Word < ActiveRecord::Base
    self.primary_keys = :id,:language_id
end

edited Oct 05 '12 at 12:45

answered Oct 05 '12 at 12:33

rogal111

5,634
2
25
33

this will add unique index on the table for id/language_id) but more than likely id will still be the primary key, and subject to unique index. so this index would be redundant since id is always unique. – Doon Oct 05 '12 at 12:36
3

Also possible: `validates :language_id, uniqueness: { scope: [:id] }` – James Billingham Mar 19 '15 at 14:04
How to create a combined unique (not primary) key based on two columns? Which basically prevents creation of multiple rows having the same values on column a & column b .. – W.M. Jun 18 '16 at 11:06
Just wanted to add that one can also add a composite_primary_key and provide a name for that constraint with the following command: `execute "ALTER TABLE words ADD CONSTRAINT contraint_name PRIMARY KEY (id, language_id)"` – bruno-martins Jul 04 '18 at 20:35

Vadim · Answer 2 · 2018-03-14T15:02:20.527

10

In Rails 5 you can do the following:

create_table :words, primary_key: %i[id language_id] do |t|
  t.integer :id
  t.integer :language_id
  t.string :value
  t.timestamps
end

It is also NOT necessary to set the primary_key attribute on the Word model.

edited Mar 14 '18 at 15:02

answered Mar 23 '17 at 22:31

Vadim

1,650
2
17
31

9

Rails 5 [still does not support composite primary keys](https://github.com/rails/rails/blob/e00b467c479e495292095e09ce53b326035ec467/activerecord/lib/active_record/attribute_methods/primary_key.rb#L131) – kolen Nov 21 '18 at 17:00

score 2 · Answer 3 · edited Mar 28 '16 at 11:20

Model

class User < ActiveRecord::Base
  has_secure_password
  self.primary_keys = :name
end

Migration

class CreateUsers < ActiveRecord::Migration
  def change
    create_table :users do |t|
      t.string :name, null: false
      t.string :emailid
      t.string :password_digest
      t.integer :locked, :default => 0
      t.text :secretquestion
      t.string :answer

      t.timestamps null: false
    end
    add_index :users, :name, :unique => true
  end
end

You will get this table

score 1 · Answer 4 · answered Oct 05 '12 at 12:42

As I said in my comments you will be fighting rails if you try this, and it isn't really supported out of the box. you can look at http://compositekeys.rubyforge.org which offers a way to do composite primary keys in rails. I haven't used it, as I haven't had a need yet (normally when I have something that is composite key like it is just a join table with no primary key and a unique index on the joined pair (HABTM).

score 0 · Answer 5 · answered Oct 05 '12 at 12:43

Depending on your use case you might want to try out composite key gem which allows you to define composite primary keys and also ehances ActiveRecord to deal with this kind of model (helps a lot for associations to this model or for url_for helpers etc.).

So if you plan in using this model as any other rails model the gem will help a lot.

score 0 · Answer 6 · answered Feb 16 '16 at 22:55

I faced a similar problem when migrating a site to Rails. I had a table which stores text data for each language my site is available in so I had something like this:

CREATE TABLE Project_Lang(
    project_id INT NOT NULL,
    language_id INT NOT NULL,
    title VARCHAR(80),
    description TEXT,

    PRIMARY KEY pk_Project_Lang(project_id, language_id),

    FOREIGN KEY fk_Project_Lang_Project(project_id)
        REFERENCES Project(project_id)
        ON DELETE RESTRICT ON UPDATE CASCADE,

    FOREIGN KEY fk_Project_Lang_Language(language_id)
        REFERENCES Language(language_id)
        ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 DEFAULT COLLATE = utf8_spanish_ci;

But since Rails doesn't handle composite primary keys out of the box I was forced to change the structure of the table so it had it's own primary key:

CREATE TABLE Project_Lang(
    project_lang_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    project_id INT NOT NULL,
    language_id INT NOT NULL,
    title VARCHAR(80),
    description TEXT,

    UNIQUE INDEX(project_id, language_id),

    FOREIGN KEY fk_Project_Lang_Project(project_id)
        REFERENCES Project(project_id)
        ON DELETE RESTRICT ON UPDATE CASCADE,

    FOREIGN KEY fk_Project_Lang_Language(language_id)
        REFERENCES Language(language_id)
        ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 DEFAULT COLLATE = utf8_spanish_ci;

I also created a unique index for the columns that previously made the composite primary key so that no duplicate record is inserted. Then in my Rails model I could simply:

self.primary_key = "project_lang_id"

And that did the trick. Is not what I wanted but is better than fighting the framework.

score 0 · Answer 7 · answered Dec 01 '16 at 03:30

0

Just like @rogal111 said, but if a primary key already exists then you'll want to do this

ALTER TABLE sections DROP PRIMARY KEY, ADD PRIMARY KEY(id, workspace_id, section_key);

answered Dec 01 '16 at 03:30

Son Tr.

503
4
12

Define a unique primary key based on 2 columns

7 Answers7

Linked