February 26, 2013 at 10:06 am
HI All,
Just wanted to know whether can we create a constraint to restrict the overlapping records in the following table.
Here is the table script..
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TranBreakUp](
[TranID] [int] IDENTITY(1,1) NOT NULL,
[ProductCode] [nchar](4) NOT NULL,
[AccountCode] [nchar](4) NOT NULL,
[FromAmt] [numeric](17, 2) NOT NULL,
[ToAmt] [numeric](17, 2) NOT NULL,
[FromYear] [tinyint] NOT NULL,
[ToYear] [tinyint] NOT NULL,
[USRID] [varchar](10) NOT NULL,
[SystemDt] [datetime] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TranBreakUp]
ADD CONSTRAINT PK_TranBreakUp_TranID PRIMARY KEY (TranID);
GO
ALTER TABLE [dbo].[TranBreakUp]
ADD CONSTRAINT UK_TranBreakUp UNIQUE (ProductCode, AccountCode, FromAmt, ToAmt, FromYear, ToYear );
GO
ALTER TABLE [dbo].[TranBreakUp]
ADD CONSTRAINT CK_TranBreakUp_FromAmt CHECK (FromAmt >= 0);
GO
ALTER TABLE [dbo].[TranBreakUp]
ADD CONSTRAINT CK_TranBreakUp_ToAmt CHECK (ToAmt > FromAmt);
GO
ALTER TABLE [dbo].[TranBreakUp]
ADD CONSTRAINT CK_TranBreakUp_FromYear CHECK (FromYear >= 0);
GO
ALTER TABLE [dbo].[TranBreakUp]
ADD CONSTRAINT CK_TranBreakUp_ToYear CHECK (ToYear >= FromYear);
GO
INSERT INTO [dbo].[TranBreakUp]
([ProductCode]
,[AccountCode]
,[FromAmt]
,[ToAmt]
,[FromYear]
,[ToYear]
,[USRID]
,[SystemDt])
VALUES
('P001'
,'A002'
,0
,10000
,0
,1
,'986532'
,GETDATE())
,
VALUES
('P001'
,'A002'
,10001
,100000
,2
,3
,'986532'
,GETDATE())
,
VALUES
('P001'
,'A002'
,100001
,1000000
,3
,4
,'986532'
,GETDATE())
The above data is ideal
I have created check constraint on columns "FromAmt" to restrict amount values greater than equal to 0..
Also created check constraint on column "ToAmt" to restrict To Amount is always greater than From Amount. (ToAmt > FromAmt)
But, i just want to know whether is there any constraint that could be added to restrict last 2 data rows in the following script.
INSERT INTO [dbo].[TranBreakUp]
([ProductCode]
,[AccountCode]
,[FromAmt]
,[ToAmt]
,[FromYear]
,[ToYear]
,[USRID]
,[SystemDt])
VALUES
('P001'
,'A002'
,0
,10000
,0
,1
,'986532'
,GETDATE())
,
VALUES
('P001'
,'A002'
,1000
,10000
,0
,3
,'986532'
,GETDATE())
,
VALUES
('P001'
,'A002'
,5000
,10000
,2
,3
,'986532'
,GETDATE())
February 26, 2013 at 12:15 pm
Hi
If I've understood your question right, you could create a check function for your year ranges and use that in a constraint. Something like:
CREATE FUNCTION chkOverLap(@fromYear tinyint, @toYear tinyint) RETURNS BIT AS
BEGIN
IF EXISTS(SELECT 1 FROM TranBreakUp t WHERE @FromYear BETWEEN t.FromYear AND t.ToYear - 1 OR @ToYear BETWEEN t.FromYear + 1 AND t.ToYear )
RETURN 1
RETURN 0
END;
GO
ALTER TABLE [dbo].TranBreakUp
ADD CONSTRAINT CK_TranBreakUp_OverlapYear CHECK (dbo.chkOverLap(ToYear, FromYear) = 0);
GO
February 27, 2013 at 3:30 am
HI MickyT,
Thanks for the reply, the solution gave is partially correct. I want the slab to be checked is From Amount, To Amount , From Year & To year for a particular ProductCode - AccountCode combination.
But, your solution gives me a hint...
February 27, 2013 at 10:36 pm
HI all,
After creating a check constraint using the function , no record is getting inserted...
Please help....
February 27, 2013 at 11:11 pm
Can you post the function you created?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply