|
| 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
January 7th, 2007, 07:04 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
Primary Keys
I have a table with two primary keys (its a linking table). I'd like both the
fields to be able to accept null values. Is this possible, and if so, how do I do that? 
|
|
#2
January 7th, 2007, 07:11 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Primary Keys
Nanette wrote:
I have a table with two primary keys (its a linking table). I'd like both the fields to be able to accept null values. Is this possible, and if so, how do I do that? Nope. Fields that are part of the PK cannot be Null. You could use a surrogate PK (like an AutoNumber) and create a Unique Index over the two fields you are using now for the PK. Fields in a Unique Index can be Null. -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com
|
|
#3
January 7th, 2007, 07:14 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Primary Keys
Primary Keys cannot contain a Null value because they must always contain
unique values. But, perhaps you want to allow Foreign Key fields to have a Null value (foreign key fields are fields in a child / related table that link that table back to a parent or lookup table)? If that is what you seek, be sure that the Required property for the field in the table is set to No. -- Ken Snell MS ACCESS MVP "Nanette" wrote in message ... I have a table with two primary keys (its a linking table). I'd like both the fields to be able to accept null values. Is this possible, and if so, how do I do that?
|
|
#4
January 7th, 2007, 08:21 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Primary Keys
Hi Ken,
They are actually Foreign Key fields, I mis-spoke earlier. How do I set two fields in one table to be Foreign Keys? I thought I'd used the primary key function in access, but that doesn't work. "Ken Snell (MVP)" wrote: Primary Keys cannot contain a Null value because they must always contain unique values. But, perhaps you want to allow Foreign Key fields to have a Null value (foreign key fields are fields in a child / related table that link that table back to a parent or lookup table)? If that is what you seek, be sure that the Required property for the field in the table is set to No. -- Ken Snell MS ACCESS MVP "Nanette" wrote in message ... I have a table with two primary keys (its a linking table). I'd like both the fields to be able to accept null values. Is this possible, and if so, how do I do that?
|
|
#5
January 7th, 2007, 08:29 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Primary Keys
I actually created a surrogate id field to supplant the two foreign keys.
How would I set up a Unique index over the two fields? "Rick Brandt" wrote: Nanette wrote: I have a table with two primary keys (its a linking table). I'd like both the fields to be able to accept null values. Is this possible, and if so, how do I do that? Nope. Fields that are part of the PK cannot be Null. You could use a surrogate PK (like an AutoNumber) and create a Unique Index over the two fields you are using now for the PK. Fields in a Unique Index can be Null. -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com
|
|
#6
January 7th, 2007, 08:33 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Primary Keys
Nanette wrote:
I actually created a surrogate id field to supplant the two foreign keys. How would I set up a Unique index over the two fields? They didn't make that very obvious. While in table design view go to menu... View Indexes ....and in the resulting form enter... IndexName FieldName1 FieldName2 (at bottom) Unique = "yes" -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com
|
|
#7
January 7th, 2007, 09:29 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Primary Keys
I'm getting an error message when I attempt to change one of the fields to
unique that says: The changes you requested to the table were not successful because they would create duplicates values in the index, primary key or relationship. Change the data in the field or fields that contain duplicate data (I checked and there is none), remove the index, or redefine the index to permit duplicate entries and try again. HELP! "Rick Brandt" wrote: Nanette wrote: I actually created a surrogate id field to supplant the two foreign keys. How would I set up a Unique index over the two fields? They didn't make that very obvious. While in table design view go to menu... View Indexes ....and in the resulting form enter... IndexName FieldName1 FieldName2 (at bottom) Unique = "yes" -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com
|
|
#8
January 7th, 2007, 09:44 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Primary Keys
Nanette wrote:
I'm getting an error message when I attempt to change one of the fields to unique that says: The changes you requested to the table were not successful because they would create duplicates values in the index, primary key or relationship. Change the data in the field or fields that contain duplicate data (I checked and there is none), remove the index, or redefine the index to permit duplicate entries and try again. HELP! You don't make each field unique. You make the index that is made up of both fields unique. Again; in the index form you put the name of the index and the name of one field on one line and then only the name of the second field on the next line (index name will be blank on that line). Then put your cursor back on the line where the index name is and and then set the Unique = Yes at the bottom. -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com
|
|
#9
January 7th, 2007, 10:19 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Primary Keys
Nanette wrote:
I'm getting an error message when I attempt to change one of the fields to unique that says: The changes you requested to the table were not successful because they would create duplicates values in the index, primary key or relationship. Change the data in the field or fields that contain duplicate data (I checked and there is none), remove the index, or redefine the index to permit duplicate entries and try again. Does one of the fields contain Null values? (Per earlier suggestion that you could possibly allow Null FK fields...) -- Smartin
|
|
#10
January 7th, 2007, 10:41 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Primary Keys
Thanks Rick,
Sometimes I can be pretty dense! "Rick Brandt" wrote: Nanette wrote: I'm getting an error message when I attempt to change one of the fields to unique that says: The changes you requested to the table were not successful because they would create duplicates values in the index, primary key or relationship. Change the data in the field or fields that contain duplicate data (I checked and there is none), remove the index, or redefine the index to permit duplicate entries and try again. HELP! You don't make each field unique. You make the index that is made up of both fields unique. Again; in the index form you put the name of the index and the name of one field on one line and then only the name of the second field on the next line (index name will be blank on that line). Then put your cursor back on the line where the index name is and and then set the Unique = Yes at the bottom. -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com
|
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
