Q: One to Many and keys

  • Thread starter Thread starter Geoff Jones
  • Start date Start date
G

Geoff Jones

Hi

I've just started looking at Access and the Northwind sample database and
have a question which I hope somebody can answer?

In the Order Details table, the OrderID and ProductID fields have keys next
to them and also have "Indexed" set to "Yes (Duplicates OK)".

If I try to reproduce the table in another application e.g. by having
including OrderID and ProductID and giving them keys with "Indexed" set to
"Yes (Duplicates OK)" I get:

"Removing or changing the index for this field would require removal of the
primary key"

i.e. I can either have the keys or "Indexed" set to "Yes (Duplicates OK)"
but not both.

Can anybody help?

Thanks in advance

Geoff
 
You can hold down the control key and select more then one field when you
make a primary key.

Likely, both fields were selected BEFORE the primary key button was
selected.

In this case, then many records could have the same orderID, and many
records could have the same ProductId. However, if you make both fields a
single primary key, then only ONE record can have the same orderID, but all
products will have to be different.

If you try and change one of the fields...you can't, since BOTH fields make
up the primary key (you can even use more then 2 fields).
 
Albert D. Kallal said:
You can hold down the control key and select more then one field when you
make a primary key.

Likely, both fields were selected BEFORE the primary key button was
selected.

In this case, then many records could have the same orderID, and many
records could have the same ProductId. However, if you make both fields a
single primary key, then only ONE record can have the same orderID, but all
products will have to be different.

If you try and change one of the fields...you can't, since BOTH fields make
up the primary key (you can even use more then 2 fields).


--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.attcanada.net/~kallal.msn
Click to expand...

Albert

You're spot on! Many thanks.

Geoff
 
Hi

I've just started looking at Access and the Northwind sample database and
have a question which I hope somebody can answer?

In the Order Details table, the OrderID and ProductID fields have keys next
to them and also have "Indexed" set to "Yes (Duplicates OK)".

If I try to reproduce the table in another application e.g. by having
including OrderID and ProductID and giving them keys with "Indexed" set to
"Yes (Duplicates OK)" I get:

"Removing or changing the index for this field would require removal of the
primary key"

i.e. I can either have the keys or "Indexed" set to "Yes (Duplicates OK)"
but not both.
Click to expand...

A Primary Key (which may consist of one or of multiple fields) is
ALREADY indexed - a Primary Key is a special type of unique index. You
may not *need* this other index; if you do, you should add a new
index, not try to change the existing one!
 
Back
Top