What's the purpose of multi key for one table?

[latestgood]

Hello,



Like the title stated, can anyone explain a scenario where more than one key is needed for one table?

Thank you,

[rivereridanus]

In a scenario where you have two tables in a many to many relationship. For example, if you have a table called customer, and a table called item. You cannot store what item a customer ordered in the customer table because one customer can order more than one item, and you cannot store which customer ordered which item in the item table because one item can be ordered by more than one customer.
So the solution is to make a table in the middle called order, which has both the primary key from the customer table and the primary key from the item table, and can link the two together. This order table's primary key will be composite and will be made of both primary keys from the other tables.

[boblarson]

This can get into some fairly detailed and technical information, especially when you start talking about Data Modeling. But the very simple explanation (I'm not up for a more complex explanation) is that a table should have a key that identifies a unique row of data. So the term Primary Key is used to identify that as the key which is being used to identify a unique row. A foreign key is a primary key from another table which is stored in this other table to be able to relate the data from one table to another.

So there is only ONE key assigned for a table to identify the row. You can use a surrogate key (not a natural key) like an Autonumber to make it simpler and let the system manage the keys that way. I like to do that. A COMPOSITE key is a key that is made up of more than one field. However, the drawback to a composite key, is if you need to use it as a foreign key in another table, you must store ALL of the fields which make up that composite key in that other table whereas a surrogate key would only be one field. And that simplifies things greatly.

A composite key is sometimes used when you have a table where the key is not going to be used as a foreign key in another table. So then you can use that to avoid duplicate data and not add another key like the surrogate key.

When a composite key is desired but is going to be used as a foreign key, then you can use a surrogate key (Autonumber) and then place a multi-field index on the table (set to no duplicates) and it will then restrict the duplicates while letting you still have the surrogate key.

[latestgood]

Hi,

Thank you for your feedback. I heard that I need to make additional table when creating many to many relationship. Maybe it's my lack of knowledge, but I don't understand this concept. I've attached two tables where many to many relationship will be needed. Can somebody take a look at database and create many to many table? I learn better by seeing an example. Again, thank you