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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy