Index Definition in SQL

1. Some SQL implementations includes data definition commands to create and drop indices. The IBM SAA-SQL commands are
   • An index is created by

     

   • The attribute list is the list of attributes in relation that form the search key for the index.
   • To create an index on bname for the branch relation:

     

   • If the search key is a candidate key, we add the word unique to the definition:

     

   • If bname is not a candidate key, an error message will appear.
   • If the index creation succeeds, any attempt to insert a tuple violating this requirement will fail.
   • The unique keyword is redundant if primary keys have been defined with integrity constraints already.
2. To remove an index, the command is