SQL Create Table with Indexes.
Creating Indexes with the CREATE TABLE statement
In some RDBMS you can create indexes with the CREATE TABLE statement.Syntax:
MySQL, SQL server:
CREATE TABLE table_name
(col_name column_definition,
col_name column_definition,
...
INDEX index_name (index_col_name, ... )
...Example:
CREATE TABLE Bookstore2 (
ISBN_NO varchar(15) NOT NULL,
SHORT_DESC varchar(100) ,
AUTHOR varchar(40) ,
PUBLISHER varchar(40) ,
PRICE float ,
PRIMARY KEY (ISBN_NO),
INDEX SHORT_DESC_IND (SHORT_DESC, PUBLISHER)
);RDBMS will create a unique index for ISBN_NO since this column is defined as a PRIMARY KEY. PRIMARY KEY field can also be of Composite type, which is to include more than one column.
The index, SHORT_DESC_IND, is defined as a composite index for SHORT_DESC and PUBLISHER columns.
Oracle:
Oracle has no INDEX statement inside the Create statement,
but we can create one through the CONSTRAINT statement or on the PRIMARY KEY.
Index on CONSTRAINT example:
CREATE TABLE Bookstore2 (
ISBN_NO VARCHAR(15) PRIMARY KEY,
SHORT_DESC VARCHAR(100) NOT NULL ,
AUTHOR VARCHAR(40) ,
PUBLISHER VARCHAR(40) NOT NULL ,
CONSTRAINT my_Constraint UNIQUE (SHORT_DESC, PUBLISHER)
USING INDEX (CREATE UNIQUE INDEX MY_IDX on Bookstore2(SHORT_DESC, PUBLISHER))
);Index on PRIMARY KEY example:
CREATE TABLE Bookstore (
ISBN_NO VARCHAR(15) NOT NULL PRIMARY KEY
USING INDEX (create index pk_ind_b on Bookstore(ISBN_NO)),
SHORT_DESC VARCHAR(100) NOT NULL ,
AUTHOR VARCHAR(40) ,
PUBLISHER VARCHAR(40) NOT NULL
);Alternatively, and perhaps the most commonly used method is to add an index to a table when creating an index.
PostgreSQL:
You can only create UNIQUE indexes with the Create table statement in PostgreSQL.
You need to add an index to a table when creating an index.
Next in menu
© 2010 by Finnesand Data.
All rights reserved.
This site aims to provide FREE programming training and technics.
Finnesand Data as site owner gives no warranty for the
correctness in the pages or source codes.
The risk of using this web-site pages or any program
codes from this website is entirely at the individual user.