外部キー同士の組合せの重複保存ができないようにUnique制約を追加します。
尚、作成するテーブルはcommentsテーブルとし、カラムである外部キーはuser_idとpost_idとします。
MigrationID_MigrationName.rb
class CreateComments < ActiveRecord::Migration[6.0]
def change
create_table :comments do |t|
t.references :user, null: false, foreign_key: true
t.references :post, null: false, foreign_key: true
t.text :text, null: false
t.timestamps
end
add_index :comments, [:user_id, :post_id], unique: true #user_id × post_id = 一意の値としてUnique制約を追加
end
end
テーブルの設計が完了したら作成する。
$ bundle exec rake db:migrate
テーブルの作成とindexの確認をした後に、検証する。
$ rails db
mysql> show tables;
+-----------------------------+
| Tables_in_name_development |
+-----------------------------+
| comments |
.
.
mysql> show index from comments;
+----------+------------+---------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+---------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| comments | 0 | PRIMARY | 1 | id | A | 1 | NULL | NULL | | BTREE | | |
| comments | 0 | index_comments_on_user_id_and_post_id | 1 | user_id | A | 1 | NULL | NULL | | BTREE | | |
| comments | 0 | index_comments_on_user_id_and_post_id | 2 | post_id | A | 1 | NULL | NULL | | BTREE | | |
| comments | 1 | index_comments_on_user_id | 1 | user_id | A | 1 | NULL | NULL | | BTREE | | |
| comments | 1 | index_comments_on_post_id | 1 | post_id | A | 1 | NULL | NULL | | BTREE | | |
+----------+------------+---------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.00 sec)
mysql> insert into comments (user_id, post_id) values(有効なUserID, 有効なPostID);
Query OK
mysql> insert into comments (user_id, post_id) values(上記同様, 上記同様);
ERROR 1062 (23000): Duplicate entry '5-17' for key 'index_comments_on_user_id_and_post_id'
Done.