Can You add Unique Indexes to a developed database?

[Comn8u]

Can you add Unique Indexes to an Access database that has been long since created? Will it mess with the queries in the database? I'm trying to upsize this monsterous database to MSSQL Server and would hate to have to recreate the table structure from scratch. Does anyone know?

 

[willir]

A conditional Yes.

In the design view you can high light the desire field, right click and select primary key option. When you save or eixt and save from the design mode, if all the conidtions are met (unique, no nulls), it will save the table with the new index.

While in table design modem you can also select from the menu, "View" and then "Indexes". Here you can add your required indexes including adding two fields together to act as the primary index, or meet a search criteria. Once again, all the conditions for the index have to be met before you can successfully save.

If you do not have a unique field that allows you to successfully save the design, you do have the ability to create one. First backup your table. When you create a new unique ID field, you have two options.

First when you define it, define it as an "autonumber". When you save the table, you will be warned about integrity rules (because you just defined a field that requires a value when you have 100's or 1,000's of records with a null value.) The system, if successful, will numeric add a sequential value to each record.

Or you can define the field to your specifications, and create some code to update the field for each record.

Once complete, you can make the new field the primary field.

Of course if you create your own unique ID field, you will have to recreate each relation. (Hint: If have relations between tables and have to add a unique field for each table, then you pretty well have to use code to loop through each record and ensure the foreign key matches the new field. Messy)

Richard

 

[MikeRBS]

The relational model specifically specifies that your database must be independant of any indexes except in terms of performance. So you can add or remove them to your heart's content. The exception to this is where the dbms insists (which it is entitled to do). An example is where Jet always puts an index on a primary key. But this is a mechanical thing without any logical impact.

As regards creating a uniqueness constraint then this is either logically valid or not. Put an index on the table and see if it rejects due to duplicates. If not, you're in business.

Once you've added an index then you can optionally recompile existing queries to take advantage of it. Just off hand I've forgotten how you do that - is it compact/repair?

It is unusual for an index to degrade performance, although it can happen if you have high levels of update activity compared to retrieval.

The only problem I can see is Access often presents records in index order. A relation is by definition unordered so a programmer should always explicitly order relations if that is needed but I imagine lots of people don't bother if they always see the records coming in the order they want. This functionality might be disturbed by a new index.

 

[jksmi]

Hi,

if I remember correctly then you compile a query by simply running it and saving. Access automatically assigns a sort order to any index that you create - you can change the sort direction in View>Indexes or you can change the order by for the whole table in the table properties (in design view)

HTH, Jamie
FAQ219-2884