MySQL unique index

[centenial]

Hi,

 

I have a table

 

CREATE TABLE processes
(
process_id int primary key,
process_name tinytext,
process_type varchar(10),
gallery_id int
);

 

The `process_type`field has two possible values:

• Task1
  
• Task2
  

 

Only one instance of "Task2" can run on a gallery at a time. Task1 cannot run on a gallery that is currently being used by Task2. Multiple instances of Task1 can run on the same gallery. I want to create an index that will handle this logic for me, so that if I try to insert into the table, it will return false should there be a conflict.

 

Is it possible to do this with unique indexes?

[Mchl]

Not really if I understand you correctly...

 

Could you post examples of valid and invalid data?

[centenial]

Sure.

 

Valid example 1:

INSERT INTO processes (0, 'process1', 'Task1','10');
INSERT INTO processes (0, 'process2', 'Task1','10');
INSERT INTO processes (0, 'process3', 'Task1','10');

 

Valid example 2:

INSERT INTO processes (0, 'process1', 'Task1','10');
INSERT INTO processes (0, 'process2', 'Task2','20');

 

Invalid example 1:

// invalid because Task1 and Task2 cannot run on the same gallery.

INSERT INTO processes (0, 'process1', 'Task1','10');
INSERT INTO processes (0, 'process2', 'Task2','10');

 

Invalid example 2:

// invalid because only one instance of Task2 can run on the same gallery.

INSERT INTO processes (0, 'process1', 'Task2','10');
INSERT INTO processes (0, 'process2', 'Task2','10');

[Mchl]

What about

INSERT INTO processes (0, 'process1', 'Task1','10');
INSERT INTO processes (0, 'process2', 'Task1','10');

?

[centenial]

What about

INSERT INTO processes (0, 'process1', 'Task1','10');
INSERT INTO processes (0, 'process2', 'Task1','10');

?

This is valid, because multiple instances of Task1 can run on the same gallery. I've been banging my head on this for quite some time. Appreciate the help.

[Mchl]

UNIQUE index wont help you here then. You might try rigging something up with triggers.