 |
| Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA. |
Welcome to the p2p.wrox.com Forums.
You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
|
|
|
|
August 15th, 2005, 07:11 PM
|
|
Authorized User
|
|
Join Date: Jan 2005
Location: , VIC, Australia.
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
One to one relationship query
Hi all,
Just wondering on what people's thoughts are on this problem.
I currently have a table with about 70+ fields in it, and now I have hit the 32 index limit on the table.
So my two options are to:
a) create another table and have a one to one relationship with it. Pros: This will allow me to overcome the 32 index limit.
Cons: I will have to join these two tables together for any query and all existing queries and forms will need to be changed to reflect this new table
b) have the one table.
Pros: no changes to current application
Cons: unable to reinforce referential integrity on all the foreign key joins
Even though it seems like it's going to be a bit of extra work, I am favouring option a, but are there any other pro's and con's of either method?
Has anyone else come across a similar problem and what did they do?
What would you do?
Cheers
Rohan
__________________
Cheers
Rohan
|
|
August 16th, 2005, 08:34 AM
|
|
Friend of Wrox
|
|
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
|
|
Why would you need sooooooo many indexes?
I would get rid of some of the indexes, perhaps all but a few.
mmcdonal
|
|
August 16th, 2005, 08:39 AM
|
|
Friend of Wrox
|
|
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
|
|
You can enforce referential integrity without creating an index on the FK. Indexes are only for searches, not referential integrity. If you add additional indexes on FKs, this is for sorting on those FKs for searches. So the Con of not enforcing referential integrity is invalid.
This makes me think that perhaps you shouldn't need so many fields in this table. What is the normalization of this schema?
mmcdonal
|
|
August 16th, 2005, 10:01 PM
|
|
Authorized User
|
|
Join Date: Jan 2005
Location: , VIC, Australia.
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
I actually don't need that many indexes, probably about 5-10 for myself. However, whenever Access creates a link and enforces RI between two tables it creates a hidden index that is counted towards the 32 limit. As I have over 30 lookup tables to this table, I go over the 32 index limit.
Unless I have got this wrong, but I'm sure I don't. If I remove RI from some of the tables then I can create more indexes that I want. However as this main table has 32 joins to other tables with RI enforced, I reach the 32 index limit.
Unless there is a way to turn it off, but I don't think that there is.
Any further comments on this and the original question from anyone?
Cheers
Rohan
|
|
Similar Threads
