January 29, 2016 at 6:36 am
I have a table aa(id int, sdate date, edate date, constraint chk check(sdate<= enddate).
For a particular id I have to check for overlapping dates. That is I do not want any one to insert data of a perticular id which has overlapping dates. So i need to check the below conditions -
if @id = id and (@sdate >= edate or @edate <= sdate) then allow insert
if @id = id and (@sdate < edate or @edate > sdate) then do not allow insert
if @id <> id then allow inserts
I have encapsulated the above logic in a function and used that function in check constraint. Function is working fine but check constraint is not allowing me to enter any records. I do not know why - my function and constraint are mentioned below :
alter function fn_aa(@id int,@sdate date,@edate date)
returns int
as
begin
declare @i int
if exists (select * from aa where id = @id and (@sdate >= edate or @edate <= sdate)) or not exists(select * from aa where id = @id)
begin
set @i = 1
end
if exists(select * from aa where id = @id and (@sdate < edate or @edate < sdate))
begin
set @i = 0
end
return @i
end
go
alter table aa
add constraint aa_ck check(dbo.fn_aa(id,sdate,edate) = 1)
Now when I try to insert any value in the table aa I get the following error -
"Msg 547, Level 16, State 0, Line 1 The INSERT statement conflicted with the CHECK constraint "aa_ck". The conflict occurred in database "tempdb", table "dbo.aa". The statement has been terminated."
Function is returning value 1 but constraint is not allowing to insert any data. Can some one help me here. I am trying for last 2 hours but cannot understand what am i doing wrong?
January 29, 2016 at 10:13 am
You don't have any default value. I'm guessing that you're trying to insert rows with new ids which would return a null value which is not equal to 1 and failing the constraint validation.
Is your id a real row identifier? or just a foreign key and can have duplicates?
Also, the constraint is checked after the insertion, which means that it takes the inserted row in consideration for your function's queries, returning always a 0. Unless you have something to differentiate the inserted row from the preexisting rows.
An alternative could be a trigger, but I'm not sure if any of this would be a good idea.
CREATE table aa(id int, sdate date, edate date);
GO
CREATE TRIGGER T_aa ON aa
INSTEAD OF INSERT, UPDATE
AS
BEGIN
IF EXISTS (
SELECT 1
FROM aa a
JOIN inserted i ON a.id = i.id
AND i.sdate < a.edate
AND i.edate > a.sdate)
BEGIN
ROLLBACK;
RAISERROR('Invalid date range',1,1);
END;
ELSE
INSERT INTO aa
SELECT * FROM inserted;
END
GO
INSERT INTO aa VALUES(1, '20150101', '20150201');
INSERT INTO aa VALUES(1, '20150102', '20150301');
GO
SELECT * FROM aa;
GO
DROP TABLE aa;
January 29, 2016 at 12:18 pm
SQL Server will execute the function to verify your check constraint *after* inserting the row.
Assuming that your row has sdate < edate, that row itsself will trigger the condition for setting the return value to 0.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply