Referential Integrity: Beyond ’32 indexes’ limit?

[Eric]

Hi!

In my database, I would like to enforce ‘referential
integrity’ between the records in the ‘Main Table’ and up
to 50 other tables. Unfortunately, when I try to ‘enforce
referential integrity’ on more then 30/32 tables I get the
following error message:
=====
The operation failed. There are too many indexes on table
<MAIN TABLE>. Delete some of the indexes on the table and
try the operation again. (Error 3626)
A table in a Microsoft Jet database can have no more than
32 indexes. You cannot create additional indexes on a
table with this many indexes. Further, you cannot compact
a database containing a table with this many indexes
because compacting a database involves creating several
new indexes.
Delete one or more indexes from the named table and try
the operation again.
=====
How can I enforce referential integrity from the ‘Main
Table’ to more then 32 tables? What are the ‘normal’ ways
of getting around this?

Thanks in advance for your help!

Eric

 

[Lynn Trapp]

The limit of 32 indexes is "un-getaroundable". Without knowing more about
your database, I would suggest that you take some time to look at a redesign
of your database. Enforcing referential integrity between 1 table and 50
others seems a bit excessive to me and something that could be corrected
with good data normalization.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm



Hi!

In my database, I would like to enforce ‘referential
integrity’ between the records in the ‘Main Table’ and up
to 50 other tables. Unfortunately, when I try to ‘enforce
referential integrity’ on more then 30/32 tables I get the
following error message:
=====
The operation failed. There are too many indexes on table
<MAIN TABLE>. Delete some of the indexes on the table and
try the operation again. (Error 3626)
A table in a Microsoft Jet database can have no more than
32 indexes. You cannot create additional indexes on a
table with this many indexes. Further, you cannot compact
a database containing a table with this many indexes
because compacting a database involves creating several
new indexes.
Delete one or more indexes from the named table and try
the operation again.
=====
How can I enforce referential integrity from the ‘Main
Table’ to more then 32 tables? What are the ‘normal’ ways
of getting around this?

Thanks in advance for your help!

Eric

 

[Albert D. Kallal]

You can have relations between hundreds of tables if you want.

Here is partial screen shot of one with 50+ tables (not all are shown):

http://www.attcanada.net/~kallal.msn/Articles/PickSql/Appendex2.html

However, to "one" table, you do have the limit of 32 indexes.

Why do you have so many tables related to "one" table? Do you really need 50
other tables? Something is SERIOUSLY WRONG with the design here. Perhaps you
can add 1 field that has FIFTY different values, and thus only need ONE
table.

It is possible that you need to relation 50 tables to the one table, but I
kind of doubt this makes sense. Is this data normalized at all?

What is different between each of those 50 tables?

 

[eric]

Thanks Albert and Lynn,

I guess I should really have done some reading (perhaps
on 'normalization'?) before asking again - but maybe I can
save some time if you can point out an 'obvious' mistake I
am making - and perhaps an obvious solution - if I
describe what I am doing in more detail..

Well - here is my situation. I am looking after a database
for a multi-year psychological study. There are a number
of women who we are following over two years or so. We
interview them 5 times:

1) before marriage
2) immediately after marriage
3) 3 months after marriage
4) 9 months after marriage
5) 18 months after marriage

Each Interview contains about 10 'sections' concerning
different aspects of the marriage e.g. relationships with
others, living situation, health etc. Each section has
about 30 questions (i.e. 'fields').

Given the above structure, the database is currently set
up like this:

There is one main table called the 'DEMOGRAPHIC' table,
which contains information on the womens' ID, age, race,
age etc.

Then there is a set of 10 tables for each of the 5
interviews.. (given that there are 10 sections or parts to
each interview)

e.g. there is a table for the women's "living situation"
for the before marriage interview - right up to the 18-
month interview, i.e. five "living situation" tables.

5 interviews X 10 Types of table for each of the 5
interviews = 50 Tables.

I have attempted to link the ID field in the DEMOGRAPHIC
Table to the ID fields in each of the 50 tables. To stop
the data-enterers entering invalid IDs (i.e. entering IDs
that aren't in the study) in any of the tables, I have
also tried to enforce referential integrity for these
links.

Unfortunately, I have only been able to enforce
referential integrity for 32 of the tables..

So what should I be doing differently? Should I have a
different structure for my database?

Thanks in advance again!

Eric




They are interviewed at





I have a 'DEMOGRAPHIC' Table.. which has all the

 

[Lynn Trapp]

YES, you definitely need to restructure the database. Without going into
great detail, you could build the main structure of this database around 4
tables (your original 'Demographic' table, an 'Interview' table, an
'Interview_Sections' table, and a 'Questions' table). Each woman will have
multiple interviews, each interview will have multiple sections, and each
interview section will have multiple questions. Thus the basic structure
would be kind of like this:

Demographic Table
WomanID -- Primary Key
LName
FName
Address
---other fields needed

Interview Table
InterviewID-- Primary Key
WomanID -- Foreign Key to Demographic Table
InterviewType
--other fields needed

Interview_Sections Table
Interview_Section_ID -- Primary Key
InterviewID -- Foreign Key to Interview Table
SectionType
--other fields needed

Questions Table
Question_id -- Primary Key
Interview_Section_ID -- Foreign Key to Interview Section table
Question


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm