If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
2 Primary Keys
Just a semantic pont, but, by definition, you can't have 2 primary keys.
Are you asking about having a primary key that has more than one field in it? In that case, then yes, you can have the same product for multiple suppliers, but you can't repeat a product for the the same supplier. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Amir" wrote in message ... Hi! Can someone explain to me what is the purpose of using more the one primary key in a table? What will be the result of doing so? If I have a table of Products which has two primary keys: 1. ProductID 2. SupplierID If I set the ProductID to be indexed (allow duplicates) and the SupplierID to be indexed (duplicates are not allowed) does that mean I could have different products with the same ID, but not if they are from the same supplier? What is the advantage of using this method of double primary key as opposed to using validation rules? Regards. |
#2
|
|||
|
|||
2 Primary Keys
Hi!
Can someone explain to me what is the purpose of using more the one primary key in a table? What will be the result of doing so? If I have a table of Products which has two primary keys: 1. ProductID 2. SupplierID If I set the ProductID to be indexed (allow duplicates) and the SupplierID to be indexed (duplicates are not allowed) does that mean I could have different products with the same ID, but not if they are from the same supplier? What is the advantage of using this method of double primary key as opposed to using validation rules? Regards. |
#3
|
|||
|
|||
2 Primary Keys
Hi
following from Doug's post ... leave the index settings alone ... creating a primary key with two fields will do the type of "validation" that you want .. . however be careful if you set the primary key with the fields described you are in fact saying that supplier A can only EVER buy product A once ... maybe you need to include a date field in the primary key so that supplier A can only purchase product A on a single date, but can purchase product A again later on. Regards JulieD "Douglas J. Steele" wrote in message ... Just a semantic pont, but, by definition, you can't have 2 primary keys. Are you asking about having a primary key that has more than one field in it? In that case, then yes, you can have the same product for multiple suppliers, but you can't repeat a product for the the same supplier. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Amir" wrote in message ... Hi! Can someone explain to me what is the purpose of using more the one primary key in a table? What will be the result of doing so? If I have a table of Products which has two primary keys: 1. ProductID 2. SupplierID If I set the ProductID to be indexed (allow duplicates) and the SupplierID to be indexed (duplicates are not allowed) does that mean I could have different products with the same ID, but not if they are from the same supplier? What is the advantage of using this method of double primary key as opposed to using validation rules? Regards. |
#4
|
|||
|
|||
2 Primary Keys
Hi there!
In my DB there is a table with double field primary key: It's primary key is builded from the ReceiptNumber and the ReceiptSupplier Fields. I thought this is good because one supplier can't have more than one Receipt with the same number, but different suppliers can use the same receipt number. What should I put in the index? After all, I want to allow duplicates in the receipt numbers but I only want to allow it if it's for different suppliers. Amir. "JulieD" wrote in message ... Hi following from Doug's post ... leave the index settings alone ... creating a primary key with two fields will do the type of "validation" that you want .. . however be careful if you set the primary key with the fields described you are in fact saying that supplier A can only EVER buy product A once ... maybe you need to include a date field in the primary key so that supplier A can only purchase product A on a single date, but can purchase product A again later on. Regards JulieD "Douglas J. Steele" wrote in message ... Just a semantic pont, but, by definition, you can't have 2 primary keys. Are you asking about having a primary key that has more than one field in it? In that case, then yes, you can have the same product for multiple suppliers, but you can't repeat a product for the the same supplier. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Amir" wrote in message ... Hi! Can someone explain to me what is the purpose of using more the one primary key in a table? What will be the result of doing so? If I have a table of Products which has two primary keys: 1. ProductID 2. SupplierID If I set the ProductID to be indexed (allow duplicates) and the SupplierID to be indexed (duplicates are not allowed) does that mean I could have different products with the same ID, but not if they are from the same supplier? What is the advantage of using this method of double primary key as opposed to using validation rules? Regards. |
#5
|
|||
|
|||
2 Primary Keys
As Julie said, you put both fields in the primary key.
When you have the table open in Design mode, highlight both fields, then click on the "Key" icon to create the primary key. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Amir" wrote in message ... Hi there! In my DB there is a table with double field primary key: It's primary key is builded from the ReceiptNumber and the ReceiptSupplier Fields. I thought this is good because one supplier can't have more than one Receipt with the same number, but different suppliers can use the same receipt number. What should I put in the index? After all, I want to allow duplicates in the receipt numbers but I only want to allow it if it's for different suppliers. Amir. "JulieD" wrote in message ... Hi following from Doug's post ... leave the index settings alone ... creating a primary key with two fields will do the type of "validation" that you want .. . however be careful if you set the primary key with the fields described you are in fact saying that supplier A can only EVER buy product A once ... maybe you need to include a date field in the primary key so that supplier A can only purchase product A on a single date, but can purchase product A again later on. Regards JulieD "Douglas J. Steele" wrote in message ... Just a semantic pont, but, by definition, you can't have 2 primary keys. Are you asking about having a primary key that has more than one field in it? In that case, then yes, you can have the same product for multiple suppliers, but you can't repeat a product for the the same supplier. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Amir" wrote in message ... Hi! Can someone explain to me what is the purpose of using more the one primary key in a table? What will be the result of doing so? If I have a table of Products which has two primary keys: 1. ProductID 2. SupplierID If I set the ProductID to be indexed (allow duplicates) and the SupplierID to be indexed (duplicates are not allowed) does that mean I could have different products with the same ID, but not if they are from the same supplier? What is the advantage of using this method of double primary key as opposed to using validation rules? Regards. |
#6
|
|||
|
|||
2 Primary Keys
OK, Thank you very much!
"Douglas J. Steele" wrote in message ... As Julie said, you put both fields in the primary key. When you have the table open in Design mode, highlight both fields, then click on the "Key" icon to create the primary key. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Amir" wrote in message ... Hi there! In my DB there is a table with double field primary key: It's primary key is builded from the ReceiptNumber and the ReceiptSupplier Fields. I thought this is good because one supplier can't have more than one Receipt with the same number, but different suppliers can use the same receipt number. What should I put in the index? After all, I want to allow duplicates in the receipt numbers but I only want to allow it if it's for different suppliers. Amir. "JulieD" wrote in message ... Hi following from Doug's post ... leave the index settings alone ... creating a primary key with two fields will do the type of "validation" that you want .. . however be careful if you set the primary key with the fields described you are in fact saying that supplier A can only EVER buy product A once .... maybe you need to include a date field in the primary key so that supplier A can only purchase product A on a single date, but can purchase product A again later on. Regards JulieD "Douglas J. Steele" wrote in message ... Just a semantic pont, but, by definition, you can't have 2 primary keys. Are you asking about having a primary key that has more than one field in it? In that case, then yes, you can have the same product for multiple suppliers, but you can't repeat a product for the the same supplier. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Amir" wrote in message ... Hi! Can someone explain to me what is the purpose of using more the one primary key in a table? What will be the result of doing so? If I have a table of Products which has two primary keys: 1. ProductID 2. SupplierID If I set the ProductID to be indexed (allow duplicates) and the SupplierID to be indexed (duplicates are not allowed) does that mean I could have different products with the same ID, but not if they are from the same supplier? What is the advantage of using this method of double primary key as opposed to using validation rules? Regards. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Multiple Primary Keys | Mike | Database Design | 7 | July 19th, 2004 05:53 PM |
Using Primary Keys | Jodie | General Discussion | 1 | July 14th, 2004 08:49 PM |
Primary Keys | js | New Users | 5 | June 8th, 2004 11:36 PM |
Choosing Primary and Foreign Keys | A.V.H | New Users | 8 | May 23rd, 2004 09:12 PM |
Primary Keys and Relationships | TR | Database Design | 2 | May 23rd, 2004 03:19 AM |