Add a reference column using migration having non standard primary key column

Question

I have a model which has a non-rails conventional primary key.

class Guoid < ActiveRecord::Base
  self.primary_key = :guoid
end

and related migration

class CreateGuoids < ActiveRecord::Migration
  def change
    create_table :guoids, id: false do |t|
      t.integer :guoid, limit: 8, auto_increment: true, primary_key: true
      t.integer :parent_guoid, limit: 8
      t.string :resource_type, limit: 60
    end
  end
end

Now I want to reference this model in another model and trying to create migration using references which doesn't to work.

class ContentUnit < ActiveRecord::Base
  self.primary_key = :guoid
end

class Content < ActiveRecord::Base
  self.primary_key = :guoid
  belongs_to :user
  belongs_to :content_unit
end

and related migration

class CreateContents < ActiveRecord::Migration
  def change
    create_table :contents, id: false do |t|
      t.references :content_unit, index: true, foreign_key: true
      t.references :user, index: true, foreign_key: true
    end
  end
end

When I run the migration, I am getting following error.

Mysql2::Error: Can't create table `myapp_development_cr1`.`#sql-54a_308` (errno: 150 "Foreign key constraint is incorrectly formed"): ALTER TABLE `contents` ADD CONSTRAINT `fk_rails_823443bd0d`
FOREIGN KEY (`content_unit_id`)
  REFERENCES `content_units` (`id`)

I am expecting to create content_unit_guoid foreign key in contents table referencing guoid in guoids table.

I used activerecord-mysql-awesome gem to work well with non-rails convention primary keys.

Here is a trigger which first creates a record in guids table and use it's pk as pk of the target table.

DELIMITER $$
CREATE TRIGGER `content_before_insert` BEFORE INSERT ON `content`
 FOR EACH ROW BEGIN
IF NEW.guoid = 0 THEN
    INSERT INTO `content`.guoids (resource_type)
        VALUES('Content');
    SET NEW.guoid = LAST_INSERT_ID();
END IF;
END
$$
DELIMITER ;

Is this a legacy application where you have to use this weird setup or do you just like making things hard on yourself? — max, May 24 '16 at 12:32
No. It is not a legacy application. I have to create a globally unique id and use that in different tables. My technical manager who comes from non-rails background proposed to use such ids. — Amit Patel, May 24 '16 at 12:35
He is an idiot. Managers often are :). You need to have primary keys on each table for ActiveRecord to work. I would strongly advise you stick with the rails defaults. `id` on each table and `_id` for foreign keys. — max, May 24 '16 at 12:37
First of all, check if you REALLY need the globally unique ID. If you do, then I would propose using uuid, instead of changing the conventions. Changing them will bring you a lot of pain. — ZebThan, May 24 '16 at 12:48
PS. GUID is really just microsoft speak for what the rest of us call UUID's — max, May 24 '16 at 13:14

max · Accepted Answer · 2016-05-24T13:18:42.573

That is not a viable database design for ActiveRecord or even in general.

ActiveRecord (and any decent ORM) requires each table to have a primary key. This is what enables relations and lets Rails differentiate records.

class Content < ActiveRecord::Base
  self.primary_key = :guoid 
  belongs_to :user
end

This will never work since self.primary_key = :guoid references contents.guoid not guoids.guoid. You cannot use relations in ActiveRecord as primary keys. Even if you could this would really be a performance killer since every query would need to join the guoids table - even recursively!

Rails is strongly convention driven and really smiles on you if you take a little time to learn the Rails way vs fighting the framework to make it work like framework X. The rails guides are a good place to start.

Stick with id for primary keys and _id for foreign key columns. You will have less fuss and not be treated like idiots if you have to collaborate with other developers.

There are valid cases where you would want to use unique identifiers (UUIDs) instead of auto-incrementing values. For example if you have several databases auto-incrementing values can cause race conditions. But in that case you still need to use a primary key on each table - the difference is simply the contents of the primary key.

This is either done by generating a hashes with a algorithm that has a low chance of collision on the application level or more recently by using binary UUID types in the database. The later being preferable today.

Not by using relations. AR just does not work that way.

Using non-standard foreign keys.

The belongs_to and reference macros are an example of things that just work if you follow the conventions.

For foreign key constrainst that does not match the conventions you need to manually create it in the migration:

class CreateStores < ActiveRecord::Migration
  def change
    create_table :contents do |t|
      t.references :manger, index: true, foreign_key: false
    end
    add_foreign_key :stores, :users, column: 'manager_id', primary_key: 'uuid'
  end
end

Note that this will not fix your issue since your general approach is not viable!

Thanks, @max. I have fair knowledge about 'Rails Way' of doing things which saves time and energy. But I am in a situation where someone else is a decision maker. — Amit Patel, May 24 '16 at 13:20
Then you need to explain to your manager that the approach is not viable and suggest a viable solution - if you want to use UUID's in MySQL then you would use a PK per table and generate the UUID with MySQL:s `uuid` function. Although I would really recommend Postgres if you are starting fresh. — max, May 24 '16 at 13:27
Yes. I could convince to use `id` instead of `guoid`. Thanks, @max. — Amit Patel, May 25 '16 at 01:52

score 1 · Answer 2 · answered May 24 '16 at 13:14

So, your trying to make a foreign key from the contents table to the guoids table using this correct?

t.references :content_unit, index: true, foreign_key: true

references takes a table name as the argument and tries to find a column called id on it to make the foreign key between the tables. So you can see in your error message its trying to find the column id on content_units table. This is in no way referencing your guoids.

Its perfectly reasonable to want globally unique identifiers (typically GUID's or UUID's, however i'm not sure why you are storing them on a separate table and then (i'm assuming) going to make everything foreign key to it creating some massive many to many table that connects every table in your database? Seems really unscalable. Postgress handles uuid's for you nicely, but as I have in the past, it looks like your using mysql. Here is how I have done it.

Models

Class Teacher < ActiveRecord::Base
  has_many :students

  before_validation :generate_id
  self.primary_key = :id

private

  def generate_id
    write_attribute(:id, SecureRandom.uuid) unless read_attribute(:id)
  end
end


Class Student < ActiveRecord::Base
  belongs_to :teacher
end

Migrations

create_table :teachers, id: false do |t|
  t.string :id, null: false, unique: true, limit: 36
end

create_table :students do |t|
  t.string :teacher_id, limit: 36
end

add_foreign_key :model_a, :model_b

Add a reference column using migration having non standard primary key column

2 Answers2

Using non-standard foreign keys.

Models

Migrations