index

[prasanth_kasturi]

what is unique indexe?? how it differs with non unique.. whts its usage

Edited by: prasanth kasturi on Dec 31, 2007 8:19 AM

[Former Member]

Hi,

check this link

http://help.sap.com/saphelp_47x200/helpdata/en/9b/c743f5b40711d194f900a0c929b3c3/frameset.htm

Regards

[Former Member]

Hi Prasanth

I am sending you material, pls go thru it.

Pls reward pts if help.

Deepanker

Inroduction to Database Indexes

When you create a database table in the ABAP Dictionary, you must specify the combination of fields that enable an entry within the table to be clearly identified. Position these fields at the top of the table field list, and define them as key fields.

After activating the table, an index is created (for Oracle, Informix, DB2) that consists of all key fields. This index is called a primary index. The primary index is unique by definition. As well as the primary index, you can define one or more secondary indexes for a table in the ABAP Dictionary, and create them on the database. Secondary indexes can be unique or non-unique. Index records and table records are organized in data blocks.

If you dispatch an SQL statement from an ABAP program to the database, the program searches for the data records requested either in the database table itself (full table scan) or by using an index (index unique scan or index range scan). If all fields requested are found in the index using an index scan, the table records do not need to be accessed.

A data block shows the level of detail in which data is written to the hard disk or read from the hard disk. Data blocks may contain multiple data records, but a single data record may be spread across several data blocks.

1.Both Primary & Unique keys are Indexes.

Index:

An index is a structure in a table that orders the data. It allows the database to access data quickly (In MySQL its implemented using B-tree algorithms).

Primary Key:

This is an index that cannot be NULL, Primary Keys are used in building relationships between tables in a database. (an index is automatically created on the primary key). The difference between primary and ordinary keys is that there can be multiple keys, but only one primary key.

Unique Key:

Unique and Index are same, the difference is, in Unique, duplicate are not allowed in any circumstances and that is enforced by database server. Primary key(s) qualify to be Unique on basis

of their uniqueness.

In case, your table has 2 primary keys means that the 2 fields together form one unique key. Each field by itself may have repeating values, but both primary keys combined together must be unique.

2. Primary index: the primary index contains key fields of a table and a pointer to non-key fields of the table. The primary index is created automatically when a table is created in database and moreover you can further define reference to the primary index which are known as Secondary index.

Primary Key:

This is an index that cannot be NULL, Primary Keys are used in building relationships between tables in a database. (an index is automatically created on the primary key). The difference between primary and ordinary keys is that there can be multiple keys, but only one primary key.

pls reward pts if help.

[Former Member]

Unique Indexes

An entry in an index can refer to several records that have the same values for the index fields. A unique index does not permit these multiple entries. The index fields of a unique index thus have key function, that is they already uniquely identify each record of the table.

The primary index of a table is always a unique index since the index fields form the key of the table, uniquely identifying each data record.

You can define a secondary index as a unique index when you create it. This ensures that there are no double records in the table fields contained in the index. An attempt to maintain an entry violating this condition in the table results in termination due to a database error.

The accessing speed does not depend on whether or not an index is defined as a unique index. A unique index is simply a means of defining that certain field combinations of data records in a table are unique.

A unique index for a client-dependent table must contain the client field.

Awrd Points If Useful

Bhupal