In SQL how can I have two fields that can't both be identical, only one is a primary key

Question

I'm useing MySQL and I have three tables, a table of tasks, a table of products and a table that describes the relation between the two: Each product is composed of several tasks, and each task may be found in multiple products.

The table that describes the relationship between the two has two primary keys, ProductID and TaskID that are both foreign keys as well. In this table I have a field called TaskOrder that for a given Product lists the order that tasks must be performed.

What I want is to say that for any product you can't have two tasks with the same TaskOrder, however I can't just set TaskOrder to unique because diffrent products will (and should) have duplicate values for TaskOrder

Is there some way to do this?

Answer 1

The junction table has one primary key, but it's a multi-field key with two fields (the two FK's for the other tables). A PK doesn't need to be only one field.

Answer 2

You want a UNIQUE constraint:

create table ProductTasks
 ( ProductID ...
 , TaskId ...
 , TaskOrder ...
 , primary key (ProductId, TaskId)
 , unique (ProductId, TaskOrder)
 );

Answer 3

Create a unique index on the combination of the two fields ProductID and TaskOrder by adding the following to the table creation command:

CONSTRAINT UNIQUE (ProductID, TaskOrder)