The syntax for creating Indexes and Primary Keys in SQL

Who I am
Martí Micolau
@martimicolau
Author and references

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.


Adriana gil We are a specialized and passionate team of virtual reality. We have extensive experience in this area. We decided to create ForVirtualRealityLovers to share all our information with customers and users. We have quality information. You can find tips, guides, interviews, top products and much more! If you are curious, enter our site ForVirtualRealityLovers.com and discover the virtual world! 😉 How to recover deleted files on PC ❯

Articles related to

Edit PDFs as if they were images
Assign a fixed letter to the disks
How to convert a web page to PDF
How to recover damaged or deleted files on Windows
The best NAS 2021 for home and office: buying guide
Partition Master Free the best program to partition hard drives
Add a comment from The syntax for creating Indexes and Primary Keys in SQL
Comment sent successfully! We will review it in the next few hours.

Software

  • How to delete a locked file from your PC
  • Edraw Max: complete software for diagrams, mind maps and organization charts
  • Is it possible to recover a wet Notebook?
  • How to recover deleted files for free
  • Best app for expense management and family budget
  • Best programs to uninstall on Windows 10
  • How to create a social network with the Ning platform
  • How to save images from a pre-Office 2010 Word document
  • Best SSDs 2021 for your PC: buying guide
  • How to dictate a text in Word
  • Javascript: how to use pdfObject to view PDF files
  • How to open a file with the .tar.gz extension
  • How to compare two Excel files and find the differences
  • How to color alternate rows in Excel
  • How to edit a PDF file with LibreOffice and OpenOffice
  • Why WAMP doesn't work in localhost on Windows
  • How to recover a Word document
  • How to schedule and launch an SSIS package (DTS) Job
  • How to shrink the log file on all databases
  • How to empty the Recycle Bin automatically
❤️Chi Sleep For Virtual Reality Lovers?

For Virtual Reality Lovers we are a team of people who aim to give you all the best information about Virtual Reality.


What information do we share?

We share everything you need to know about virtual reality, tips, prices, features, guides and much more !.

🤖What are the best virtual reality products?

We have the best information section on the best virtual reality products.


This work is under a Creative Commons Attribution-Non-Commercial-No Derivatives 4.0 International License. 2021/primerpaso.me X

Check out our best articles!

Programs to delete undeletable files

How to compare two text files

Programs to delete unnecessary files

How to download and clone an entire website

add a comment of The syntax for creating Indexes and Primary Keys in SQL
Comment sent successfully! We will review it in the next few hours.