外部キー(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;
  • このエントリーをはてなブックマークに追加

関連記事

no image

LIKEでカンマのついた数字を(FINF_IN_SET)

ちょっとてこづってしまったので備忘録。 次のようなカンマ区切りのカラムがあったとき、8は抽出したが88は抽出したくないとき ROW1: 10,5,2,8, ROW2: 2,7,9,65 ROW3: 9

no image

MySQL WorkbenchでSSH経由でDBに接続

さくらインターネットのデータベースにはクライントマシンから直接TCPで接続を試みると10060エラーなどになってしまいますが、SSH経由で接続は可能のようです。 SSH経由の接続設定 MySQ

no image

vs SQLite コマンド対応表

SQLiteって今までまじめに触ることがなかったのでおさらいしてみました。 バージョンは、"3.6.20"。コマンドの使用方法は".help"オプションで一覧を表示することができます。 $ s

no image

SELECT COUNTでNULLだとカウントしてくれない

> SELECT COUNT(column) FROM table; の場合、columnデータがNULLの場合カウントしてくれません。 > SELECT COUNT(*) F

ユーザ作成と権限設定

MySQLでは、ログイン元のホストごとにユーザ名、パスワードを設定可能です。また、ユーザごとに細かくオペレーションの権限を設定することができます。 ユーザ作成 ユーザ作成は、「ユーザ名」「パス

Comment

  1. これだとすると、下記現象でpgsqlでは通るのかも kwski.net/mysql/602/ >MySQLでは外部キーを設定すると自動的にインデックスが作成されるようです。 >逆に外部キーが設定されたままインデックスを削除しようとするとエラーになります。

Message

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

*

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください

    PAGE TOP ↑