Troubleshoot keys, relationships, and indexes (ADP)

Keep in mind the following guidelines when creating or modifying a primary key:

• All columns that participate in a table's primary key must contain unique values other than Null. To add the column to a primary key, first clear the Allow Nulls check box on the Column Property dialog box.
• A primary key must contain at least one column and contain unique data values.
• You cannot create a primary key using columns with a text, ntext, image, or bit data type.
• The maximum number of columns you can use to create a primary key is 16.
• The sum of the lengths of all columns in a primary key cannot exceed 900 bytes.

Keep in mind the following guidelines when creating or modifying a database relationship and corresponding columns:

• A database relationship must relate at least one column from the foreign-key table to at least one column in the primary key table.
• At least one of the two tables must have a primary key or unique constraint defined and the columns on the primary key side of a database relationship must participate in either a primary key or a unique constraint.
• Both sides of the relationship must have the same number of columns; the maximum number is 16 columns for each side of the relationship.
• The Data Type, Length, Precision, Scale, and Collation properties of each matching columns between the tables must be the same.
• Deleting columns that participate in relationships will delete the relationships associated with them.
• You cannot change a table's primary key or unique constraint while other foreign key relationships are still in place. To change a table's primary key or unique constraint, first delete all existing relationships for the table.
• A computed column cannot participate in relationship. Adding a formula to a column will delete all relationships it participates in. Either clear the column's Formula property or remove the column from the index or relationship.
• Removing a formula from a column may leave it with a data type that is disallowed in relationships.
• If you change the data type of a column that is related to columns in other tables, then the data type of the related columns must also be changed to preserve referential integrity. When you save the table or database diagram, Access displays the Datatype Change Required dialog box so you can automatically change the data type of the related columns.

The Microsoft OLE DB Provider for SQL Server 6.5 (SQL Service Pack 5) is implemented as a dynamic link library (DLL) on the client machine and a set of stored procedures on the server. Unless both parts are installed, the OLE DB provider will not function correctly. Also, if you install a newer version of Sqloledb.dll, but do not install Instcat.sql on each Microsoft SQL Server machine running OLE DB, some OLE DB functions will return incorrect results.

To resolve this, you should run Instcat.sql to install the stored procedures on the Microsoft SQL Server you wish to access with OLE DB.

For more information on installing SQL Server, see the SQL Server documentation.

Keep in mind the following guidelines when creating, modifying, or deleting an index or a column that participates in an index:

• An index must contain at least one column. If you are creating a unique index, that column must contain unique data values.
• You cannot create indexes of columns with a text, ntext, image, or bit data type.
• The maximum number of columns you can use to create an index is 16.
• The sum of the lengths of all columns in an index cannot exceed 900 bytes.
• Be careful deleting a column or changing it's data type. If the column participates in an index, you may end up deleting the index unintentionally.
• You can only create one clustered index on a table. A clustered index is a special type of index that reorders the way records in the table are physically stored. If you want to create a two or more indexes on the same table, the additional indexes must be non-clustered indexes.

• In SQL Server 7.0 and 6.5 databases, a computed column cannot participate in an index. Either clear the column's Formula property or remove the column from the index.

• Columns you modify may contribute to one or more indexes. If you modify the column and it becomes a computed column, it cannot contribute to indexes anymore.