0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

【wordpressプラグイン作成】unsingedを付けたらSQL文が通った

Last updated at Posted at 2022-09-12

wordpressでテーブルを生成するプラグインを作成する際、
既存のwp-usersテーブルの主キーカラム(ID)を参照する外部キーを設定しようとしたところ、以下のエラーが排出されました。

Can't create table wp-01.hoge (errno: 150 "Foreign key constraint is incorrectly formed") for query

wp-userテーブルのIDカラムに合わせて、参照元のカラムの型をbigint型にし、unsignedの記述を追記したらSQL文が通るようになりました。

以下、修正前と修正後です。

before_modify.php
$sql = CREATE TABLE hoge (
            hoge_id    int not null PRIMARY KEY AUTO_INCREMENT,
            user_id    int not null,   
            CONSTRAINT ga FOREIGN KEY (user_id) REFERENCES wp_users(ID)
        );
modyfied.php
$sql = CREATE TABLE hoge (
            hoge_id    int not null PRIMARY KEY AUTO_INCREMENT,
            user_id    bigint unsigned not null,   
            CONSTRAINT ga FOREIGN KEY (user_id) REFERENCES wp_users(ID)
        );
0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?