Referential integirty - 32 index table limit

[Movian]

Hey,
i am in the middle of trying to redesign the backend of our database product and have hit a road block. I now have (believe it or not) over 100 tables that i need linking to a "Primary" table. Previously there was 5 tables each bursting to the limit and in no way normalized. I have now split the tables and forms (from tab controls). HOwever i now come to setup the relationships (for the 60 or so sub forms etc) and find out that every time you create a relationship with referential integrity it creates an index. Also you can only have 32 indexes in a single table. So counting the index on the primary key that i uses for seek's in VBA i have a possible 31 relationships....

so essentially it looks like i have 2 options

1) create relationships WITHOUT referential integrity.

2) figure out a way around this problem.

what would be the downsides to option 1 ? data is not generally deleted. The relationships are almost exclusively used to ensure the correct record is shown on the subframe. And to ensure the correct data is being retrieved from the tables when we need to create reports...

i think i have managed to exceed (almost) all the access restrictions there are


:banghead:

[hansup]

HOwever i now come to setup the relationships (for the 60 or so sub forms etc) and find out that every time you create a relationship with referential integrity it creates an index. Also you can only have 32 indexes in a single table. So counting the index on the primary key that i uses for seek's in VBA i have a possible 31 relationships.... You could use "NO INDEX" in Jet DDL with ADO to create your constraints.

ALTER TABLE tblInvoices
ADD CONSTRAINT FK_tblInvoices
FOREIGN KEY NO INDEX (CustomerID) REFERENCES
tblCustomers (CustomerID)
ON UPDATE CASCADE
ON DELETE CASCADE I copied that SQL statement from http://msdn.microsoft.com/en-us/library/aa140015%28office.10%29.aspx

Good luck,
Hans