محدودیت های جامعیتی

محدودیت های جامعیتی (integrity constraint)

در هر محیط عملیاتی همیشه مجموعه‌ای از قواعد معنایی،بامحدودیتهای جامعیتی، وجود دارند. چنانچه داده‌های ذخیره شده در بانک اطلاعاتی همواره چنین محدودیت‌هایی را رعایت کنند، جامعیت پایگاه داده تضمین می‌گردد. جامعیت پایگاه داده‌ها به معنی صحت، دقت و سازگاری داده‌ها ذخیره شده در پایگاه داده‌ها در تمام لحظات است. هر سیستم پایگاه داده باید بتواند integrity پایگاه داده را مدیریت و تضمین کند.
محدویت های جامعیتی مانع از ورود اطلاعات نا معتبر به داخل جداول پایگاه داده می شود. در oracle پنج نوع مختلف محدودیت جامعیتی داریم که به شرح زیر می باشند.

• Not Null
• Primary Key
• Foreign Key
• Check
• Unique

محدودیت جامعیتی Not Null

محدودیت جامعیتی Not Null یک ستون را مجبور می کند که مقدار خالی قبول نکند.
محدودیت Not Null یک ستون را وادار می کند که همیشه حاوی مقداری باشد، بدان معنا که شما نمی توانید record جدیدی را وارد کرده یا تغییر دهید، بدون اینکه مقداری را در آن اضافه کرده باشید.
به صورت پیش فرض کلیه ی ستون های موجود در یک جدول می توانند مقادیر null در خود داشته باشند. برای اینکه مطمئن شویم ستون مد نظر ما همیشه مقداری را در خود داشته باشد و خالی یا blank باقی نماند، لازم است یک محدویت Not Null برای آن ستون تعریف کنیم.
لازم است همیشه در تعریف و اعمال محدویت NOT NULL بر روی ستون یا ستون های مد نظر توجه و دقت لازم را داشته باشیم. به عنوان مثال، در جدولی به نام employees ممکن است تعدادی از کارکنان دارای کمیسیون باشند و تعدادی دیگر فاقد کمیسیون باشند. حال اگر NOT NULL constraint روی ستون COMM اعمال کنید، دیگر قادر نخواهید بود برای آن دسته از کارکنانی که دارای کومیسیون نیستند (مقدار NULL را دارند)، سطر جدید درج یا وارد کنید. از این رو توصیه می گردد از محدویدت Not Null تنها در موارد خاص استفاده شود.

کلید اصلی (primary key)

کلید اصلی هر رکورد در پایگاه داده را به طور منحصر به فردی مشخص می کند.
کلید اصلی باید دارای مقادیر منحصر به فردی باشند.
ستون primary key نمی تواند حاوی مقدار null باشد.
هر جدول باید حاوی یک کلید اصلی باشد و هر جدول تنها می تواند یک کلید اصلی داشته باشد.
در واقع هر جدول می تواند ویژه ی خود یک کلید اصلی داشته باشد که هر سطر را به طور منحصر بفرد تعریف و شناسایی کرده و همچنین مطمئن می شود که هیچ سطر تکراری یا مشابهی در جدول وجود ندارد. به هنگام انتخاب کلید اصلی، توصیه می شود از رهنمون های زیر پیروی کنید.
تا حد ممکن سعی کنید از ستونی استفاده کنید که دارای sequence number باشد.
استفاده ی خود از primary key های مرکب را تا حد امکان کاهش دهید. یکی از دلایل آن این است که مقادیر primary key های مرکب طولانی بوده و امکان تخصیص آن ها با استفاده از sequence number ها وجود ندارد.
از ستونی استفاده کنید که مقادیر داده های آن منحصر بفرد باشد، زیرا که مقصود اصلی استفاده از primary key شناسایی و مشخص کردن یک سطر یا رکورد در جدول به گونه ای منحصر به فرد است.
ستونی را انتخاب کنید که مقادیر داده های آن هیچ گاه تغییر نمی کنند. مقدار یک primary key فقط و فقط برای مشخص کردن یک سطر در جدول بکار می رود و داده های آن نباید هیچگاه برای هیچ منظور دیگری مورد استفاده قرار گیرد. از این رو مقادیر primary key ها را نباید تغییر داد یا در صورت لزوم این کار را فقط در شرایط خاص انجام داد.
ستونی را انتخاب کنید که مقدار null نداشته باشد. primary key، همان طور که در تعریف آن ذکر شد، به هیچ سطر یا رکوردی در هیچ ستونی که متعلق به primary key است، اجازه ی نمی دهد مقدار null داشته باشد.
آن ستونی را انتخاب کنید که کوتاه بوده و می تواند عدد در خود جای دهد. Primary key هایی که کوتاه هستند، تایپ آن ها نیز آسان می باشد. می توانید با استفاده از sequence number به راحتی primary key های عددی تولید کنید.
به منظور تعریف و اعمال primary key بر روی جدول مورد نظر، از ساختار دستوری زیر استفاده می کنیم:

Alter table emp add constraint emppk primary key (empno); ‎

دستور فوق تنها زمانی با موفقیت اجرا می گردد که هیچ مقدار تکراری در ستون EMPNO وجود نداشته باشد. چنانچه ستون نام برده دارای حتی یک مقدار تکراری باشد، دستور بالا با شکست مواجه شده و oracle پیغام خطایی مبنی بر عدم تطبیق مقادیر برمی گرداند.
هر گاه که یک primary key جدید ایجاد می کنید، oracle به صورت خودکار یک اندیس یا شمارنده در آن جدول ایجاد می کند. چنانچه از قبل اندیسی در آن ستون وجود داشته باشد، oracle فقط و فقط از همان اندیس استفاده می کند.

کلید خارجی ( foreign key)

foreign key (کلید خارجی) در یک جدول اشاره به primary key (کلید اصلی) در جدول دیگر دارد. محدودیت کلید خارجی برای جلوگیری از اقداماتی که ارتباط بین دو جدول را مختل می کند بکار می رود.
همچنین محدودیت کلید خارجی از ورود داده های نا معتبر و غیر مجاز به ستون کلید خارجی جلوگیری می کند، زیرا داده ها باید یکی از مقادیر جدولی باشند که کلید خارجی به آن ارجاع داده شده است.
بر روی هر ستونی که محدودیت FOREIGN KEY اعمال شود، مقادیر موجود در آن ستون باید به مقادیر موجود در جدول دیگر اشاره کنند. یک ستون foreign key می تواند به ستون primary key و unique key دیگر جدول ها اشاره کند. این ارتباط بین primary و foreign key تحت عنوان رابطه ی پدر-فرزندی (parent-child relationship) نیز شناخته می شود. جدولی که حاوی کلید اصلی یا همان primary key باشد، جدول پدر (parent table) خوانده می شود و جدولی که حامل کلید خارجی/foreign key باشد، جدول فرزند (child table) اطلاق می گردد. از این نوع رابطه به عنوان referential integrity (جامعیت ارجاعی) نیز یاد می شود.
مقدار موجود در کلید اصلی بایستی در رابطه مرجع وجود داشته باشد و یا تهی باشد.
تصویر زیر رابطه ی پدر- فرزندی را به زیبایی نمایش می دهد.
اینجا EMPNO در جدول attendance یک کلید خارجی است که به ستون EMPNO جدول EMP اشاره دارد.

Alter table attendance add constraint empno_fk ‎

        ‎     Foreign key (empno) references emp(empno);‎

دستور بالا تنها در صورتی با موفقیت اجرا می شود که ستون EMPNO در جدول ATTENDANCE دربردارنده ی مقادیری باشد که در ستون EMPNO جدول EMP موجود می باشند. چنانچه هر مقداری از ستون EMPNO جدول EMP در ستون EMPNO جدول ATTENDANCE موجود نباشد، دستور فوق ناموفق بوده و پیرو آن oracle پیغام خطایی مبنی بر عدم تطبیق مقادیر (non-compliant values) بازمی گرداند.
زیر برخی از نکات مهم که باید درباره ی جامعیت ارجاعی به خاطر داشته باشید، تشریح شده است.
به یاد داشته باشید که در صورت وجود رکورد فرزند (child record)، نمی توان رکورد پدر (parent record) را حذف کرد. در صورت لزوم، ابتدا باید رکورد فرزند را حذف کرد، سپس به حذف رکورد پدر اقدام نمود. در مثال بالا، امکان حذف سطر کارکن شماره ی 101 وجود ندارد، زیرا که سطر فرزند (child row) آن در جدول attendance موجود می باشد. اما می توان سطر کارکن شماره ی 102 را حذف کرد، زیرا که هیچ سطر فرزندی از آن در جدول ATTENDANCE وجود ندارد. اگر foreign key را با استفاده از گزینه ی ON DELETE CASCADE حذف کنید، در آن صورت امکان حذف رکورد پدر فراهم می آید و تمامی رکوردهای فرزند آن نیز دنباله روی آن به صورت خودکار پاک می شوند.
به منظور تعریف یک محدودیت کلید خارجی با استفاده از گزینه ی ON DELETE CASCADE، بایستی دستور زیر را بنویسید.

Alter table attendance add constraint empno_fk ‎

        ‎          Foreign key (empno) references emp(empno)‎

        ‎              On delete cascade;‎

ON DELETE SET NULL یگ امکان جدید است که برای اولین بار در ویرایش 9i پایگاه داده ی oracle، ارائه گردید. کارکرد گزینه ی ذکر شده این است که با حذف رکورد پدر، مقدار foreign key را null می دهد.
جهت تعریف محدودیت foreign key با استفاده از گزینه ی ON DELETE SET NULL، کافی است دستور زیر را بکار ببرید.

Alter table attendance add constraint empno_fk ‎

        ‎          Foreign key (empno) references emp(empno)‎

        ‎              On delete set null;‎

همچنین باید به خاطر داشته باشید که بدون حذف محدودیت foreign key از جدول attendance، نمی توان جدول پدر را حذف کرد. با این حال اگر گزینه ی CASCADE CONSTRAINTS را در دستور DROP TABLE خود بکار ببرید، oracle به صورت خودکار اول ارجاعات (references) و در مرحله ی بعد خود جدول را حذف می کند.

checkمحدودیت

محدودیت check مشخص می کند که چه بازه یا دامنه ای از مقادیر و داده با چه ویژگی هایی می توانند در ستون جدول پایگاه داده قرار بگیرند.
همچنین می توان برای جدول با توجه به ستونهایش محدودیت check ایجاد کرد.
اگر محدودیت CHECK را برای یک ستون تکی تعریف کنید تنها مقادیر خاصی را برای این ستون می توان وارد کرد.
اگر محدودیت CHECK را روی یک جدول (ترکیبی از ستون ها) تعریف کنید این محدودیت می تواند ستون های خاصی از جدول را بسته به مقادیر موجود در دیگر ستون ها در همان سطر محدود کند.
به عنوان نمونه، در جدول attendance مثال بالا، ستون DAYS نمی تواند ( نباید) مقداری بیش از 31 در خود بپذیرد. برای این منظور لازم است یک محدودیت CHECK همانند دستور زیر بنویسید.

 Alter table attendance add constraint dayscheck

        ‎              Check (days <= 31);‎

به همین شیوه، اگر می خواهید درآمد های وارد شده در ستون SAL جدول employee بین مقادیر 1000 تا 3000 باشد، می توانید یک محدودیت CHECK روی جدول EMP به ترتیب زیر تعریف کنید.

alter table emp add constraint sal_check ‎

        ‎                   check (sal between 1000 and 20000);‎

می توانید هر تعداد محدودیت CHECK که مایلید روی یک ستون مجزا، تعریف کنید.

محدودیت UNIQUE KEY **

محدودیت unique key بسیار شبیه به محدودیت primary key است، اما تفاوت هایی نیز بین این دو وجود دارد که به شرح زیر می باشد.

شباهت ها

هر دوی این کلید ها برای به اجرا در آوردن جامعیت داده ها به کار گرفته می شوند. در حقیقت هر دو، نوعی قید یا محدودیت می باشند، در واقع قیود مکانیزم استانداردی را برای oracle آماده می کنند تا جامعیت داده ها توسط آنها اعمال شود.
نوع ستون parent-key در جدول parent (در یک قید foreign key) حتما باید منحصر بفرد باشد. پس از هر دو کلید یکتا و اولیه می توانیم به عنوان یک parent-key یا referenced key استفاده کنیم.
هر دو برای جلوگیری از وارد شدن نمونه های تکراری (duplicate) مورد استفاده قرار می گیرند. یعنی هر دو برای اعمال uniqueness ستون یا ستونهایی (در صورت ترکیبی بودن) بکار گرفته می شوند.
زمانی که یک قید primary key یا unique key ایجاد می کنیم یک اندیس منحصر بفرد توسط موتور پایگاه داده ساخته می شود. از این اندیس برای سرعت بخشی به query هایی که بر اساس این ستونها هستند مورد استفاده قرار می گیرد.
اگر مقادیر تکراری در جدول درج شده باشند (در ستون یا ترکیبی از ستونهای مورد نظر) نمی توانیم این محدودیت ها (constraint) را ایجاد کنیم.
هر دوی این محدودیت ها می توانند از ترکیب چند ستون ایجاد شوند.

تفاوت ها

کلید اصلی اجازه نمی دهد که مقدار NULL جزئی از مقادیر کلید باشد، یعنی ما اجازه نداریم که ستون هایی که در PK شرکت دارند را Nullable در نظر بگیریم. ولی برعکس آن unique key به ما این امکان را می دهد که مقادیر NULL را برای ستون یا ستونهایی (در صورتی که قید مرکب (compound) باشد که در UK شرکت دارند درج کنیم. البته مقادیر NULL مثل تمام مقادیر دیگر تنها یکبار قابل درج شدند هستند. مثلا اگر ستون A دارای کلید منحصر بفرد (unique key) و allow null باشد نمی توانیم دو رکورد با مقدار NULL در ستون A داشته باشیم.
چندین محدودیت unique می توانیم برای یک جدول ایجاد کنیم (که آن وابسته به محدودیت تعداد شاخصی است که در یک جدول می توانیم ایجاد کنیم) ولی تنها یک قید primary key جدول می تواند داشته باشد.
زمانی که یک قید کلید اولیه ایجاد می کنید، یک unique clustered index روی ستون یا ستونها بطور اتوماتیک ایجاد می شود اگر یک clustered index قبلا ایجاد نشده باشد، بدلیل اینکه هر جدول تنها یک Clustered index می تواند داشته باشد. (هیچ اجباری به clustered index بودن primary key وجود ندارد، شما می توانید ستون یا ستونهای دیگری را به عنوان clustered index انتخاب کنید) زمانی که یک محدودیت unique key ایجاد می کنید به صورت پیش فرض یک اندیس منحصر بفرد برای اعمال integrity و سرعت بخشیدن به جستجو ایجاد می شود نوع این اندیس nonclustered است. بدلیل اینکه هر جدول تنها یک clustered index می تواند داشته باشد آن هم بطور پیشرفض به key primary اختصاص داده می شود.
نحوه ی تعریف unique key به شکل زیر می باشد:

Alter table emp add constraint id_unique unique (idno);‎

محدودیت DEFAULT

قید DEFAULT برای وارد کردن مقداری به صورت پیش فرض در یک ستون مورد بهره وری قرار می گیرد.
مقدار DEFAULT به تمام رکوردهای (سطر ها) جدید افزوده می شود، البته منوط به اینکه هیچ مقدار دیگری به جای آن وارد نشده باشد.
به عنوان مثال، بیشتر کارکنان موجود در جدول EMP، از Hyderabad هستند، شما می توانید این را به عنوان مقدار پیش فرض برای ستون CITY در نظر بگیرید. سپس، به هنگام وارد کردن رکوردها، اگر کاربر مقداری را وارد ستون CITY نکند، ستون یاد شده Hyderabad را به عنوان مقدار پیش فرض مورد استفاده قرار می دهد.
به منظور تعریف مقدار پیش فرض برای ستون ها، جدول را به ترتیب زیر ایجاد کنید.

create table emp (empno number(5),‎

        ‎                   name varchar2(20),‎

        ‎                   sal number(10,2),‎

        ‎                   city varchar2(20) default ‘Hyd’);‎

اکنون هنگامی که کاربر رکورد را همانند زیر وارد می کند.

insert into emp values (101,’Sami’,2000,’Bom’);‎

ستون CITY مقدار ‘Bom ‘را خواهد داشت. اما هنگامی که کاربر یک رکورد را به ترتیب زیر درج می کند.

Insert into emp (empno,name,sal) values (102,’Ashi’,4000);‎

ستون CITY مقدار ‘Hyd’ را خواهد داشت، زیرا که این مقدار، مقدار پیش فرض می باشد.

تعریف محدودیت در دستور CREATE TABLE.

create table emp (empno number(5) constraint emppk

        ‎                                  Primary key,‎

        ‎                   ename varchar2(20) constraint namenn

        ‎                                  not null,‎

       ‎                   sal  number(10,2) constraint salcheck

       ‎                        check (sal between 1000 and 20000)‎

        ‎                   idno varchar2(20) constraint id_unique

        ‎                             unique );‎

        create table attendance (empno number(5) constraint empfk

‎                             references emp (empno)‎

        ‎                             on delete cascade,‎

        ‎                   month varchar2(10),‎

        ‎                   days number(2) constraint dayscheck

        ‎                             check (days <= 31) );‎

اسم محدودیت ها صرفا اختیاری می باشد. اگر شما هیچ اسمی هم برای محدودیت تعریف نکنید، oracle این اسامی را به طور تصادفی ایجاد می کند، همانند این مثال

‎‘SYS_C1234’‎

در مثال زیر، نحوه ی دیگری از تعریف محدودیت در دستور CREATE TABLE را مشاهده می کنید.

create table emp (empno number(5),‎

        ‎                   ename varchar2(20) not null,‎

        ‎                   sal  number(10,2), ‎

        ‎                   idno varchar2(20),‎

        ‎                      constraint emppk Primary key (empno)‎

        ‎   constraint salcheck

        ‎                        check (sal between 1000 and 20000)‎

        ‎                     constraint id_unique unique (idno) );‎

        create table attendance (empno number(5),‎

        ‎                   month varchar2(10),‎

        ‎                   days number(2),‎

        constraint empfk foreign key (empno)     ‎

        ‎     references emp (empno)‎

        ‎       on delete cascade

        constraint dayscheck

        ‎                        check (days <= 31) );‎

چنانچه داده هایی که در حال کار با آن ها هستید دارای ویژگی های زیر می باشند، توصیه می گردد بررسی و اعتبار سنجی محدودیت های کلید های unique و foreign را به تعویق بیاندازید.
در صورتی که جدول مورد نظر تنها یک نسخه ی فوری یا snapshot از جدول اصلی (master table) باشد.
در صورتی که جدول یا جداول مد نظر دربردارنده ی حجم زیادی از داده ها است که توسط دیگر برنامه ها (application) در حال دستکاری شدن می باشد که ممکن است داده ها را به همان ترتیب بازنگرداند.
عملیات update کردن کلیدهای خارجی به صورت زنجیر وار (cascade)
به هنگام کار با انبوهی از داده های یکپارچه که توسط دیگر برنامه های خارجی دستکاری می شوند، می توان چک کردن محدودیت ها را برای بررسی اعتبار آن ها تا پایان اجرای تراکنش (transaction) به تعویق انداخت.
پس از اینکه جداول مناسب را شناسایی و انتخاب کردید، اطمینان کسب کنید که محدودیت های primary ، unique keys و foreign اعمال شده به این جداول همگی در پایان اجرا و ثبت مجموعه دستورات، ارزیابی یا اعتبار سنجی می شوند (deferrable constraint = محدودیت هایی که تنها پس از اجرای گروهی از دستورها اجرا می شوند و نه زمانی که جداول مجزا را درج یا بروز رسانی می کنید). این کار را می توان با ارائه ی دستور زیر انجام داد.

create table attendance (empno number(5),‎

        ‎                   month varchar2(10),‎

        ‎                   days number(2),‎

        constraint empfk foreign key (empno)     ‎

        ‎     references emp (empno)‎

        ‎       on delete cascade

        ‎          DEFERRABLE

        constraint dayscheck

        ‎                        check (days <= 31) );‎

حال از دستور زیر استفاده کنید.

Set constraint empfk deferred;‎

        Update attendance set empno=104 where empno=102;‎

        Insert into emp values (104,’Sami’,4000,’A123’);‎

        Commit;‎

همچنین می توان پیش از اجرای دستور commit (اجرا و ثبت)، بررسی کرد آیا خطای constraint violations رخ داده یا خیر. این کار را با استفاده از دستور SET CONSTRAINTS ALL IMMEDIATE پیش از اجرا کردن دستور COMMIT انجام می دهیم. در صورت وجود هر گونه مشکل در constraint ها، این دستور با شکست مواجه شده و نیز آن constraint ای که باعث ایجاد خطا گشته، شناسایی خواهد شد. چنانچه به هنگام نقض محدودیت ها (constraint violation) دستور commit را اجرا کنید ، تراکنش به حالت اولیه بازگشته (تغییرات اعمال شده در آن لغو گردیده) و شما با یک پیغام خطا مواجه خواهید شد.

فعال یا غیر فعال کردن constraint ها

این قابلیت وجود دارد که در صورت نیاز، constraint ها را در هر زمان فعال / غیر فعال ساخت. به منظور فعال/غیر فعال ساختن constraint ها، از ساختار نگارشی زیر استفاده می کنیم.

ALTER TABLE  ENABLE/DISABLE ‎

        ‎           CONSTRAINT  ‎

به عنوان مثال، برای غیر فعال کردن primary key constraintاز جدول emp، دستور زیر را نوشته و اجرا می کنیم.

Alter table emp drop constraint emppk;‎

برای اینکه دستور بالا با موفقیت اجرا شود، محدودیت foreign key باید از پیش حذف شده باشد. در غیر این صورت اول باید foreign key را حذف کرده، سپس primary key را حذف کنید. اگر می خواهید هر دو محدودیت های foreign key و primary key را در یک دستور حذف کنید.

Alter table emp drop constraint emppk cascade;‎

بازیابی اطلاعات درباره ی constraint ها

برای مشاهده ی اطلاعاتی درباره ی constraint ها، می توان از جداول کاتالوگ سیستم (data dictionary table) بدین صورت query بگیرید.

select * from user_constraints;‎



        select * from user_cons_columns;‎