Using sql function on CHECK constraint for newly inserted row

Question

First of all i need help with this for my bachelor thesis. I'm doing the whole database on sql server 2008 Release 2. The problem is with check constraint that is using a function that is working on her own but not with the use in the constraint. The result of the constraint should be something like this: An employee could go only on one bussines trip per day.

Table Bussines trips:

CREATE TABLE SluzebniCesta(
idSluzCesty int PRIMARY KEY NOT NULL,
DatumCesty DATE NOT NULL,
CasOdjezdu TIME(0) NOT NULL,
CasPrijezdu TIME(0),
CONSTRAINT Odjezd_prijezd CHECK(CasPrijezdu > DATEADD(hour,2,CasOdjezdu))
);

Table that contains the employs that goes on bussines trip:

CREATE TABLE ZamNaCeste(
idZamNaCeste int PRIMARY KEY NOT NULL,
SluzebCestaID int NOT NULL,
ZamestnanecID int NOT NULL,
FOREIGN KEY (ZamestnanecID) REFERENCES Zamestnanec(idZamestnance),
FOREIGN KEY (SluzebCestaID) REFERENCES SluzebniCesta(idSluzCesty)
);

Foreign key ZamestnanecID is an employee's id and SluzebCestaID is the bussines trip id.

Now the function :

CREATE FUNCTION myCheckZamNaCeste(@SluzebCestaID int, @ZamestnanecID int)
RETURNS int
AS
BEGIN
    DECLARE @retVal int;
    DECLARE @Zamestnanec int;
    DECLARE @SluzebniCesta int;
    SET @Zamestnanec = (SELECT idZamestnance FROM Zamestnanec WHERE idZamestnance=@ZamestnanecID);
    SET @SluzebniCesta = (SELECT idSluzCesty FROM SluzebniCesta WHERE idSluzCesty=@SluzebCestaID);
    IF EXISTS ( SELECT DatumCesty FROM SluzebniCesta
    WHERE idSluzCesty = @SluzebniCesta
    AND DatumCesty IN (SELECT DatumCesty FROM ZamNaCeste
    LEFT JOIN SluzebniCesta
    ON ZamNaCeste.SluzebCestaID = SluzebniCesta.idSluzCesty
    WHERE ZamestnanecID=@Zamestnanec))
    BEGIN
    SET @retVal=0;
    END
    ELSE
    BEGIN
    SET @retVal=1;
    END
    return @retVal
END
GO

And the alter table for the table that contains evidence of employee and their bussines trips:

ALTER TABLE ZamNaCeste
ADD CONSTRAINT check_cesty_zamestnance CHECK(dbo.myCheckZamNaCeste(SluzebCestaID,ZamestnanecID)=1);

And when I try to enter any new row the constraint is broken even if the function gives the right data. return 1 is the good result ....

score 0 · Accepted Answer · answered Jan 31 '15 at 23:57

In the first place, I'm not sure but it looks like the two set statements in the function are going out to retrieve from tables exactly the same values they already have from being passed in as parameters.

In the second place, I don't see anything limiting trips in the same day. Anywhere.

If you wanted to limit a trip by an employee to one per day, that is easy.

CREATE TABLE ZamNaCeste(
    idZamNaCeste int PRIMARY KEY NOT NULL,
    SluzebCestaID int NOT NULL,
    ZamestnanecID int NOT NULL,
    TripDate      date not null,
    FOREIGN KEY (ZamestnanecID) REFERENCES Zamestnanec(idZamestnance),
    FOREIGN KEY (SluzebCestaID) REFERENCES SluzebniCesta(idSluzCesty),
    constraint UQ_OneTripPerDay unique( ZamestnanecID, TripDate )
);

The unique constraint ensures the same employee cannot log more than one trip on the same day.

That's briliant :-) Well sometimes you just can't see the easy solution because you are trying to make it the hard way. Anyway thanks. — Caen, Feb 02 '15 at 10:37
Don't feel bad. I cannot begin to count the number of times over the years I've developed very complicated (and, if I may add, innovative) solutions -- only to delete everything but the one or two lines that actually solved the problem. Simplicity is a difficult challenge. However, I will forgo any chest-thumping until the testing is completed. :-P So keep us informed. — TommCatt, Feb 02 '15 at 19:38

Caen · Answer 2 · 2015-02-04T14:37:49.287

Well in the end i solved with a more sophisticated and better looking solution. The employ is limited with the times of arrival and departure. And i solved it with a function that returns number of incorrect occurences, if its zero than its all right and it works:


SELECT COUNT(*) FROM(SELECT * FROM SluzebniCesta JOIN ZamNaCeste
    ON SluzebniCesta.idSluzCesty = ZamNaCeste.SluzebCestaID) AS a
    JOIN (SELECT * FROM SluzebniCesta2 JOIN ZamNaCeste
    ON SluzebniCesta.idSluzCesty = ZamNaCeste.SluzebCestaID)AS b
    ON a.SluzebCestaID  b.SluzebCestaID
    AND a.CasOdjezdu  b.CasOdjezdu
    AND a.ZamestnanecID = b.ZamestnanecID
    AND (SELECT SluzebniCesta.DatumCesty FROM SluzebniCesta
    WHERE SluzebniCesta.idSluzCesty = a.SluzebCestaID) = (SELECT SluzebniCesta.DatumCesty
    FROM SluzebniCesta WHERE SluzebniCesta.idSluzCesty = b.SluzebCestaID)

Using sql function on CHECK constraint for newly inserted row

2 Answers2