プロモーションコードを期間内においてユニークにするには

Question

プロモーションコードを管理するテーブルを作成しています

CREATE TABLE promotion_codes(
    id SERIAL PRIMARY KEY,
    code VARCHAR(25) NOT NULL,
    start_at TIMESTAMP WITH TIME ZONE NOT NULL,
    expired_at TIMESTAMP WITH TIME ZONE NOT NULL,
);

promotion_codes.codeがプロモーションコード本体で有効期間がstart_at〜expired_atを想定しています。

ここで、codeに対してUNIQUE制約をつけたいのですが、start_at〜expired_atの期間内においてUNIQUEにするという制約は可能なものでしょうか？CHECK制約で制限可能であればご教示ください。

よろしくお願いいたします。

Comments

[maisumakun]

わざわざ中途半端な制約をかけるより、「全期間を通してUNIQUE」のほうが管理しやすいかと思いますが、そうできない理由はありますでしょうか。

[m0a]

例えば株主番号など、決められた文字列をプロモーションコードとして用いなくてはいけない場合があります。その場合毎年同じ文字列を使うことになるのでUNIQUE制約に対して期限を設けたいのです。

[Y.H.]

> start_at〜expired_atの期間内においてUNIQUE
期間は重なることはあるのでしょうか？
1. 2019/07/01～2019/07/31
2. 2019/07/15～2019/08/15
のような場合、2019/07/15～2019/07/31 の期間は、1,2を通してユニークであるとか。

Answer 1

条件は
・start～end がかぶる期間において、同一のプロモーションコードがあってはならない
ですか？

制約でどうこうではなくてトリガで制御した方がよいような気がします。
before insert も before update も使えるはずですから、その中で

• 登録しようとしているプロモーションコードと同一で、
• 登録しようとしている start が start ～ end とかぶるか、end が start ～ end とかぶる

ならば登録失敗。

更新の場合は「更新しようとしている自分自身を除く」必要がありますが、これは「かぶる件数をカウントし、それが 2 より大きいなら失敗」（登録の場合は 1 より大きいと失敗）とすればほぼほぼ同一処理で済むでしょうか。

Comments

[m0a]

ありがとうございました。結局無理のある設計だと思い通常のUNIQUE属性ですすめることにしました。

Answer 2

別のカラムで「ABCDEF20191203」みたいに、プロモーションコードと日付をくっつけたデータを持っておいて、そのカラムをUNIQUEにするとかどうでしょう。

Answer 3

詳細条件が分かりませんが、5.3.3 一意性制約 で、複数列を指定することで、制約することもできそうですね。

Answer 4

コードは書きませんが、コードのテーブルを

｜コード｜タイムスタンプ｜

のカラムに設定しておいて、このタイムスタンプでif文で論理演算をしないといけないと思います。
ミリ秒をタイムスタンプにとって重複しないようにコードを発行すればいいと思います。