ACCESS FEED: RE: [MS_AccessPros] Primary Key

Senin, 25 Januari 2016

RE: [MS_AccessPros] Primary Key

Oops – my apologies – I completely concur with Darrell's statement about only one primary key – I just misread it!! 8-/

Everything else I said holds though, except that I should have said that while setting IgnoreNulls to Yes/No does not affect the BEHAVIOUR of the index, it does affect its PERFORMANCE. Any non-primary key which includes non-required field(s) should be set to IgnoreNulls so that the index does not have unnecessary entries for the records with Nulls.

Best wishes,
Graham

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Tuesday, 26 January 2016 14:25
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Primary Key

Hi Sarah

Someone said earlier "there can only be one primary key per table". This is not correct – you can have only one primary key (which must be unique and whose fields are required – i.e. cannot be null).

However, you can have multiple other indexes which may or may not be unique, and may or may not comprise multiple fields. If one or more of those fields is not "Required" then it can contain a null, but it will not be entered into the index. Therefore, you can have a unique index on a non-required field and have as many records as you like with Null values.

Similarly, as you have discovered, you can have a two-field index with one required and one non-required field, and have as many duplicates as you like in the required field so long as the non-required one is null. The index does have an "IgnoreNulls" property which I believe should stop this behaviour, but in 24 years working with Access I have yet to see this make any practical difference.

I think what you should do, then, is make Color required and do NOT allow zero length, and make Season required but DO allow zero length. Then, when you want a color with no season, set the season to "" (a zero-length string). that way the index will allow only one such combination.

Best wishes,
Graham

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Tuesday, 26 January 2016 13:17
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Primary Key

Crystal,

dont understand what do you mean:

1. either add "No Color" and "No Season" as choices and do not Allow Nulls
Sarah

---In MS_Access_Professionals@yahoogroups.com, <strive4peace2008@...> wrote :

Hi Sarah,

> "allowing me to enter the same Color multiple times, without as

Season"

yes, you have to make a decision:

1. either add "No Color" and "No Season" as choices and do not Allow Nulls
OR
2. continue as it is now (Allow Nulls and do not build index where both are not specified)

warm regards,
crystal

~ have an awesome day ~

On 1/25/2016 3:58 PM, sarahk@... [MS_Access_Professionals] wrote:

Crystal,
I am using the bolt.
The problem is that the Season is optional.
If I specify color and season as a primary key, I get a message that primary key can not contain a null value.

If I specify 'Unique-Yes for Unique Index' it is working except for it is allowing me to enter the same Color multiple times, without as Season. Works fine if the Season is specified.

Sarah

---In MS_Access_Professionals@yahoogroups.com, <strive4peace2008@...> wrote :
thanks, Bill :) [ ... and I am still thinking ... just in case you wondered why I haven't said anything]

Sarah, you can also click on the lightening bolt icon on the ribbon to toggle the display of the indexes window off and on

when you have a multi-field index and are on the first field in the indexes window (the row with the index name), you can set more options for that index:
Primary -- only Yes for Primary Key
Unique -- Yes for Unique Index
Allow Nulls -- Yes if you want to allow any of the value in the index to be null ... I usually choose Yes

warm regards,
crystal

~ have an awesome day ~
On 1/25/2016 2:38 PM, wrmosca@... [MS_Access_Professionals] wrote:
Sarah - Pardon my jumping in...
No, Zero-length strings are not the same as nulls. When you use the Index wizard box you will see a property line for allowing nulls. That's what Crystal was referring to.

-Bill

---In MS_Access_Professionals@yahoogroups.com, <sarahk@...> wrote :

I am following your instructions and making a unique index on the combination.
By 'allow nulls' do you mean 'Allow Zero Length"=Yes?
Sarah

---In MS_Access_Professionals@yahoogroups.com, <strive4peace2008@...> wrote :
Hi Sarah,

don't use that as a primary key -- make the primary key an autonumber.

Instead, create a unique index on the combination and allow nulls ~ I gave you the steps to do that :)

warm regards,
crystal

~ have an awesome day ~

On 1/25/2016 12:21 PM, sarahk@... [MS_Access_Professionals] wrote:

Hi Crystal,
I tried this but I am not getting the desired results.
The fields are Color and Season. The combination has to be unique. The Season is optional.
I defined the Index as Color and Season. Primary = No ( If I try yes, then I get message 'Index or primary key can not contain a null value'.)
I can not enter the same Color and the same Season in the table, but it allows me to enter the same Color without a Season multiple times in the table.
What am I doing wrong.
Sarah
---In MS_Access_Professionals@yahoogroups.com, <strive4peace2008@...> wrote :
Hi Sarah,

by definition, a Primary Key is one or more fields.

I usually use an AutoNumber as the Primary Key, and then a Long Integer as the corresponding Foreign Key.

You can define a UNIQUE INDEX on a field or combination of fields too.

Make a Unique Index on multiple fields:

1. go to the Design view of the table
2. make a unique index on the first field
-- click on the field
-- set Indexed in bottom pane to Yes (No Duplicates)
3. open the indexes window (lightening bolt icon on the Design ribbon)
4. in the second column, the field column, add a second field to the same index by filling out the next row. The first column of the next row will be blank
5. if necessary, add a third field the same way

If you want some of the fields in the index to be allowed to not be filled out then set Ignore Nulls to Yes (in addition to Unique is Yes) in the properties in the Indexes window

warm regards,
crystal

~ have an awesome day ~
On 1/25/2016 10:04 AM, sarahk@... [MS_Access_Professionals] wrote:
Is it possible when defining multiple primary keys for a table, to specify that only the combined primary keys can not have duplicates , but the individual key can have duplicate entries?
Sarah