外部キー(foreign key)を作成するとインデックスもついてくる

MySQLでは外部キーを設定すると自動的にインデックスが作成されるようです。
逆に外部キーが設定されたままインデックスを削除しようとするとエラーになります。
#1553 - Cannot drop index 'user_id': needed in a foreign key constraint

外部キー設定

alter table profiles 
	add 
		foreign key (user_id) 
		references users (id);

上記を実行すると、以下の名前で外部キー制約とインデックスが作成されます。

• 外部キー名：profiles_ibfk_1
• インデックス名：user_id

外部キー名は指定しない場合は、「テーブル名」_ibfk_[n] ([n]は通番)となります。ちなみにibfkは「InnoDB Foreign key」らしいです。

外部キー設定・追加
Using FOREIGN KEY Constraints

外部キー名を指定する場合

この場合、外部キー名・インデックス名ともfk_profiles_user_id_users_idになります。

alter table profiles 
	add 
		constraint fk_profiles_user_id_users_id
		foreign key (user_id) 
		references users (id);

外部キーの一般の命名っていろいろあるみたいですが、感覚的に以下のようにしています。
fk_[参照元テーブル]_[参照元カラム]_[参照先テーブル]_[参照先カラム]
ただし、64文字まで→CONSTRAINT_NAME

> desc information_schema.TABLE_CONSTRAINTS;
+--------------------+--------------+------+-----+---------+-------+
| Field              | Type         | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| CONSTRAINT_CATALOG | varchar(512) | NO   |     |         |       |
| CONSTRAINT_SCHEMA  | varchar(64)  | NO   |     |         |       |
| CONSTRAINT_NAME    | varchar(64)  | NO   |     |         |       |
| TABLE_SCHEMA       | varchar(64)  | NO   |     |         |       |
| TABLE_NAME         | varchar(64)  | NO   |     |         |       |
| CONSTRAINT_TYPE    | varchar(64)  | NO   |     |         |       |
+--------------------+--------------+------+-----+---------+-------+

外部キー名・インデックス名を指定

// インデックス作成
create unique index idx_user_id on profiles(user_id);
// uniqeuはオプション

// 外部キー作成
alter table profiles 
    add constraint fk_profiles_user_id_users_id
    foreign key (user_id)
    references users (id);

show index profiles

外部キーの情報取得

設定外部キーの一覧取得

select * from information_schema.TABLE_CONSTRAINTS
    where CONSTRAINT_TYPE = 'FOREIGN KEY';

設定外部キーの詳細取得

select * from REFERENTIAL_CONSTRAINTS;

Related

hasOne アソシエーション recursive設定によるfind()性能改善 recursive設定によるfind()性能改善 LIKEでカンマのついた数字を(FINF_IN_SET) SELECT COUNTでNULLだとカウントしてくれない ユーザ登録(仮登録・メール・本登録) 「isUnique」バリデーション selectボックスを実装