How do I enforce uniqueness on a composite index?

[Dave]

How do I enforce uniqueness on a composite index?

For example, I have a table called orderitems. It has an orderitemid, a
orderid, and an itemid column. The orderitemid is the primary key. I want
to ensure that any combination of orderid and itemid is unqiue.

How can I do this?

 

[Douglas J. Steele]

Create an index on those two fields, and set it to Unique.

You can do this by opening the table in Design view, then choosing View |
Indexes from the menu bar.

Pick a name for your index, and put in in the left-most column (Index Name)
of the first empty row in the Indexes dialog. Move across to the middle
column (Field Name) and type select orderid from the combobox. Go down a row
and select itemid from the combobox. Don't worry about the right-most column
(Sort Order), and do not put an Index Name on the second row. Select the
first row again, and set Unique to Yes in the bottom of the dialog.

 

[DBS]

From Access Help:

1. Open the table in Design view.

2. Click Indexes on the toolbar.

3. In the first blank row in the Index Name column, type a name for the
index. You can name the index after one of the index fields, or use some
other appropriate name.

4. In the Field Name column, click the arrow and select the first field for
the index.

5. In the next row in the Field Name column, select the second field for the
index. (Leave the Index Name column blank in that row.) Repeat this step
until you have selected all the fields you want to include in this index. You
can use up to 10 fields.

6. In the "Index Properties" section underneath, select "Yes" for the
"Unique" option.

Hope that helps!

DBS (David Staas)

 

[John Vinson]

How do I enforce uniqueness on a composite index?

For example, I have a table called orderitems. It has an orderitemid, a
orderid, and an itemid column. The orderitemid is the primary key. I want
to ensure that any combination of orderid and itemid is unqiue.

How can I do this?

Two ways:

- Remove the OrderItemID and make OrderID and ItemID a joint,
two-field Primary Key. To do so open the table in design view and
ctrl-mouseclick the two fields, so they're both highlighted, and click
the Key icon.

- Or, click the Indexes icon (looks like lightning hitting a
datasheet). Type a name for the index in the left column (UniqueOrder
perhaps), and select the OrderID field on the same row, and the ItemID
on the next in the right column. Specify that the index is Unique
using the checkbox.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Dave]

Thanks guys for your help on this

Two ways:

- Remove the OrderItemID and make OrderID and ItemID a joint,
two-field Primary Key. To do so open the table in design view and
ctrl-mouseclick the two fields, so they're both highlighted, and click
the Key icon.

- Or, click the Indexes icon (looks like lightning hitting a
datasheet). Type a name for the index in the left column (UniqueOrder
perhaps), and select the OrderID field on the same row, and the ItemID
on the next in the right column. Specify that the index is Unique
using the checkbox.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps