Creating indexes on Interbase

Indexes allow InterBase to locate data (dramatically) more quickly. An index has two key components to it - the field(s) that you will want to search on and whether the field(s) are unique (e.g. a Reference number will probably need to be unique, but you may well need to accommodate several people sharing a birth date or a last name).

One particular type of index that is usually needed is an index on the Field(s) which uniquely identify a record within a table (e.g. the unique reference number given to each record, or a Social Security ID, or a post code and House number/name combination within an Address table). This is called the Primary key. Those who don't feel comfortable with what they are doing might want to see Creating indexes - Tips, prior to "doing it".

Do it!
Creating the Primary Key

Open your "table.SQL" file.


Add a line at the bottom of the definition, immediately before the final ")", and add the phrase (with the comma in front)
, PRIMARY KEY (field)
where field is the name of the field(s) you want as the primary key, eg:
, PRIMARY KEY (REF) or another example: , PRIMARY KEY (LASTNAME, FIRSTNAME, POSTCODE)


If you have already created your table, see Creating indexes - Tips
Creating other Indexes

Open the "Indexes.SQL" file


Add a new index with the syntax (don't forget the semi-colon at the end):
CREATE INDEX NAME ON ANIMALS(NAME);
where "Animals" is the name of the table, and "Name" is the field on which to index (sort)

Web: http://www.ibphoenix.com