مبانی اس کیو ال - قیدها(Constraints)

[Galawij]

سلام،
در ادامه مطالب قبلی ارائه شده در راستای آموزش مبانی SQL، در این تاپیک به آشنایی با قیود و نحوه استفاده از آنها پرداخته می شود.

Constraints:

قیدها عمومی ترین، ساده ترین و مناسب ترین (از نظر کارایی) روش برای اعمال قوانین جامعیت بانک اطلاعاتی می باشند و توصیه می شود تا جایی که امکان دارد برای اعمال قوانین جامعیت از این روش استفاده شود (استفاده از قید باعث بهبود کارائی سیستم در انجام پرس و جوها و مدیریت بانک اطلاعاتی می شود).
هم اکنون پنج نوع Constraint در SQL وجود دارد:


NOT Null Constraint
Default Constraint
Check Constraint
Primary Key Constraint
Foreign Key Constraint

در ادامه به بررسی این انواع پرداخته می شود:

NOT Null Constraint: برای تعیین اینکه یک فیلد از جدول می تواند مقادیر NULL بگیرد یا نه استفاده می شود.
Default Constraint: با تعیین این مورد در هنگام ورود اطلاعات به جداول، مقادیر پیش فرضی برای فیلدها در نظر گرفته خواهد شد.
Check Constraint: عبارتهای منطقی هستند که در ورود اطلاعات به ازای مقادیر وارد شده کنترل می شوند و در صورت نتیجه درست از عبارت (عبارت های) منطقی دریافت اطلاعات انجام می شود (این نوع قیود توسط مدیر بانک اطلاعاتی و با توجه به تحلیل نرم افزار با دستورات T-SQL ایجاد می شود).
Primary Key Constraint: با تعیین این مورد، ورود مقادیر به فیلدهای جداول، از نظر منحصر به فرد بودن مقادیر کنترل می شود. با تعریف کلید اصلی جداول، بانک اطلاعاتی را در ساختن ایندکس اطلاعات و مدیریت اندیکس راهنمائی می کنیم و سرعت دسترسی به داده ها را افزایش می دهیم.
Foreign Key Constraint: با تعیین یک فیلد به عنوان کلید خارجی مشخص خواهد شد که بانک اطلاعاتی مقادیر ورودی به این فیلد را با مقادیر موجود فیلد کلید اصلی متناظر آن باید مقایسه نموده و از ورود مقادیر نامعتبر جلوگیری نماید.

برکت باشد، برکت باشیم ...

بازگشت به فهرست مطالب (http://barnamenevis.org/showthread.php?327265-%D9%85%D8%A8%D8%A7%D9%86%DB%8C-%D8%A7%D8%B3-%DA%A9%DB%8C%D9%88-%D8%A7%D9%84)

[Galawij]

Check Constraint:
این کنترلها از ورود داده های نامعتبر به بانک اطلاعاتی جلوگیری می کنند ولی در آنها امکان تغییر اطلاعات ورودی به بانک وجود ندارد. در حقیقت اگر داده ها با شرایط مشخص شده مطابقت داشته باشد وارد بانک می شود و در صورتی که مطابقت نداشته باشد با ایجاد یک خطای ورود اطلاعات از ورود آنها جلوگیری خواهد شد. به عنوان مثال می توان از طریق ایجاد یک محدودیت، محدوده داده های وارد شده برای فیلد نمره را بین صفر تا بیست در نظر گرفت.
عبارتهای منطقی که برای ایجاد محدودیت Check استفاده می شوند، همان عبارتهای منطقی استانداری می باشند که برای مثال در شرط Where دستور Select نیز به کار برده می شوند. برای مثال: Degree>=0 AND Degree<=20
برای این قیدهای کنترلی دو نوع مجزا وجود دارد:

قیدهای کنترلی سطح فیلد
قیدهای کنترلی سطح جدول

در کنترل های سطح فیلد، عبارتهای منطقی صرفاً به یک فیلد ارجاع داده می شود و عبارت منطقی تشکیل شده به همان فیلد منتسب می شود ولی در کنترل های سطح جدول می توان از همه فیلدهای آن جدول استفاده کرد و عبارت تشکیل شده به جدول منتسب می شود (می توان قید را روی چندین فیلد جدول اعمال کرد).
تعریف Check Constraints ها:
1) ساده ترین راه برای ایجاد این نوع از قیدها استفاده از ابزارهای خود Enterprise Manager هست. برای این کار در قسمت Design یا Modify جدول مورد نظر بر روی ابزار Manage Check Constraint کلیک می کنیم و در قسمت Expression عبارت منطقی خود را وارد می کنیم.
83239
2) روش دیگر اضافه کردن این بخش در دستور Create Table هست، این Check در این دستور می تواند در سطح فیلد و یا در سطح جدول آورده شود.
مثال در سطح فیلد:
CREATE TABLE [dbo].[TableForTest](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nchar](10) COLLATE Arabic_CI_AI NOT NULL,
[Minvalue] [smallint] NOT NULL CHECK (([Minvalue]>=(0))),
[Maxvalue] [bigint] NOT NULL CHECK (([Maxvalue]<=(1000))),
CONSTRAINT [PK_TableForTest] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
مثال در سطح جدول:
CREATE TABLE [dbo].[TableForTest](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nchar](10) COLLATE Arabic_CI_AI NOT NULL,
[Minvalue] [smallint] NOT NULL,
[Maxvalue] [bigint] NOT NULL,
PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
CONSTRAINT [CK_TableForTest] CHECK (([Minvalue]<=[Maxvalue] AND [Maxvalue]>=[Minvalue]))
)
تعریف قیدها در سطح جدول این مزیت را نیز دارند که می توان مستقیماً نام مورد نظر خود را بر روی قید اعمال کنیم.
3) اعمال تغییرات بر روی قیدهای جداول موجود:برای اضافه کردن قید بر روی جداولی که از قبل موجود هستند و یا حذف کردن، فعال یا غیر فعال کردن یا اصلاح کردن قیدها از دستور Alter Table استفاده می کنیم. امکان اصلاح Check ها صرفاً از طریق حذف آن و افزودن Check جدید انجام پذیر هست.
مثال افزودن قید کنترلی جدید به جدول موجود:
ALTER TABLE [dbo].[TableForTest] WITH CHECK ADD CONSTRAINT [CK_TableForTest] CHECK (([Minvalue]<=[Maxvalue] AND [Maxvalue]>=[Minvalue]))
غیر فعال کردن Check تعریف شده موجود:
ALTER TABLE [dbo].[TableForTest] NOCHECK CONSTRAINT [CK_TableForTest]
فعال کردن Check تعریف شده موجود:
ALTER TABLE [dbo].[TableForTest] CHECK CONSTRAINT [CK_TableForTest]
حذف Check تعریف شده موجود از یک جدول:
ALTER TABLE [dbo].[TableForTest] DROP CONSTRAINT [CK_TableForTest]
نکته: با تعریف یک Check جدید محدودیت تعریف شده هم برای اطلاعات موجود و هم برای اطلاعات جدیدی که به جدول وارد می شوند، اعمال خواهد شد. اگر اطلاعات موجود در جدول در مواردی که در Check تعریف شده، صدق ننمایند از افزوده شدن Check به جدول جلوگیری می شود، این در مورد ورود اطلاعات جدید به جدول از هر طریقی نیز صدق می کند.

ادامه دارد ...

[tiphooo]

آیا پیغامهای خطای برگردانده شده در محیطهای برنامه نویسی قابل کنترل است؟ چون پیغامهای انگلیسی به ذائقه کاربران معمولی (مشتریان) خوش نمی آید.
مثلا اگر در مثالی که گفتید من برای نمره عدد 21 را وارد کنم چه اتفاقی می افتد؟

[Galawij]

مثلا اگر در مثالی که گفتید من برای نمره عدد 21 را وارد کنم چه اتفاقی می افتد؟
سوال شما برمی گرده به Error Handling در SQL، با یک مثال توضیح می دم:
دستورات (DML (Insert,Select,Update مربوط به جداولتون را در SP ها در داخل TRY - Catch قرار می دهید که در قسمت Catch می تونید روی پیغام خطاها (عمدی یا سهوی یا ساختاری) کنترل داشته باشید. به ساختار این SP دقت کنید:
ALTER PROCEDURE [dbo].[SPR_LabsProfile](@IdLab SMALLINT=0,@ErrorMessagePersian NVARCHAR(500) OUTPUT,@ErrorNumber INT OUTPUT)
AS
BEGIN
BEGIN TRY
SET @ErrorNumber=0;
SELECT LabName, Manager, LabPhone, LabAddress, LabFax, WebSite
FROM dbo.TbLabs
WHERE ((@IdLab=0)OR(dbo.TbLabs.IdLab=@IdLab))
END TRY
BEGIN Catch
SET @ErrorNumber=ERROR_NUMBER()
EXECUTE SPHandlerError
@ErrorNumber
,@ErrorMessagePersian OUTPUT
END Catch
END
در قسمت Catch مربوط به دستورات، شماره خطای برگشتی در صورت عدم اجرای دستورات DML در داخل متغیر ErrorNumber@ قرار می گیرد و بعد از طریق فراخوانی یک روال به نام SPHandlerError شماره خطا پاس داده می شود و معادل آن شماره پیغام مناسب (فارسی) به سمت برنامه برگردانده می شود.
شما می تونید از قبل شماره خطا ها و توضیحات متناسب با آنها را در یک جدول جداگانه نگهداری کنید و در داخل این SP عملیات واکشی را انجام بدید.
توضیح: لیست تمام خطاهای SQL و توضیحات لاتین مربوط به آنها را می تونید از طریق دستور select * from sys.messages به دست بیارید(خطای شماره 547 مربوط به قیدها است).
ساختار SP هم که واضحه:
ALTER PROCEDURE [dbo].[SPHandlerError](@IdErrorNumber INT,@ErrorMessagePersian NVARCHAR(500) OUTPUT)
AS
BEGIN
SET NOCOUNT ON;
SELECT @ErrorMessagePersian=TableErrorMessagePersian FROM TbTableErrorNumbers WHERE Id_ErrorNumber=@IdErrorNumber
END
می تونید کارهای جالب تری هم انجام بدید، مثلاً برای هر جدولی یک سری پیغام خطاهای خاص داشته باشید و ...
برکت باشد.

[مهدی هادیان2]

سوال شما برمی گرده به Error Handling در SQL، با یک مثال توضیح می دم:
دستورات (DML (Insert,Select,Update مربوط به جداولتون را در SP ها در داخل TRY - Catch قرار می دهید که در قسمت Catch می تونید روی پیغام خطاها (عمدی یا سهوی یا ساختاری) کنترل داشته باشید. به ساختار این SP دقت کنید:
ALTER PROCEDURE [dbo].[SPR_LabsProfile](@IdLab SMALLINT=0,@ErrorMessagePersian NVARCHAR(500) OUTPUT,@ErrorNumber INT OUTPUT)
AS
BEGIN
BEGIN TRY
SET @ErrorNumber=0;
SELECT LabName, Manager, LabPhone, LabAddress, LabFax, WebSite
FROM dbo.TbLabs
WHERE ((@IdLab=0)OR(dbo.TbLabs.IdLab=@IdLab))
END TRY
BEGIN Catch
SET @ErrorNumber=ERROR_NUMBER()
EXECUTE SPHandlerError
@ErrorNumber
,@ErrorMessagePersian OUTPUT
END Catch
END
در قسمت Catch مربوط به دستورات، شماره خطای برگشتی در صورت عدم اجرای دستورات DML در داخل متغیر ErrorNumber@ قرار می گیرد و بعد از طریق فراخوانی یک روال به نام SPHandlerError شماره خطا پاس داده می شود و معادل آن شماره پیغام مناسب (فارسی) به سمت برنامه برگردانده می شود.
شما می تونید از قبل شماره خطا ها و توضیحات متناسب با آنها را در یک جدول جداگانه نگهداری کنید و در داخل این SP عملیات واکشی را انجام بدید.
توضیح: لیست تمام خطاهای SQL و توضیحات لاتین مربوط به آنها را می تونید از طریق دستور select * from sys.messages به دست بیارید(خطای شماره 547 مربوط به قیدها است).
ساختار SP هم که واضحه:
ALTER PROCEDURE [dbo].[SPHandlerError](@IdErrorNumber INT,@ErrorMessagePersian NVARCHAR(500) OUTPUT)
AS
BEGIN
SET NOCOUNT ON;
SELECT @ErrorMessagePersian=TableErrorMessagePersian FROM TbTableErrorNumbers WHERE Id_ErrorNumber=@IdErrorNumber
END
می تونید کارهای جالب تری هم انجام بدید، مثلاً برای هر جدولی یک سری پیغام خطاهای خاص داشته باشید و ...
برکت باشد.
با سلام
به طور کلی این تیپ کارها رو بهتره در سطح فرم و برنامه نویسی انجام بدیم یا در سطح پایگاه داده؟
با تشکر

[banou1980]

سلام.
ببخشید من یه فیلد شماره شناسایی دارم که یکتا هست ولی کلید اصلی نیست.چطوری میتونم unique index روی این فیلد تعریف کنم. یعنی نمی دونم چه قیدی روش بزارم.لطفا راهنمایی کنید.

[مهدی نان شکری]

با سلام
Unique constraint می تواند مشکلتان را برطرف نماید.
با تشکر