CODEWITHSUNDEEP
sqlmysql
Benjamin Confino
Benjamin Confino

Reputation: 2374

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

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?

Upvotes: 1

Views: 236

Answers (3)

David Hanak
David Hanak

Reputation: 10984

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)

Upvotes: 2

Tony Andrews
Tony Andrews

Reputation: 132660

You want a UNIQUE constraint:

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

Upvotes: 5

dkretz
dkretz

Reputation: 37655

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.

Upvotes: 0

Related Questions