If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Error 3626-Can't force referential Integrety
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? -- Laura |
#2
|
|||
|
|||
Error 3626-Can't force referential Integrety
On Thu, 11 Sep 2008 06:32:01 -0700, Laura M
wrote: 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 |
#3
|
|||
|
|||
Error 3626-Can't force referential Integrety
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? -- Laura "Armen Stein" wrote: On Thu, 11 Sep 2008 06:32:01 -0700, Laura M wrote: 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 |
#4
|
|||
|
|||
Error 3626-Can't force referential Integrety
On Thu, 11 Sep 2008 11:12:02 -0700, Laura M
wrote: 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/Acces...Chapter_04.pdf And this thread: http://www.eggheadcafe.com/software/...y-indexes.aspx I don't see too many *over* normalized databases - usually they're under normalized. Armen Stein Microsoft Access MVP www.JStreetTech.com |
Thread Tools | |
Display Modes | |
|
|