Foreign Key with Custom Variable

Questions / Help
1

I don’t get how to create a foreign key (using MariaDB) between the question table (column: chapter_ID) and chapter table (column: chapter_ID)

chapter migration:

defmodule My.Chapter do
  use My.Web, :model

  schema "chapter" do
    field :chapter_ID, :integer
    field :chapter_name, :string

    timestamps()
  end
end

I have this migration;

defmodule My.Repo.Migrations.CreateQuestion do
  use Ecto.Migration
      def change do
        create table(:question, primary_key: false) do
          add :question_ID, :"integer(8) unsigned", primary_key: true, autogenerate: true, null: false    
          add :chapter_ID, :"integer(5) unsigned", references(:chapter, column: :chapter_ID) 
        end
    end

and a model:

defmodule My.Question do
  use My.Web, :model

  schema "question" do
    field :question_ID, :integer 
    belongs_to :chapter, foreign_key: chapter_ID, My.Chapter
  end
end
1 Like
2

Do you need to have a custom PK for this? It seems like it might be more work than is required?

Ecto with mariaex will create the PK and FK fields as BIGINT(20) UNSIGNED by default, so you don’t need to specify them manually if you are just wanting to get unsigned cols.

I’m not sure what the relation is between the question and chapter, is it a one-to-many or a many-to-many?

1 Like
3

I know but I have achieved this results, and by default mariaex doesn’t generate an UNSIGNED INT.

The relation between question and chapter is: A question belongs only to a chapter, and a chapter can have many questions.

1 Like
4

It doesn’t for ‘normal’ INT fields, but for primary and foreign key fields it does.

I’m not sure on how to do it with a custom PK, as I’ve never had the need to do it I’m afraid.
However, for something like this, I would go with something like:

defmodule My.Repo.Migrations.CreateChapter do
  use Ecto.Migration
  
  def change do
    create table(:chapter) do
      add :chapter_name, :string
      timestamps()
    end
  end
end

defmodule My.Repo.Migrations.CreateQuestion do
  use Ecto.Migration
  
  def change do
    create table(:question) do
      add :chapter_id, references(:chapter)
      add :question, :string
    end
  end
end

and then for your models

defmodule My.Chapter do
  use My.Web, :model

  schema "chapter" do
    has_many :questions, My.Question
    field :chapter_name, :string
    timestamps()
  end
end

defmodule My.Question do
  use My.Web, :model

  schema "question" do
    belongs_to :chapter, My.Chapter
    field :question, :string
  end
end

Which makes SQL of:

SHOW CREATE TABLE chapter;

CREATE TABLE `chapter` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `chapter_name` varchar(255) DEFAULT NULL,
  `inserted_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

SHOW CREATE TABLE question;

CREATE TABLE `question` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `chapter_id` bigint(20) unsigned DEFAULT NULL,
  `question` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `question_chapter_id_fkey` (`chapter_id`),
  CONSTRAINT `question_chapter_id_fkey` FOREIGN KEY (`chapter_id`) REFERENCES `chapter` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
2 Likes