Error 3626-Can't force referential Integrety

  • Thread starter Thread starter Laura M
  • Start date Start date
L

Laura M

I am trying to add several tables to the relationships screen. When I try to
enforce Referential Integrity, Cascade Update and Cascade Deletes, I get an
error saying there are too many indexes in Tbl Agent. I only have 2 indexes
in that table. Can anyone explain what the problem might be?
 
I am trying to add several tables to the relationships screen. When I try to
enforce Referential Integrity, Cascade Update and Cascade Deletes, I get an
error saying there are too many indexes in Tbl Agent. I only have 2 indexes
in that table. Can anyone explain what the problem might be?
Click to expand...

Access builds hidden indexes too, one for each foreign key in your
table. There's a limit of 32 total indexes, including these hidden
ones. Sounds like you might have a *lot* of foreign keys
(relationships to other tables) and are hitting this limit.

This usually (but not always) indicates that your database design is
not well normalized. Perhaps some of your repeating data would be
better in related child tables.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com
 
All of the tables link to one table (Tbl Agent). Could the problem be that I
am trying to create too many relationships? I inherited this database. It
is probably over normalized, but it's really too big to redesign at this
point. Is there a way to see the hidden indexes?
 
All of the tables link to one table (Tbl Agent). Could the problem be that I
am trying to create too many relationships? I inherited this database. It
is probably over normalized, but it's really too big to redesign at this
point. Is there a way to see the hidden indexes?
Click to expand...

You might have some redundant indexes on tblAgent that could be
removed, since Access creates them for fields involved in
relationships anyway. Try removing any of the visible ones.

Check out this guide for more info:
http://www.allenbrowne.com/bin/Access_Basics_Crystal_080220_Chapter_04.pdf

And this thread:
http://www.eggheadcafe.com/software/aspnet/32630786/too-many-indexes.aspx

I don't see too many *over* normalized databases - usually they're
under normalized.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com
 
Back
Top