Scribd
Upload
49 views

Lab 10 DB

This document discusses creating indexes, dropping indexes, and auto-increment fields in SQL. It provides the syntax for creating regular and unique indexes, and dropping indexes. It also explains that auto-increment allows unique numbers to be automatically generated for a field like a primary key when new records are inserted. The syntax for defining an auto-increment primary key field in MySQL is shown.

Uploaded by

Malik Mohsin
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
49 views

Lab 10 DB

This document discusses creating indexes, dropping indexes, and auto-increment fields in SQL. It provides the syntax for creating regular and unique indexes, and dropping indexes. It also explains that auto-increment allows unique numbers to be automatically generated for a field like a primary key when new records are inserted. The syntax for defining an auto-increment primary key field in MySQL is shown.

Uploaded by

Malik Mohsin
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 3

LAB EXPERIMENT # 10

INDEX Statement and Auto Increment Field

Student Name: Muhammad Ibrahim Roll No: S21BCSDF1M02033

Lab Instructor Signatures: Date:

OBJECTIVES:
• Create Index Statement
• Drop Index
• Auto Increment Field

CREATE INDEX Syntax


CREATE INDEX index_ name
ON table_name (column1, column2, ...);

CREATE UNIQUE INDEX Syntax


Creates a unique index on a table. Duplicate values are not allowed:
CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ...);

CREATE INDEX Example

The SQL statement below creates an index named "idx_lastname" on the "LastName" column in the "Persons"
table:
CREATE INDEX idx_lastname
ON Persons (Last Name);

If you want to create an index on a combination of columns, you can list the column names within the
parentheses, separated by commas:

CREATE INDEX idx_pname


ON Persons (LastName, FirstName);

DROP INDEX Statement


The DROP INDEX statement is used to delete an index in a table.

SQL Server:
DROP INDEX table_name.index_name;

My SQL:
ALTER TABLE table_name
DROP INDEX index_name;

AUTO INCREMENT Field

Auto-increment allows a unique number to be generated automatically when a new record is inserted into a
table.
Often this is the primary key field that we would like to be created automatically every time a new record is
inserted.

Syntax for MySQL


The following SQL statement defines the "Person id" column to be an auto-increment primary key field in the
"Persons" table:

CREATE TABLE Persons (


Person id int NOT NULL AUTO_INCREMENT,
Last Name varchar(255) NOT NULL,
First Name varchar(255),
Age int,
PRIMARY KEY (Person id)
);

MySQL uses the AUTO_INCREMENT keyword to perform an auto-increment feature.


By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record.
To let the AUTO_INCREMENT sequence start with another value, use the following SQL statement:
ALTER TABLE Persons AUTO_INCREMENT=100;

To insert a new record into the "Persons" table, we will NOT have to specify a value for the "Person id"
column (a unique value will be added automatically):

INSERT INTO Persons (First Name, Last Name) VALUES ('Lars','Monsen');

The SQL statement above would insert a new record into the "Persons" table. The "Person id" column would
be assigned a unique value. The "First Name" column would be set to "Lars" and the "Last Name" column
would be set to "Monsen".

You might also like