Adding a Nonunique Index with SQL DDL

You can’t use the CREATE TABLE statement to add a nonunique index. To add a nonunique index to an existing table, you can use the CREATE INDEX statement:

CREATE INDEX Ix_Violation_Type 
	ON Parking_Tickets (Violation_Type)
	WITH DISALLOW NULL;

Because the index is not specified as unique, any number of records within the Parking_Tickets table can have the same Violation_Type value. After the Ix_Violation_Type index is created, searches for records involving the Violation_Type field are noticeably faster. The example uses the optional WITH DISALLOW NULL clause, which prohibits Null values in the indexed field. You can also use the WITH IGNORE NULL clause to prevent records with Null values in the indexed field or fields from being included in the index.

An index can consist of combinations of more than one field:

CREATE TABLE Order_Item
	(OrderID INTEGER,
	LineItem INTEGER,
	ProductType TEXT (10),
	ProductCode TEXT (20), 
	CONSTRAINT PrimaryKey PRIMARY KEY
	(OrderID, LineItem));
CREATE INDEX Ix_ProdTypeCode 
	ON Order_Item (ProductType, ProductCode);