外部キー(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;
関連記事
-
LIKEでカンマのついた数字を(FINF_IN_SET)
ちょっとてこづってしまったので備忘録。 次のようなカンマ区切りのカラムがあったとき、8は抽出したが88は抽出したくないとき ROW1: 10,5,2,8, ROW2: 2,7,9,65 ROW3: 9
-
MySQL WorkbenchでSSH経由でDBに接続
さくらインターネットのデータベースにはクライントマシンから直接TCPで接続を試みると10060エラーなどになってしまいますが、SSH経由で接続は可能のようです。 SSH経由の接続設定 MySQ
-
vs SQLite コマンド対応表
SQLiteって今までまじめに触ることがなかったのでおさらいしてみました。 バージョンは、"3.6.20"。コマンドの使用方法は".help"オプションで一覧を表示することができます。 $ s
-
SELECT COUNTでNULLだとカウントしてくれない
> SELECT COUNT(column) FROM table; の場合、columnデータがNULLの場合カウントしてくれません。 > SELECT COUNT(*) F
-
ユーザ作成と権限設定
MySQLでは、ログイン元のホストごとにユーザ名、パスワードを設定可能です。また、ユーザごとに細かくオペレーションの権限を設定することができます。 ユーザ作成 ユーザ作成は、「ユーザ名」「パス
- PREV
- JDK 7 インストール
- NEXT
- Twenty Fourteen 「投稿者」を非表示
Comment
[…] 参考 […]
[…] http://kwski.net/mysql/602/ […]
これだとすると、下記現象でpgsqlでは通るのかも kwski.net/mysql/602/ >MySQLでは外部キーを設定すると自動的にインデックスが作成されるようです。 >逆に外部キーが設定されたままインデックスを削除しようとするとエラーになります。
“ちなみにibfkは「InnoDB Foreign key」らしいです。”
外部キー(foreign key)を作成するとインデックスもついてくる http://t.co/wjecczL5SO
“MySQL - 外部キー(foreign key)を作成するとインデックスもついてくる” http://t.co/Kz1f0wfx9L