tirsdag 3. mai 2016

MySQL - Composite Keys

I wanted to make a table Task that belonged to a Project.
The Task's had to be identified with projectId and taskId.
Every time a Task-record was inserted, then the taskId would be dependent
upon which project it belonged to.

ProjectId       TaskId
1                     1
1                     2
1                     3
2                     1
2                     2

I tried this Table definition

CREATE TABLE Task
(ProjectId INT NOT NULL,
TaskId INT NOT NULL AUTO_INCREMENT,
TaskCreated TIMESTAMP DEFAULT NOW(),
TaskName VARCHAR(30),
TaskDescription VARCHAR(1000),
ResponsibleId INT NOT NUll,
PRIMARY KEY (ProjectId, TaskId));

I saw that this would work in MySQL from version 5.6
But it didn't.

A trigger should do the job:

DELIMITER $$

CREATE TRIGGER IncrTaskId BEFORE INSERT ON Task
FOR EACH ROW BEGIN
    SET NEW.TaskId_no = (
       SELECT IFNULL(MAX(TaskId), 0) + 1
       FROM Task
       WHERE ProjectId  = NEW.ProjectId
    );
END $$

DELIMITER ;

And it did, actually!  
But, in my project I had to create the trigger dynamically.
And it is not possible to create a trigger from a stored procedure.
Irritating!
But I found a solution:

I made a stored procedure to return the Max of the TaskId with the specific ProjectId. In Java code I called this and incremented the value, before I inserted it in a new row:

if(task){
          
            stmt = con.prepareCall("{call sp_find_max_TaskId(?,?)}");
            stmt.setString(1, dbName);    
            stmt.setInt(2, 2);
            rs = stmt.executeQuery();
            while (rs.next()) {
                maxValue = rs.getInt(1);
            }

            stmt = con.prepareCall("{call sp_task_INSERT(?,?,?,?,?,?,?)}");
            stmt.setString(1, dbName);
            maxValue ++;
            stmt.setInt(2, maxValue);
            stmt.setInt(3, 2);
            stmt.setString(4, "Søke DNB NOR om midler");
            stmt.setString(5, "Innen 1.5.2016 må Siv sende søknad til DNB NOR ");
            stmt.setInt(6, 4);
          
            stmt.registerOutParameter(7, java.sql.Types.INTEGER);
          
            stmt.execute();

        }   
And that did the trick !