二つの外部キーの参照先が重複する場合の書き方が分からない

Question

以下のsqlにて、send_user_idとreceive_user_idはどちらもusers(user_id)を参照しています。
しかし、書き方を変えて試してみてもエラーになってしまいました。
仮にsend_user_idとreceive_user_idの値が被ってしまっても問題はないのですが、どうすればこれが解決できますか？
user_idをラッピングする方法はありそうですが、他にシンプルな書き方があれば是非教えてください。

-- create_users.sql
CREATE TABLE IF NOT EXISTS users(
	user_id BIGINT PRIMARY KEY,
	name TEXT NOT NULL,
	regist_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT(CURRENT_TIMESTAMP AT TIME ZONE 'jst')
);

-- create_constracts.sql
CREATE TABLE IF NOT EXISTS contracts(
	id BIGSERIAL PRIMARY KEY,
	send_user_id BIGINT NOT NULL,
	receive_user_id BIGINT NOT NULL,
	amount BIGINT NOT NULL,
	regist_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT(CURRENT_TIMESTAMP AT TIME ZONE 'jst'),
	consent_at TIMESTAMP WITH TIME ZONE,
	CONSTRAINT user_fk
		-- ここ
		FOREIGN KEY (send_user_id, receive_user_id)
		REFERENCES users(user_id)
		ON DELETE CASCADE
);

試したこと

FOREIGN KEY (send_user_id)
REFERENCES users(user_id)
ON DELETE CASCADE
FOREIGN KEY (receive_user_id)
REFERENCES users(user_id)
ON DELETE CASCADE

FOREIGN KEY (send_user_id, receive_user_id)
REFERENCES users(user_id, user_id)

エラー文(rust sqlx-cli: sqlx migrate runで実行)

error: migration 20220822062308 was previously applied but has been modified

Comments

[maisumakun]

> 書き方を変えて試してみてもエラーになってしまいました。

どんな書き方を試したかと、それでどのようなエラーが出たかはご提示いただけますか？

Answer 1

エラー文(rust sqlx-cli: sqlx migrate runで実行)

エラーメッセージをよく読みましょう。適用済みのマイグレーションを変更したことによるエラーであって、SQL文の書き方とは別問題と思われます。

Comments

[maisumakun]

既存のマイグレーションからさらにテーブルを変更したい場合も、反映済みのマイグレーションは書き換えず、新たなマイグレーションを追加してそちらに書き込む形で進めてください。

[sazi]

そもそも質問のCREATE文自体エラーですけどね。

Answer 2

もしかして：

CREATE TABLE IF NOT EXISTS contracts(
	id BIGSERIAL PRIMARY KEY,
	send_user_id BIGINT NOT NULL,
	receive_user_id BIGINT NOT NULL,
	amount BIGINT NOT NULL,
	regist_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT(CURRENT_TIMESTAMP AT TIME ZONE 'jst'),
	consent_at TIMESTAMP WITH TIME ZONE,
	CONSTRAINT user_fk_1
		-- ここ
		FOREIGN KEY (send_user_id)
		REFERENCES users(user_id)
		ON DELETE CASCADE
	CONSTRAINT user_fk_2
		-- ここ
		FOREIGN KEY (receive_user_id)
		REFERENCES users(user_id)
		ON DELETE CASCADE
);

とか？

CREATE TABLE IF NOT EXISTS contracts(
	id BIGSERIAL PRIMARY KEY,
	send_user_id BIGINT NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
	receive_user_id BIGINT NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
	amount BIGINT NOT NULL,
	regist_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT(CURRENT_TIMESTAMP AT TIME ZONE 'jst'),
	consent_at TIMESTAMP WITH TIME ZONE
);

とか？
（あくまで机上のコード）

Comments

[asobinin]

解決致しました。指摘の通りマイグレーションに対する知識が浅かったです。
FOREIGNキーに関しては、一つずつ指定しなければいけないのですね。
ありがとうございました。

CONSTRAINT user_fk
FOREIGN KEY (send_user_id)
REFERENCES users(user_id)
ON DELETE CASCADE,
FOREIGN KEY (receive_user_id)
REFERENCES users(user_id)
ON DELETE CASCADE

Answer 3

send_user_idとreceive_user_idが同じだったらこれで良いかは？

    CONSTRAINT user_fk
        FOREIGN KEY (send_user_id, receive_user_id)
        REFERENCES users(user_id)
        ON DELETE CASCADE

      ↓

    CONSTRAINT user_send_user_id_fk
        FOREIGN KEY (send_user_id)
        REFERENCES users(user_id)
        ON DELETE CASCADE, 
    CONSTRAINT user_receive_user_id_fk
        FOREIGN KEY (, receive_user_id)
        REFERENCES users(user_id)
        ON DELETE CASCADE