امین جان،
این اسکریپت تیبل ام :
USE [PWKaraTrans]
GO
/****** Object: Table [dbo].[tblPersonnelInformation] Script Date: 02/26/2007 10:28:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblPersonnelInformation](
[EmployeeNo] [float] NOT NULL,
[Name] [nvarchar](50) NULL,
[Family] [nvarchar](50) NULL,
[CardNo1] [varchar](16) NULL,
[CardNo2] [varchar](16) NULL,
[CardNo3] [varchar](16) NULL,
[CardNo4] [varchar](16) NULL,
CONSTRAINT [PK_tblPersonnelInformation] PRIMARY KEY CLUSTERED
(
[EmployeeNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[tblPersonnelInformation] WITH CHECK ADD CONSTRAINT [CK_tblPersonnelInformation] CHECK (([dbo].[GetCardsConfilictCounts]()=(0)))
GO
ALTER TABLE [dbo].[tblPersonnelInformation] CHECK CONSTRAINT [CK_tblPersonnelInformation]
این هم اسکریپت فانکشن ام :
USE [PWKaraTrans]
GO
/****** Object: UserDefinedFunction [dbo].[GetCardsConfilictCounts] Script Date: 02/26/2007 10:16:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[GetCardsConfilictCounts]
(
)
RETURNS INT
AS
BEGIN
DECLARE @ConflictCount INT;
SET @ConflictCount = 0;
SELECT @ConflictCount=COUNT(*)
FROM (SELECT CardNo, COUNT(*) AS Cnt
FROM (SELECT EmployeeNo, Name, Family, CardNo1 AS CardNo, 1 as L
FROM dbo.tblPersonnelInformation
WHERE (CardNo1 IS NOT NULL) AND (LTRIM(RTRIM(CardNo1)) <> '')
UNION
SELECT EmployeeNo, Name, Family, CardNo2 AS CardNo, 2 as L
FROM dbo.tblPersonnelInformation AS tblPersonnelInformation_1
WHERE (CardNo2 IS NOT NULL) AND (LTRIM(RTRIM(CardNo2)) <> '')
UNION
SELECT EmployeeNo, Name, Family, CardNo3 AS CardNo, 3 as L
FROM dbo.tblPersonnelInformation AS tblPersonnelInformation_2
WHERE (CardNo3 IS NOT NULL) AND (LTRIM(RTRIM(CardNo3)) <> '')
UNION
SELECT EmployeeNo, Name, Family, CardNo4 AS CardNo, 4 as L
FROM dbo.tblPersonnelInformation AS tblPersonnelInformation_3
WHERE (CardNo4 IS NOT NULL) AND (LTRIM(RTRIM(CardNo4)) <> '')) AS t1
GROUP BY CardNo
HAVING (COUNT(*) >= 2)) AS t2;
RETURN @ConflictCount;
END
در زیر هم تصویری از آنچه که در اس کیو ال 2000 اتفاق می افتد.
تصویر 1 - ورژن اس کیو ال سرورم.
تصویر 2 - ثبت یک رکورد که کانسترینت ام را نقض کرده است، ولی ثبت شده است.(در کمال تعجب)
تصویر 3 - پاک کردن همان فیلد (و یا هر تغییر دیگر در تیبل) یک خطا Raise مس کند.