MySQL道普請便り

第109回 主キーを必須にさせる

この記事を読むのに必要な時間:およそ 2.5 分

MySQLはバージョン8.0.13からsql_require_primary_keyというオプションが追加されました。このオプションを追加することで,作成するテーブルに主キー(プライマリキー)をつけなければ作成できないような制限を入れることができます。今回はこのオプションについて説明していきます。なお,検証環境はCentOS 7,MySQL 8.0.18になります。

sql_require_primary_keyについて

sql_require_primary_keyは,my.cnfなどの設定ファイルに設定するか,SET構文を用いて設定することができます。設定はSELECT構文でシステム変数を確認するか,SHOW VARIABLES構文で確認できます。

mysql> SELECT @@sql_require_primary_key;
+---------------------------+
| @@sql_require_primary_key |
+---------------------------+
|                         1 |
+---------------------------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'sql_require_primary_key';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| sql_require_primary_key | ON    |
+-------------------------+-------+
1 row in set (0.08 sec)

sql_require_primary_keyが設定されている状態で主キーのないテーブルを作成しようとすると,下記のようなエラーが表示され,DDLが実行できません。作成する場合は主キーを設定するか,sql_require_primary_keyのオプションをOFFにする必要があります。

mysql> CREATE TABLE no_pktable ( id int, name varchar(256) );
ERROR 3750 (HY000): Unable to create or change a table without a primary key, when the system variable 'sql_require_primary_key' is set. Add a primary key to the table or unset this variable to avoid this message. Note that tables without a primary key can cause performance problems in row-based replication, so please consult your DBA before changing this setting.

もし,事前に作成していたテーブルがすでに主キーがない状態で,このオプションをONにした場合は,既存のテーブルは影響を受けず,今後作成するテーブルに対してのみエラーとなります。

ALTER TABLE構文とCREATE TEMPORARY TABLE構文

sql_require_primary_keyをONにしている場合は,テーブルの作成だけでなく,以下の点も注意が必要です。

  • ALTER TABLE構文で主キーを削除する
  • CREATE TEMPORARY TABLE構文で一時テーブルを作成する

ALTER TABLE構文で主キーを削除しようとした場合でも,このオプションにより主キーを削除することができません。また,ユニーク制約 + NOT NULL 制約のあるカラムで主キーを代替することもできません。必ず主キーが必須となってしまいます。もし主キーを変更したい場合は,一時的にこのオプションをOFFにする,または,ALTER TABLE構文の中に主キーの削除と追加の構文を記述することで回避することができます。

また,一時テーブルを作成する場合も主キーが必須となってしまいます。すでにバッチなどで一時テーブルを利用している場合は事前に一時テーブルのDDLを確認し,主キーがついているか確認しましょう。

対応のストレージエンジン

対応するストレージエンジンですが,主キーが設定できるストレージエンジン(MyISAMやInnoDBなど)に対しては利用可能です。CSVストレージエンジンのような主キーを設定できないストレージエンジンで利用しようとするとエラーになりますし,主キーなしで作成しようとするとsql_require_primary_keyの制限にあたり,作成することができません。どうしても利用したい場合は,このオプションを一時的にOFFにする必要があります。

各テーブルで利用しているストレージエンジンはinfomation_schema.TABLES.ENGINEで確認することができるので,設定する前は一度確認してみましょう。

mysql> CREATE TABLE csv_pktable(id int primary_key, name varchar(256)) ENGINE=CSV;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'primary_key, name varchar(256)) ENGINE=CSV' at line 1
mysql> CREATE TABLE csv_pktable(id int , name varchar(256)) ENGINE=CSV;
ERROR 3750 (HY000): Unable to create or change a table without a primary key, when the system variable 'sql_require_primary_key' is set. Add a primary key to the table or unset this variable to avoid this message. Note that tables without a primary key can cause performance problems in row-based replication, so please consult your DBA before changing this setting.

レプリケーション

sql_require_primary_keyがマスター側でOFF,スレーブ側でONの状態で,マスターに主キーがないテーブルを作成しようとする場合でも,スレーブ側で主キーのないテーブルが作成されます。

マスター側で生成されたバイナリログを確認すると,/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;というイベントを確認することができます。これはMySQLのバージョンが8.0.13以上の場合のみ実行されるため,スレーブ側では一時的にOFFになり,レプリケーションが通るという理屈です。

コメントを使ったMySQLバージョンによる実行については,公式ドキュメントのコメントの構文をご確認ください。

ただし,バージョンアップなどを想定したMySQL 5.7(マスター)とMySQL 8.0(スレーブかつsql_require_primary_key=1)のようなレプリケーションを利用している場合は注意が必要です。MySQL 5.7側で作成した主キーのないテーブルが,MySQL 8.0(スレーブ)側でエラーになって,レプリケーションが止まってしまいます。止まってしまった場合は一度OFFにしてレプリケーションを再開し,問題のクエリが通ったことが確認できたら再びONに戻してから再度レプリケーションをSTOP・STARTする必要があります。ここで,レプリケーションをSTOP・STARTする理由は,再起動しないとsql_require_primary_keyがOFFのままのコネクションが残ってしまうためです。必要であればレプリケーションをSTOP・STARTしましょう。

マスターのバイナリログ

# at 714
#191029  9:28:06 server id 39355  end_log_pos 859 CRC32 0xc904510f      Query   thread_id=13    exec_time=0     error_code=0    Xid = 48
SET TIMESTAMP=1572308886/*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
CREATE TABLE pktable(id int primary key, name varchar(256))
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

まとめ

今回はsql_require_primary_keyというMySQL 8.0から新たに導入されたオプションについて解説しました。主キーがないテーブルを作成しないように,運用ではなくシステムでカバーすることはとても便利です。もしMySQL 8.0を導入する際は,合わせてこのオプションをどうするか検討してみてください。

著者プロフィール

深町日出海(ふかまちひでみ)

GMOメディア株式会社のデータベースエンジニア。主にOracleとMySQLを担当。得意なプログラム言語はJava。MySQLの好きなところはTABLEやINDEXの識別子に64byteまで使えるところ。

Twitter:@lhfukamachi