درک محدودیت های SQL

مقدمه

هنگام طراحی یک پایگاه داده، ممکن است مواقعی پیش بیاید که بخواهید محدودیت هایی برای داده های مجاز در ستون های خاص ایجاد کنید. برای مثال، اگر جدولی ایجاد می‌کنید که اطلاعات آسمان‌خراش‌ها را در خود نگه می‌دارد، ممکن است بخواهید ستونی که ارتفاع هر ساختمان را نگه می‌دارد، مقادیر منفی را ممنوع کند.

سیستم های مدیریت پایگاه داده رابطه ای (RDBMS) به شما این امکان را می دهد که کنترل کنید چه داده هایی با محدودیت ها به جدول اضافه می شوند. یک محدودیت یک قانون ویژه است که برای یک یا چند ستون – یا کل جدول – اعمال می شود و تغییراتی را که می توان در داده های جدول اعمال کرد، از طریق عبارت INSERT، UPDATE، یا DELETE محدود می کند.

این مقاله به طور مفصل بررسی می‌کند که محدودیت‌ها چیست و چگونه در RDBMS استفاده می‌شوند. همچنین هر یک از پنج محدودیت تعریف شده در استاندارد SQL را طی می کند و عملکردهای مربوطه را توضیح می دهد.

محدودیت ها چیست؟

در SQL، یک محدودیت، هر قاعده‌ای است که بر ستون یا جدول اعمال می‌شود که داده‌هایی را که می‌توان در آن وارد کرد، محدود می‌کند. هر زمان که سعی می کنید عملیاتی را انجام دهید که داده های ذخیره شده در جدول را تغییر می دهد – مانند عبارت INSERT، UPDATE یا DELETE – RDBMS آزمایش می کند که آیا آن داده محدودیت های موجود را نقض می کند یا خیر و اگر چنین است، یک خطا را برمی گرداند.

مدیران پایگاه داده اغلب برای اطمینان از اینکه پایگاه داده از مجموعه ای از قوانین تجاری تعریف شده پیروی می کند، بر محدودیت ها تکیه می کنند. در زمینه یک پایگاه داده، یک قانون تجاری هر خط مشی یا رویه‌ای است که یک کسب و کار یا سازمان دیگر از آن پیروی می‌کند و داده‌های آن نیز باید به آن پایبند باشند. به عنوان مثال، فرض کنید که در حال ساخت یک پایگاه داده هستید که فهرست موجودی فروشگاه مشتری را فهرست بندی می کند. اگر مشتری مشخص می کند که هر رکورد محصول باید یک شماره شناسایی منحصر به فرد داشته باشد، می توانید یک ستون با یک محدودیت UNIQUE ایجاد کنید که مطمئن شود هیچ دو ورودی در آن ستون یکسان نیستند.

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

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

کاربران زمانی که برای اولین بار جدولی را ایجاد می‌کنند، محدودیت‌ها را تعریف می‌کنند، یا می‌توانند بعداً با عبارت ALTER TABLE آن‌ها را اضافه کنند تا زمانی که با هیچ داده‌ای از قبل در جدول تضاد نداشته باشد. هنگامی که یک محدودیت ایجاد می کنید، سیستم پایگاه داده به طور خودکار یک نام برای آن ایجاد می کند، اما در اکثر پیاده سازی های SQL می توانید یک نام سفارشی برای هر محدودیت اضافه کنید. این نام ها برای اشاره به محدودیت ها در دستورات ALTER TABLE هنگام تغییر یا حذف آنها استفاده می شود.

استاندارد SQL به طور رسمی فقط پنج محدودیت را تعریف می کند:

• کلید اولیه
• کلید خارجی
• منحصر بفرد
• بررسی
• تهی نیست

اکنون که درک کلی از نحوه استفاده از محدودیت ها دارید، اجازه دهید نگاهی دقیق تر به هر یک از این پنج محدودیت بیندازیم.

کلید اولیه

محدودیت PRIMARY KEY مستلزم آن است که هر ورودی در ستون داده شده منحصر به فرد باشد و NULL نباشد، و به شما امکان می دهد از آن ستون برای شناسایی هر ردیف جداگانه در جدول استفاده کنید.

در مدل رابطه‌ای، یک کلید ستون یا مجموعه‌ای از ستون‌ها در یک جدول است که در آن هر مقدار تضمین می‌شود که منحصربه‌فرد بوده و حاوی هیچ مقدار NULL نباشد. کلید اصلی یک کلید ویژه است که مقادیر آن برای شناسایی ردیف های جداگانه در یک جدول استفاده می شود و ستون یا ستون هایی که کلید اصلی را تشکیل می دهند می توانند برای شناسایی جدول در بقیه پایگاه داده استفاده شوند.

این یکی از جنبه‌های مهم پایگاه‌داده‌های رابطه‌ای است: با یک کلید اولیه، کاربران نیازی ندارند که بدانند داده‌های آنها به صورت فیزیکی در یک ماشین ذخیره می‌شود و DBMS آنها می‌تواند هر رکورد را ردیابی کند و آنها را به‌صورت موقتی بازگرداند. به نوبه خود، این بدان معنی است که رکوردها هیچ ترتیب منطقی تعریف شده ای ندارند و کاربران می توانند داده های خود را به هر ترتیب یا از طریق هر فیلتری که می خواهند برگردانند.

شما می توانید یک کلید اولیه در SQL با محدودیت PRIMARY KEY ایجاد کنید که اساسا ترکیبی از محدودیت های UNIQUE و NOT NULL است. پس از تعریف کلید اصلی، DBMS به طور خودکار یک شاخص مرتبط با آن ایجاد می کند. ایندکس یک ساختار پایگاه داده است که به بازیابی سریعتر داده ها از یک جدول کمک می کند. مانند نمایه در کتاب درسی، پرس و جوها فقط باید ورودی های ستون نمایه شده را بررسی کنند تا مقادیر مرتبط را بیابند. این چیزی است که به کلید اصلی اجازه می دهد تا به عنوان یک شناسه برای هر ردیف در جدول عمل کند.

یک جدول فقط می تواند یک کلید اصلی داشته باشد، اما مانند کلیدهای معمولی، یک کلید اصلی می تواند بیش از یک ستون را شامل شود. با توجه به آنچه گفته شد، یک ویژگی تعیین کننده کلیدهای اولیه این است که آنها فقط از حداقل مجموعه ویژگی های مورد نیاز برای شناسایی منحصر به فرد هر ردیف در یک جدول استفاده می کنند. برای نشان دادن این ایده، جدولی را تصور کنید که اطلاعات مربوط به دانش آموزان یک مدرسه را با استفاده از سه ستون زیر ذخیره می کند:

• studentID: برای نگهداری شماره شناسایی منحصر به فرد هر دانش آموز استفاده می شود
• firstName: برای نگه داشتن نام کوچک هر دانش آموز استفاده می شود
• LastName: برای نگه داشتن نام خانوادگی هر دانش آموز استفاده می شود

این امکان وجود دارد که برخی از دانش‌آموزان مدرسه بتوانند نام کوچکی را به اشتراک بگذارند، که باعث می‌شود ستون firstName انتخاب ضعیفی از کلید اصلی باشد. همین امر برای ستون lastName نیز صادق است. یک کلید اولیه متشکل از هر دو ستون firstName و lastName می‌تواند کار کند، اما هنوز این احتمال وجود دارد که دو دانش‌آموز بتوانند نام و نام خانوادگی را به اشتراک بگذارند.

یک کلید اولیه متشکل از شناسه دانشجو و ستون‌های firstName یا lastName می‌تواند کار کند، اما از آنجایی که شماره شناسایی هر دانش‌آموز از قبل منحصربه‌فرد است، گنجاندن هر یک از ستون‌های نام در کلید اصلی اضافی است. بنابراین در این مورد حداقل مجموعه ای از ویژگی ها که می تواند هر سطر را شناسایی کند، و بنابراین انتخاب خوبی برای کلید اصلی جدول است، به تنهایی ستون studentID است.

اگر یک کلید از داده‌های کاربردی قابل مشاهده (یعنی داده‌هایی که موجودیت‌ها، رویدادها یا ویژگی‌های دنیای واقعی را نشان می‌دهند) تشکیل شده باشد، به آن کلید طبیعی می‌گویند. اگر کلید به صورت داخلی تولید شده باشد و چیزی خارج از پایگاه داده را نشان ندهد، به عنوان کلید جایگزین یا مصنوعی شناخته می شود. برخی از سیستم های پایگاه داده استفاده از کلیدهای طبیعی را توصیه نمی کنند، زیرا حتی نقاط داده به ظاهر ثابت می توانند به روش های غیرقابل پیش بینی تغییر کنند.

کلید خارجی

محدودیت FOREIGN KEY مستلزم این است که هر ورودی در ستون داده شده باید قبلاً در یک ستون خاص از جدول دیگری وجود داشته باشد.

اگر دو جدول دارید که می‌خواهید آن‌ها را با یکدیگر مرتبط کنید، یکی از راه‌هایی که می‌توانید این کار را انجام دهید، تعریف یک کلید خارجی با محدودیت FOREIGN KEY است. یک کلید خارجی ستونی در یک جدول (جدول “فرزند”) است که مقادیر آن از یک کلید در جدول دیگر (“والد”) می آید. این راهی برای بیان رابطه بین دو جدول است: محدودیت FOREIGN KEY مستلزم آن است که مقادیر ستونی که روی آن اعمال می شود باید در ستونی که به آن ارجاع می دهد وجود داشته باشد.

نمودار زیر چنین رابطه‌ای را بین دو جدول نشان می‌دهد: یکی برای ثبت اطلاعات کارکنان یک شرکت و دیگری برای ردیابی فروش شرکت استفاده می‌شود. در این مثال، کلید اصلی جدول EMPLOYEES با کلید خارجی جدول SALES ارجاع داده می شود:

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

اغلب اوقات، کلید خارجی جدول، کلید اصلی جدول مادر است، اما همیشه اینطور نیست. در اکثر RDBMS ها، هر ستونی در جدول والد که دارای یک محدودیت UNIQUE یا PRIMARY KEY باشد، می تواند توسط کلید خارجی جدول فرزند ارجاع داده شود.

منحصر بفرد

محدودیت UNIQUE هرگونه مقدار تکراری را از اضافه شدن به ستون داده شده منع می کند.

همانطور که از نام آن پیداست، یک محدودیت UNIQUE نیاز دارد که هر ورودی در ستون داده شده یک مقدار منحصر به فرد باشد. هر تلاشی برای اضافه کردن مقداری که قبلاً در ستون ظاهر می شود منجر به خطا می شود.

محدودیت های UNIQUE برای اعمال روابط یک به یک بین جداول مفید هستند. همانطور که قبلا ذکر شد، شما می توانید یک رابطه بین دو جدول با یک کلید خارجی برقرار کنید، اما چندین نوع رابطه وجود دارد که می تواند بین جداول وجود داشته باشد:

• یک به یک: به دو جدول گفته می شود که رابطه یک به یک دارند اگر سطرهای جدول والد مربوط به یک و تنها یک سطر در جدول فرزند باشد.
• یک به چند: در یک رابطه چند به هر، یک سطر در جدول والد می تواند به چند سطر در جدول فرزند مربوط باشد، اما هر سطر در جدول فرزند فقط می تواند به یک سطر در والد مربوط باشد.
• خیلی به چند: اگر ردیف‌های جدول والد می‌توانند با چند ردیف در جدول فرزند مرتبط باشند و بالعکس، گفته می‌شود که این دو رابطه چند به چند دارند.

با افزودن یک محدودیت UNIQUE به ستونی که بر روی آن محدودیت FOREIGN KEY اعمال شده است، می توانید اطمینان حاصل کنید که هر ورودی در جدول والد فقط یک بار در فرزند ظاهر می شود و در نتیجه یک رابطه یک به یک بین دو جدول برقرار می شود.

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

بررسی

یک محدودیت CHECK شرطی را برای یک ستون تعریف می کند که به عنوان گزاره شناخته می شود، که هر مقدار وارد شده در آن باید برآورده شود.

محمول‌های محدودیت CHECK به شکل عبارتی نوشته می‌شوند که می‌تواند TRUE، FALSE یا بالقوه ناشناخته ارزیابی شود. اگر سعی کنید مقداری را با یک محدودیت CHECK وارد کنید و این مقدار باعث شود که گزاره به درستی یا ناشناخته ارزیابی شود (که برای مقادیر NULL اتفاق می افتد)، عملیات موفق خواهد شد. با این حال، اگر عبارت به FALSE تبدیل شود، شکست می خورد.

محمول های CHECK اغلب به یک عملگر مقایسه ریاضی (مانند <، >، <=، OR >=) برای محدود کردن دامنه داده های مجاز در ستون داده شده متکی هستند. به عنوان مثال، یکی از استفاده‌های رایج برای محدودیت‌های CHECK، جلوگیری از نگه داشتن مقادیر منفی در برخی ستون‌ها در مواردی است که مقدار منفی معنی ندارد، مانند مثال زیر.

این دستور CREATE TABLE جدولی به نام productInfo با ستون هایی برای نام، شماره شناسایی و قیمت هر محصول ایجاد می کند. از آنجایی که منطقی نیست که یک محصول قیمت منفی داشته باشد، این عبارت یک محدودیت CHECK را بر ستون قیمت اعمال می کند تا اطمینان حاصل شود که فقط مقادیر مثبت دارد:

CREATE TABLE productInfo (
productID int,
name varchar(30),
price decimal(4,2)
CHECK (price > 0)
);

هر محمول CHECK نباید از عملگر مقایسه ریاضی استفاده کند. به طور معمول، می‌توانید از هر عملگر SQL استفاده کنید که می‌تواند درست، نادرست یا ناشناخته را در یک محمول بررسی ارزیابی کند، از جمله LIKE، BETWEEN، IS NOT NULL و غیره. برخی از پیاده‌سازی‌های SQL، اما نه همه، حتی به شما اجازه می‌دهند که یک زیرپرس و جو را در یک محمول CHECK قرار دهید. با این حال، توجه داشته باشید که اکثر پیاده سازی ها به شما اجازه نمی دهند به جدول دیگری در یک گزاره ارجاع دهید.

تهی نیست

محدودیت NOT NULL هرگونه مقدار NULL را از اضافه شدن به ستون داده شده منع می کند.

در اکثر پیاده سازی های SQL، اگر ردیفی از داده ها را اضافه کنید اما مقداری را برای ستون خاصی مشخص نکنید، سیستم پایگاه داده به طور پیش فرض داده های از دست رفته را به صورت NULL نشان می دهد. در SQL، NULL یک کلمه کلیدی ویژه است که برای نشان دادن یک مقدار ناشناخته، گم شده یا نامشخص استفاده می شود. با این حال، NULL خود یک مقدار نیست، بلکه وضعیت یک مقدار ناشناخته است.

برای نشان دادن این تفاوت، جدولی را تصور کنید که برای ردیابی مشتریان در آژانس استعدادیابی استفاده می شود که دارای ستون هایی برای نام و نام خانوادگی هر مشتری است. اگر یک کلاینت از یک تک نام استفاده می کند – مانند “Cher”، “Usher” یا “Beyoncé” – مدیر پایگاه داده ممکن است تنها یک نام را در ستون نام وارد کند، که باعث می شود DBMS در ستون نام خانوادگی NULL را وارد کند. پایگاه داده نام خانوادگی مشتری را به معنای واقعی کلمه “تهی” در نظر نمی گیرد. فقط به این معنی است که مقدار ستون نام خانوادگی آن ردیف ناشناخته است یا این فیلد برای آن رکورد خاص اعمال نمی شود.

همانطور که از نام آن پیداست، محدودیت NOT NULL از NULL بودن هر مقدار در ستون داده شده جلوگیری می کند. این بدان معناست که برای هر ستونی با محدودیت NOT NULL، هنگام درج یک ردیف جدید باید مقداری برای آن مشخص کنید. در غیر این صورت، عملیات INSERT ناموفق خواهد بود.

نتیجه

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