UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Confusion With Primary Key In Access 365, Access 2016    
 
   
N.C.Barrett
post Apr 24 2019, 01:30 PM
Post#1



Posts: 11
Joined: 22-April 19



I understand that Access uses the Primary Key as a unique identifier for each record in a table.

I am using "On Access" by Grover Park (in addition to other resources, like this forum) to learn Access 365. Between pages 83 and 86, something confuses me. As instructed, I used the Indexes button and made pkHouseholdID the key. A couple pages later, the book says, "...you already know there can be no nulls in our primary key field, pkHouseholdID." I told Access that that field cannot have nulls, and it says it can't do that. How do I solve this?

When I tried to save its table, Access also told me I couldn't have pkHouseholdID as an AutoNumber field. I changed it to Number so Access would save it. How do I solve that?
Go to the top of the page
 
theDBguy
post Apr 24 2019, 01:35 PM
Post#2


Access Wiki and Forums Moderator
Posts: 75,527
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Welcome to UtterAccess!
welcome2UA.gif

I don't have a copy of that book, so I am not sure I understand your confusion. However, George should be along shortly. Cheers!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
tina t
post Apr 24 2019, 03:15 PM
Post#3



Posts: 5,956
Joined: 11-November 10
From: SoCal, USA


QUOTE
I told Access that that field cannot have nulls, and it says it can't do that.

sounds like you already have records in the table. if that is correct, and if any of those records has a Null (no data, basically) in the field you're trying to designate as the primary key, Access won't accept the pk designation.

QUOTE
When I tried to save its table, Access also told me I couldn't have pkHouseholdID as an AutoNumber field.

again, sounds like there are already records in the table. or perhaps you're using the AutoNumber data type for another field in this table, already? Access will only allow one field in a table to have AutoNumber data type. and if there is data already in a field that you're trying to change to AutoNumber data type, Access could very well refuse to do that. how to fix that issue would depend on whether you're already using that field as a foreign key in another table.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
N.C.Barrett
post Apr 25 2019, 01:56 AM
Post#4



Posts: 11
Joined: 22-April 19



This is a completely blank table, without any records. I was trying to set up the table properly before putting any records in, which I'm told is a good database design practice.

Since I lack much database design and maintenance experience, I suspect but can't confirm that you're right about the AutoNumber field type issue. It makes sense to me personally that you can't use the AutoNumber field type twice in a database, but then I get lost trying to figure out what the book's logic is in this. I suspect it's about learning how to use Primary Keys and Indexes, but I'm not clear on what Indexes are or their purpose.
Go to the top of the page
 
gemmathehusky
post Apr 25 2019, 09:12 AM
Post#5


UtterAccess VIP
Posts: 4,679
Joined: 5-June 07
From: UK


The field(s) described as a PK cannot have nulls. You need a PK for access to manage record updates and so on.

An index can be unique or non-unique, but you can allow nulls in the fields(s) in the index.

Note a very important catch because of this. The nulls allow an otherwise unique index to become non-unique (in my logical terms). You can duplicate values in a multi-field index where one part of that index is null. ie multiple records with the same data, but where the specific field is null.

A long while ago I tried to use "null" in a data field to distinguish a date field, so that a null date was valid for any date entry in linked records, but a specific date mandated that date in linked records. I expected only to be able to enter one record with a null date, as the date field was part of a unique index, but I was able to enter multiple records all with null. I had to resdesign to use date 0, and then specifically test for date 0.

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
projecttoday
post Apr 26 2019, 01:38 AM
Post#6


UtterAccess VIP
Posts: 10,758
Joined: 10-February 04
From: South Charleston, WV


Indexes are for reading specific records as opposed to searching through the table record-by-record to find a match.

If you're getting results that seem illogical try starting over again with making a new table with the new concept from the start. That way we can be sure Access just wasn't confused. If that doesn't work, post the steps you went through here so we can take a look at it.

--------------------
Robert Crouser
Go to the top of the page
 
N.C.Barrett
post Apr 28 2019, 12:46 PM
Post#7



Posts: 11
Joined: 22-April 19



Are Indexes related to linking tables? or have I linked two processes or characteristics that don't need to be connected?

It looks like I solved the problem. I really need to read ahead in the book before asking more questions. tongue.gif

I've come back to this project after a few days, and I think the book's point at this juncture is to explore primary keys and indexes. I suspect that because of its discussion of primary (and foreign) keys in the pages cited and just beyond them. Because the book refers to an outdated version of Access, I have to extrapolate what it's doing to what I have. It was showing the user how to set up Primary Keys for each table, whereas Access 365 does that automatically (and already) for the user using a field named ID.

By the way, thanks for everyone's help. I appreciate all of this being put in perspective. Cheers!
Go to the top of the page
 
projecttoday
post Apr 28 2019, 12:58 PM
Post#8


UtterAccess VIP
Posts: 10,758
Joined: 10-February 04
From: South Charleston, WV


An index is applied to a table.

A table might or might not be linked. Linking or not linking to a table does not affect its indexes.

When you create a table in Access it automatically creates some default indexes. I don't know exactly what it does but I think there's always one there for the primary key. Whenever you create a new table you should open up the indexes and check. Add indexes you need and remove the ones you don't need. I think this is the way all versions of Access work.


--------------------
Robert Crouser
Go to the top of the page
 
gemmathehusky
post Apr 28 2019, 01:14 PM
Post#9


UtterAccess VIP
Posts: 4,679
Joined: 5-June 07
From: UK


Access automatically creates indexes for fields ending in certain text values (such as Id, Ref etc)

This is amendable, I think in File/Options.

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
projecttoday
post Apr 28 2019, 02:10 PM
Post#10


UtterAccess VIP
Posts: 10,758
Joined: 10-February 04
From: South Charleston, WV


Okay. Of course the primary key will still appear automatically as an index even if it does not end in id or ref.

If you delete the primary key from the indexes then it removes the primary key designation from the field. (The field itself remains.)

As I said earlier, after you create a table you should check the indexes to make sure all the ones you need are there and get rid of ones you don't need.


--------------------
Robert Crouser
Go to the top of the page
 
AlbertKallal
post Apr 29 2019, 03:28 AM
Post#11


UtterAccess VIP
Posts: 2,813
Joined: 12-April 07
From: Edmonton, Alberta Canada


I think this issue is more of an Access UI issue.
When creating a table, you can create/set the primary keys two ways.

You can while on the row in the table designer simply hit (click) on the Primary key icon on the ribbon. (This just creates the index and does all the settings for you in one click). Nice and easy.

You can ALSO achieve the exact same thing by launching the index panel from the ribbon. This panel that opens up will show you all the indexes, and of course the one used for the PK (if you have created and set the column as PK).

This panel is thus a “manual” step by step way to create that PK. So you have to fill out all the information by hand, but the one click “key” on the ribbon does all that for you with one click.

But, BOTH result in the SAME thing and goal of creating a PK column for that table.

So the “index” panel, and the single Primary key icon on the ribbon can both allow you to create and set the PK.

QUOTE
I told Access that that field cannot have nulls


How did you do that?

There is NOT an option or feature in Access that tells or sets the column to not allow nulls. What you must do is set the column as required. And this is NOT set in the indexing panel area.

I am betting you thinking of the index settings – not the fact that no nulls are allowed.

Likely what you did was try and set the index to “ignore” nulls.

Like this:




So “ignoring” nulls in an index is a VERY different then trying to tell Access to NOT allow nulls in that column.

To my knowledge there is NOT a feature in Access that says nulls are “not” allowed. The only exception is the PK column – yes, they must have a value.

And the 365 version of access works the same for Access – at least since access 2007 – so no, no difference here.

So an index set to “ignore” nulls is VERY different then not allowing nulls for that column. It just turns out that PK is a special case – they are required.

A PK cannot be “missing” or empty, but there is not really a setting in Access for “general” columns to not allow nulls.

However, you CAN in the table properties state that the column is required – so in a roundabout way, I suppose that is a setting that says no nulls allowed in that column (but you can have an empty column that is not null).

Why an option in indexing to ignore nulls?

Indexing of “nulls” is optional in Access. In a LOT of cases we don’t want access to index a column that will often have no values. If a large amount of records for a given column will NOT have values, then it is a VERY good idea to tell the index to ignore (not index) the empty values for that column. The reason is indexing does NOT work well all the values are the same.

So, indexing tends not to work well if the column has many repeated values – and this rule applies to indexing null values. This is also why you rare will index a check/box (true/false) column – because they have the same repeating value (indexing does not work well with repeating values).

So, keep in mind that an index and a column are two very different things.

What are indexes for?

In one word?

Speed – and lots of it!!!

Indexing in Access is an astounding high performance option.

So, indexing does several things:

Very (but VERY fast searching of data).

And, since they work so very fast, then that feature is ALSO used by Access if you want to prevent duplicates in a column.

You might be say entering invoice numbers, or say a part number. (These like are NOT the PK columns).

Well, we thus kill two birds with one stone.

To prevent duplicates, we need VERY fast “checking” and “searching” if the invoice number is already in that table.

So, once again, the index is used for this no duplicate feature because they work so fast! I suppose a database system could offer an option to say no duplicates, and not use an index, but then that would be too slow.

So to prevent duplicates in a column, you MUST create an index for this feature.

Mostly, an index is for high speed searching of data.

If you have say a excel sheet of 100,000 rows?

If you go ctrl-f to search for that invoice number, then Excel will do a row by row search. It can take a LONG time – the whole document will be searched.

In Access? If you have 100,000 rows? Or even 1 million rows?

You ask access to find that invoice?

Try less then 100th of a second!!!!

Indexing results massive high speed performance data retrieval – you thus get mainframe performance on your desktop!

The indexing ability of Access is often why Access is so much better to manage data then say Excel.

So, indexing is a VERY (but VERY VERY) high speed technology that allows you pluck out a value of a table.

And if Access does not have an index to use when searching? Well, then access will do a row by row search and look at all 100,000 rows of data to find that invoice (just like Excel does!).

If Access has an index on a column, then it is automatic used when searching for a value – and they are blistering fast.

The result you search for in most cases is thus instant – even with 1 million rows of data!

In fact this “basic” indexing technology found in database systems are what makes database systems so powerful and amazing. And this is why Access is so much batter then managing data then say Excel is. Excel does not have indexing technology.

Use indexing with caution.

Access exposes all those “extra” indexing options because this feature comes at a cost. (So you can tweak and change some indexing features for this reason).

The issue is that there is “overhead” to maintain the index. That index will result in a “copy” of that column being “held” in a hidden index table behind the scenes. And there is additional CPU used when you save a record (because the indexing system now has to be updated). This all occurs very fast, but for larger table operations, it can hurt performance. That extra cost of maintaining the index is well worth the cost because you gain such huge searching speed. But, the feature does not come for free in terms of computer resources (you will use both more CPU to update a record, and you use more disk space).

With experience, you gain the knowledge when an index is a very bad idea. One such example is if you doing a huge import of data. Indexing can slow down the massive import, so such an import will run much faster without indexing.

So, you only create indexes on columns that you frequently search by – columns that you never or VERY rare search by should not have an index due to the extra disk space, and processing power required to maintain each index.

I could I suppose explain how indexes work, but for now, just keep in mind that indexes are a “earth shattering” feature that allows Access to deal with large data tables with stunning performance.

As always, such a powerful feature should be used, but used with caution.

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada


Go to the top of the page
 
GroverParkGeorge
post Apr 29 2019, 07:24 AM
Post#12


UA Admin
Posts: 35,138
Joined: 20-June 02
From: Newcastle, WA


Hi, I wrote that book back in 2004 (that's 15 years now) and I really do not remember that section.

However, based on your post, I'm afraid I don't see a contradiction unless you are encountering a data entry problem.

We can't have nulls in a Primary Key field. That's true.

If, as someone pointed out in the thread above, you have PREVIOUSLY inserted records into a table, and have left Nulls in one or more of those records in the field that will be designated as a Primary Key, then, no, Access can't allow that field to be designated as the Primary Key because of those nulls.

However, the discussion has gone well beyond that, so I'm going to back off, read the rest of the thread more in detail and reflect a bit on 15 years of good times with Access.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
N.C.Barrett
post Apr 29 2019, 01:29 PM
Post#13



Posts: 11
Joined: 22-April 19



As stated previously, I encountered this problem with a blank table, so I assume it was beginner ignorance. The key point (pun partly intended) I get with this is, my beginner status and the changes in the Access UI caused the confusion. As Grover Park George points out, the book I'm using was written 15 years ago, so I have to figure out what it's telling me.

Thanks for this in-depth discussion of Access indexes and primary keys. You really laid it out for me. laugh.gif notworthy.gif

Since I'm learning Access partly as a stepping stone to SQL in general, I do have a question. Do indexes exist in other databases, like SQLite and MariaDB?
Go to the top of the page
 
AlbertKallal
post Apr 29 2019, 02:13 PM
Post#14


UtterAccess VIP
Posts: 2,813
Joined: 12-April 07
From: Edmonton, Alberta Canada


QUOTE
Do indexes exist in other databases, like SQLite and MariaDB



Yes most systems have some form of indexing ability. However, I have worked on systems that don’t have indexing – I actually wound up building my own indexing system for that system. Turns out, some years later they did add indexing to that system. (This was d3/pick – a multi value database, and one that not a relational database in a traditional sense (it is a post relational database, or what we common hear about today as the “no SQL movement”.

I have an article written about converting such a system to Access (relational database) here:

http://www.kallal.ca/Articles/fog0000000003.html

The above is a great read, since I share ideas and concepts I used for converting that database system to Access. And that database could have well been an xml or “no SQL” database. So I share a lot of great ideas, thinking and tips on how to approach things with Access.

I am just installing and setting up SQLite. (Having trouble getting the ODBC driver to work with Access for some reason). I’m getting up to speed with SQLite because I need it for an Android application I am building.

In fact, in most cases near all systems you have will have some indexing option – it often just a question of what commands, syntax or type of GUI you have for that system to “set up” an index.

These days in general, you don’t have to worry too much about if indexing features exist - since all databases tend to have this feature.

I would however look at what we call referential integrity. This is a feature in a database that can “enforce” not only things like no duplicates allowed in a column, but will also enforce relationships.

This means that you can’t start entering invoices into the invoice table, unless in the parent table of customers exists in the customer table. So enforced relationships is a big feature.


And now, if you say create an invoice, but then decide to remove that customer? Well then all invoices, and invoice details table will now delete. And since you could have many invoices, and each invoice could have many “details” in the invoice detail table? Well, then quite an amazing “cascade” of deletes will now occur in your database – all because you deleted one customer record – it thus triggers a “cascade” of many other records being delete – and you don’t have to write any special code for this to occur – you just delete the customer, and the system takes care of the rest for you.

So “RI” and cascade delete (and cascade update) also tends to be a standard feature in most database systems. It seems like yesterday, but I recall when MySQL was just receiving the RI cascade and enforcement features.

So, yes Maria DB which is a “fork” of the open source MySQL has these indexing features (and the RI features too).

And of course indexing is a huge topic of its own – thankfully these days all we much care is can we index? And does the database have features like RI?

I plan for example to use both cascade delete, and cascade update with SQLite.

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada


Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    19th June 2019 - 12:36 AM