Index error message

  • Thread starter Thread starter ChrisH
  • Start date Start date
C

ChrisH

I am creating a table "tblMain" with 87 fields, of which
about 80 of those point to about 10 lookup tables (i.e.,
most of these lookup tables are referenced by >1 field in
tblMain). I set referential integrity on 27 of these 80
or so relationships in the relationship window. When I
try to do 28 and beyond (and it doesn't matter which
lookup table I'm referencing), I get the error message:

"The operation failed. There are too many indexes on the
table "tblMain." Delete some of these indexes on the
table and try to run the operation again."

Is there a maximum # of relationships w/ referential
integrity that a table can have?

Can someone help me get around this. THanks
 
I am creating a table "tblMain" with 87 fields, of which
about 80 of those point to about 10 lookup tables (i.e.,
most of these lookup tables are referenced by >1 field in
tblMain). I set referential integrity on 27 of these 80
or so relationships in the relationship window. When I
try to do 28 and beyond (and it doesn't matter which
lookup table I'm referencing), I get the error message:

"The operation failed. There are too many indexes on the
table "tblMain." Delete some of these indexes on the
table and try to run the operation again."

Is there a maximum # of relationships w/ referential
integrity that a table can have?
Click to expand...

Yes: 32 indexes (and each relationship creates an index).
Can someone help me get around this. THanks
Click to expand...

There is no way around it with your current design.

HOWEVER... 87 fields is ENORMOUSLY wide. I have to wonder if you have
a lot of functionally repeated fields, a suspicion heightened by the
fact that several fields in tblMain all reference the same lookup
table. What's the structure of tblMain, and might it actually benefit
from being split into a main table and one or more many-side tables?
 
Back
Top