The syntax for creating Indexes and Primary Keys in SQL

The use of indices or delle primary keys inside a table is very important for those who have to manage a very heavy database and rich in records. The easiest way to understand the usefulness of indexes it is through the example of the index present at the end of a book: if you need to identify certain pages in a book, the first you will do is go to consult the index. The same principle applies to consulting a table with many records. The introduction of an index will allow you to avoid long waiting times and save a lot of time.

Without an index the database is forced to read the entire table by scanning it. With an index, the system first makes its consultation in order to identify the positions in which to retrieve the data, allowing much faster processing times.

The use of indexes, therefore, serves to perform faster queries with the only flaw that it increases the volume of data stored and the time taken to manage the search trees in INSERT, UPDATE and DELETE operations as the index it has to be rebuilt every time.

1. The index can involve one or more columns and the syntax to create it via SQL is the following:

CREATE INDEX "index_name" ON "table_name" (field_name);

2. If we want to create an index, calling it IDX_Surname, for the table users in the field Last name it is necessary to write:

CREATE INDEX IDX_Surname ON Users (Surname);

3. If, on the other hand, we want to create more than one index, for example sul Your name, we must write:

CREATE INDEX IDX_Surname ON Users (Surname, Name);

4. In the creation of the index, there is also the possibility to add the term UNIQUE, which specifies that the index created will be unique.

CREATE UNIQUE INDEX surname_IDX ON Users (surname);

Regarding the name of the index there is no precise rule concerning the naming, before the name of the index the most common method is to place the prefix "IDX_".

La primary key identifies so unique records in the table (unique values ​​and absence of null values). To prevent the user from making a mistake in evaluating this field, it is advisable to associate the data type "Counter”Both because it is numeric and because there is no possibility to repeat the same value twice, since the counter starts from 1 and automatically increases when adding new fields to the table.

A primary key can exist in multiple fields, in which case it is given the composite key name.

1.The syntax for create a primary key when creating a table is as follows:

CREATE TABLE User (KeyPr integer PRIMARY KEY, Surname varchar (30), Name varchar (30));

The table is created User and the fields Last name e Your name and the primary key KeyPr.

2. The syntax for creating a primary key by modifying an existing table is as follows:

ALTER TABLE User ADD PRIMARY KEY (KeyPr);

The primary key on the field is added to the User table KeyPr.