Skip to content
Advertisement

Passing parameters for a constraint function

I have the following table that joins driver and truck tables in order to assign trucks to drivers. I need to constraint entries that belong to the same driver on the same day, and DO NOT include the same truck code.

CREATE TABLE Assignments(
    ID_CxC CHAR(3) NOT NULL PRIMARY KEY,
    truck_code char(3) NOT NULL REFERENCES Trucks(truck_code),
    driver_code char(5) NOT NULL REFERENCES Drivers(driver_code),
    [date] DATE NOT NULL
);
INSERT Assignments
VALUES(1,1,1,'06-11-2021');
INSERT Assignments
VALUES(2,2,2,'06-11-2021');
INSERT Assignments
VALUES(3,3,3,'06-11-2021');
INSERT Assignments
VALUES(4,4,4,'06-11-2021');
INSERT Assignments
VALUES(5,5,5,'06-11-2021');
INSERT Assignments
VALUES(6,6,6,'06-11-2021');
INSERT Assignments
VALUES(7,1,1,'06-11-2021');
INSERT Assignments
VALUES(8,2,2,'06-11-2021');
INSERT Assignments
VALUES(9,3,3,'06-11-2021');
INSERT Assignments
VALUES(10,4,4,'06-11-2021');

It was suggested to create a boolean function to pass along dateval, truckval and driverval, and returns the following.

Not Exists (Select id_CxC From Assignments
            Where Date = Dateval and Driver=Driverval
              And Truck<>Truckval)

I have tried creating a function returning a bit datatype, but I honestly lack knowledge on user created functions, how would I go about passing the parameters for the function?

Is there an easier approach to the whole situation?

Advertisement

Answer

You clearly have two different entities — but they are related. You have something like an “assignment” where a driver has a truck for one or more days. Then you have something like a “trip” where a driver uses the truck (what you are currently calling an “assignment”).

So, I would model this as:

CREATE TABLE assignments (
    ID_CxC CHAR(3) PRIMARY KEY,
    truck_code char(3) NOT NULL REFERENCES Trucks(truck_code),
    driver_code char(5) NOT NULL REFERENCES Drivers(driver_code),
    date DATE NOT NULL,
    UNIQUE (driver_code, date)
);

Note that there is only one row per driver and per date. Voila! Only one truck.

CREATE TABLE trips (
    ID_trip INT IDENTITY(1, 1) PRIMARY KEY,
    AssignmentId CHAR(3) NOT NULL REFERENCES Assignments(ID_CxC)
);

You can then add as many trips for a driver that you like, but for a given driver, they all have the same truck.

3 People found this is helpful
Advertisement