Unique Constraint با شرط در اوراکل

چگونه می توان  Unique Constraint با شرط در اوراکل ایجاد کرد؟ در این خصوص دو روش پیشنهاد می شود که در زیر توضیحات آن داده می شود.

روش اول

فرض می کنیم که شما دوفیلد در جدول خود دارید که باید مقادیر این دوفیلد بصورت یکتا باشد به شرطی که مقادیر فیلد دیگر شامل یک مجموعه ای از اطلاعات باشد و در غیر اینصورت نه.

برای درک بهتر با مثال مطرح می شود:

یک جدول در اوراکل دارید که شامل سه فیلد f1,f2,f3 می باشد . حال می خواهید اگر مقادیر فیلد f1 شامل (۱و۲و۳ ) بود فیلد f2, f3 بصورت یکتا Unique باشد

create table test
(
f1 number
,f2 varchar2(50)
,f3 number(11)
);

اضافه کردن یک unique function-based index در اوراکل

create unique index test_idx
on test (
case when f1 in (1, 2, 3) then f2
end,
case when f1 in (1, 2, 3) then f3
end
);

حال دو تا حالت بوجود می آید:

• اول Null,null وقتی که مقدار فیلد f1 شامل (۱, ۲, ۳) نباشد یعنی اینکه حالت یکتا بودن غیرفعال می شود.

• دوم مقدار فیلد f2,f3 وقتی که فیلد f1 شامل (۱, ۲, ۳) باشد یعنی اینکه حالت کنترل یکتا بودن فعال می شود.

نکته: مقادیر null,null در Index ظاهر نمی شوند و وقتی که دیتا وجود داشته باشد در Index ظاهر می گردد.

می توانید با مقادیر زیر این موضوع را چک نمایید.

۴,’saeed’,۱۵۰۰۰
۳,’armin’,۱۴۰۰۰
۲,’saeed’,۱۰۰۰۰
۴,’saeed’,۱۵۰۰۰
۳,’armin’,۱۴۰۰۰ ===در این حالت فعال می شود و خطای یکتایی ظاهر می گردد

ORA-00001: unique constraint (TEST.TEST_IDX) violated

روش دوم

در روش اول این مسئله را بوسیله unique function-based index در اوراکل حل کردیم. حال در این قسمت می خواهیم که بوسیله virtual columns در اوراکل این کنترل را ایجاد کنیم.

در این روش از یک Constraint واقعی استفاده می کنیم. بدین منظور دوتا فیلد به جدول ایجاد می کنیم بصورت زیر:

alter table test
add (f2_unq varchar2(50 byte) generated always as ((case when f1 in (1, 2, 3) then f2 end)));

alter table test
add (f3_unq number(11 byte) generated always as ((case when f1 in (1, 2, 3) then f3 end)));

حال باید یک Unique Constraint در اوراکل بر روی آن دو فیلدی جدید که ایجا کردیم بسازیم.

alter table test
add constraint f2_f3_unique
unique (f2_unq,f3_unq);

کار ما به اتمام رسیده است و می توانید با مقادیر مثال قبلی آن را چک کنید.

امیدوار هستم مورد استفاده دوستان قرار گیرد.