sql文エラーにつきまして

Question

###前提・実現したいこと
いただいた回答をもとに再度実行したのですがなかなかうまくいきません。
実行したsql全文添付します。

DROP TABLE IF EXISTS meeting_room CASCADE;
DROP TABLE IF EXISTS reserbable_room CASCADE;
DROP TABLE IF EXISTS reservatio CASCADE;
DROP TABLE IF EXISTS usr CASCADE;

CREATE TABLE IF NOT EXISTS meeting_room(
room_id SERIAL NOT NULL PRIMARY KEY,
room_name VARCHAR(255) NOT NULL
);

CREATE TABLE IF NOT EXISTS reservable_room(
reserved_date DATE NOT NULL,
room_id INT4 NOT NULL,
PRIMARY KEY(reserved_date,room_id)
);
CREATE TABLE IF NOT EXISTS reservation(
reservation_id SERIAL NOT NULL PRIMARY KEY,
end_time TIME NOT NULL,
start_time TIME NOT NULL,
reservel_date DATE NOT NULL,
roomid INT4 NOT NULL,
user_id VARCHAR(255) NOT NULL
);

CREATE TABLE IF NOT EXISTS usr(
user_id VARCHAR(255) NOT NULL PRIMARY KEY,
first_name VARCHAR(255) NOT NULL,
last_name VARCHAR(255) NOT NULL,
password VARCHAR(255) NOT NULL,
role_name VARCHAR(255) NOT NULL
);
ALTER TABLE reservable_room ADD CONSTRAINT FK_f4wnx2qj0d59s9tl1q5800fw7 FOREIGN KEY(room_id) REFERENCES meeting_room(room_id);
ALTER TABLE reservation ADD CONSTRAINT FK_p1k4iriqd4eo1cpnv79uvni9g FOREIGN KEY (reserved_date,room_id) REFERENCES reservable_room;
ALTER TABLE reservation ADD CONSTRAINT FK_recqnfjcp370rygd9hjjxjtg FOREIGN KEY (user_id) REFERENCES usr;

下記がエラー文なのですが
なぜ”Cannot add foreign key constraint”になるのかが不明です。

###発生している問題・エラーメッセージ

Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'org.springframework.boot.autoconfigure.orm.jpa.HibernateJpaAutoConfiguration': Unsatisfied dependency expressed through constructor parameter 0; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataSource' defined in class path resource [org/springframework/boot/autoconfigure/jdbc/DataSourceConfiguration$Tomcat.class]: Initialization of bean failed; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataSourceInitializer': Invocation of init method failed; nested exception is org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #9 of URL [file:/Users/kazuanzo/Documents/Reservation/mrs/target/classes/schema.sql]: ALTER TABLE reservable_room ADD CONSTRAINT FK_f4wnx2qj0d59s9tl1q5800fw7 FOREIGN KEY(room_id) REFERENCES meeting_room(room_id); nested exception is java.sql.SQLException: Cannot add foreign key constraint

自分なりに調査したのですが、下記内容が出ました。

【発生する条件】
クエリにシングルコーテーション「'」が含まれている場合。
シングルコーテーションを削除するか、「`」に置換する必要がある

シングルコーテションが文脈に入っておりません。
お手上げになってしまいました。

###該当のソースコード

ここにご自身が実行したソースコードを書いてください

###試したこと
課題に対してアプローチしたことを記載してください

###補足情報(言語/FW/ツール等のバージョンなど)
より詳細な情報

Answer 1

ALTER TABLE reservable_room ADD CONSTRAINT FK_f4wnx2qj0d59s9tl1q5800fw7 FOREIGN KEY(room_id) REFERENCES 参照先テーブル名 (参照先カラム名);

例えば、rootテーブルのidカラムに対して外部キー制約を作成したい場合、以下のようになります。

ALTER TABLE reservable_room ADD CONSTRAINT FK_f4wnx2qj0d59s9tl1q5800fw7 FOREIGN KEY(room_id) REFERENCES room (id);

https://dev.mysql.com/doc/refman/5.6/ja/create-table-foreign-keys.html

[CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name, ...)
REFERENCES tbl_name (index_col_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]

reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION

上の SQL構文の読み方は、以下のページの

データベース名、テーブル名、およびカラム名は多くの場合、...

以降を参考にしてください。
https://dev.mysql.com/doc/refman/5.6/ja/manual-conventions.html

---

ちなみに、

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 '' at line 1

について私が簡単に確認した限りでは、SQL文の末尾に必要な語句が抜けている場合、このエラーメッセージが表示されるようです。

mysql> SELECT * FROM ;
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 '' at line 1

追記

Cannot add foreign key constraint

参照するカラム（reservable_room.room_id）と参照されるカラム（meeting_room.room_id）のデータ型が異なっているためです。
https://dev.mysql.com/doc/refman/5.6/ja/create-table-foreign-keys.html

外部キー内の対応するカラムと、参照されるキーは同様のデータ型を持っている必要があります。整数型のサイズと符号が同じである必要があります。

meeting_room.room_idカラムのデータ型を INT4 に変更するか、reservable_room.room_idカラムのデータ型を BIGINT UNSIGNED に変更してください。
https://dev.mysql.com/doc/refman/5.6/ja/numeric-type-overview.html

SERIAL は BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE のエイリアスです。

---

ちなみに

なぜ”Cannot add foreign key constraint”になるのかが不明です。

とのことですが、たいていの場合、エラーメッセージで検索すればすぐに原因と解決策を見つけられます。
https://www.google.com/search?q=Cannot%20add%20foreign%20key%20constraint

Comments

[kazuhihi]

ありがとうございます！！

Answer 2

外部キー定義時の構文がそもそも違うのではないでしょうか。
こちらを参照しても分かると思うのですが、
外部キー定義時は他テーブルのどの項目を参照するかを指定する必要があります。

REFERENCES句以下は質問者様がどのテーブルと関連を持たせるか次第なので、
回答はここまでとなります。

追記

上記回答で「他テーブルのどの項目を参照するかを指定」と書きましたが、
同一テーブルの他項目でもいいので、追記にて訂正しておきます。

#さらに追記
KiyoshiMotokiさんの追記内容の通り、
データ型を一致させれば(SERIAL → INT4)現在のエラーは解消できますね。

これはアドバイスとなりますが、
余計なトラブルを招かないためにも、
テーブル間で関連付ける項目同士のデータ型は必ず合わせるようにしましょう。

蛇足ですが添付のSQLのソースのままだと、
現在のエラー箇所以降の外部キー定義のALTER TABLEでこけそうなので、
それも合わせて直しておきましょうね。

Answer 3

MySQLであれば、MySQL Workbenchか、MariaDB付属のHeidiSQLを使えば、SQLを直接入力せずにテーブル定義変更のクエリは生成されますので、それを参考にするのもありですね。