Error 3626-Can't force referential Integrety

[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?

 

[Armen Stein]

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?

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

 

[Laura M]

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?

 

[Armen Stein]

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?

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