【MySQL】外部キーを設定/追加/削除する方法

ここではMySQLで、外部キーの設定/追加/削除する方法を紹介しています。

外部キーの設定/追加/削除する方法

今回は、次のSQLで2つのテーブルを準備して外部キーを追加/設定してみました。

tab1テーブルのdeptカラムに外部キーを追加して、tab2テーブルのdeptカラムを参照したいと思います。

CREATE TABLE tab1(
 emp_id varChar(10)
 ,emp_name varChar(100)
 ,dept varChar(10) 
 ,age int
 );

CREATE TABLE tab2(
 dept varChar(10) PRIMARY KEY
 ,dept_name varChar(100)
);

外部キーの設定：CREATE TABLE文

外部キーはCREATE TABLE文でテーブル作成時に設定することが出来ます。

外部キーの基本構文は次の通りです。

FOREIGN KEY 外部キー名(参照元カラム名) REFERENCES 参照先テーブル名(参照先カラム名)

tab1で外部キーを作製すると次のように書くことが出来ます。

CREATE TABLE tab1(
 emp_id varChar(10)
 ,emp_name varChar(100)
 ,dept varChar(10) 
 ,age int
 ,FOREIGN KEY fk_dept(dept) REFERENCES tab2(dept)
 );

外部キーの追加：ALTER TABLE文

既に存在するテーブルに外部キーを作製する場合は、ALTER TABLE文を使います。

ALTER TABLE テーブル名 ADD FOREIGN KEY 外部キー名(参照元カラム名) REFERENCES 参照先テーブル名(参照先カラム名);

tab1で外部キーを作製すると次のように書くことが出来ます。

ALTER TABLE tab1 ADD FOREIGN KEY fk_dept(dept) REFERENCES tab2(dept);

外部キーの削除：ALTER TABLE文

外部キーを削除するには、ALTER TABLE文を使います。

まずはテーブルに作成されている外部キーの制約名を確認します。

SHOW CREATE TABLE tab1;

実行すると次のようにCREATE文で制約名を確認することが出来ます。

CREATE TABLE `tab1` (
   `emp_id` varchar(10) DEFAULT NULL,
   `emp_name` varchar(100) DEFAULT NULL,
   `dept` varchar(10) DEFAULT NULL,
   `age` int(11) DEFAULT NULL,
   KEY `fk_dept` (`dept`),
   CONSTRAINT `tab1_ibfk_1` FOREIGN KEY (`dept`) REFERENCES `tab2` (`dept`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8

ALTER TABLE文で外部キーを削除する基本構文は次の通りです。

ALTER TABLE テーブル名 DROP FOREIGN KEY 制約名;

tab1の外部キーを削除する場合は次のように「tab1_ibfk_1」を指定して削除します。

ALTER TABLE tab1 DROP FOREIGN KEY tab1_ibfk_1;