Purpose for unique indexes
Experts,
I am seeking affirmation or clarification on indexes that serve to maintain data integrity and setting a primary key as AutoNumber.
As I understand indexes I can create an index that references up to six other fields making each field unique. This index is not a primary key but serves to maintain data integrity. The purpose for the primary key serves as a link in other tables. In other tables this would be known as a foreign key.
I am seeking affirmation or clarification on indexes that serve to maintain data integrity and setting a primary key as AutoNumber.
As I understand indexes I can create an index that references up to six other fields making each field unique. This index is not a primary key but serves to maintain data integrity. The purpose for the primary key serves as a link in other tables. In other tables this would be known as a foreign key.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
BusyMama
Also, think of indexing almost as a filing system - it helps the application retrieve data more quickly.
ASKER
In creating a named index with multiple columns the combination of the columns is unique? I did not mean to imply each column would be unique. The index in and of itself may or may not serve as a primary key. Is this more correct?
In creating a primary key I meant to say that one of its purposes is to be able link to another table as a foreign key and can also serve to maintain data integrity. Is this more correrct?
In creating a primary key I meant to say that one of its purposes is to be able link to another table as a foreign key and can also serve to maintain data integrity. Is this more correrct?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thnaks
I also fall into the "surrogate key" camp.
Primarily because writing SQL to update or delete records that involve multi-column natural keys can be quite dangerous. I once had a guy working for me that was using natural keys that involved 4 columns, and he wrote an update query that only included 3 of those columns in the WHERE clause, overwriting the values in dozens of other records. Didn't take long to cure him of the multi-column natural key.
Primarily because writing SQL to update or delete records that involve multi-column natural keys can be quite dangerous. I once had a guy working for me that was using natural keys that involved 4 columns, and he wrote an update query that only included 3 of those columns in the WHERE clause, overwriting the values in dozens of other records. Didn't take long to cure him of the multi-column natural key.