Creating Indices and Relationships
Indices can be created (as was the example with the primary key above) while you are creating a table; alternatively, you can create them separately at a later point in time:
CREATE INDEX <indexname> ON <tablename> (<<column1>[, ..., <columnn>])
For example, suppose we wanted to create a unique index on the isbn column of the book table we created earlier:
CREATE INDEX book_isbn ON book (isbn)
The name of the index is, of course, entirely arbitrary and only has a meaning when deleting the latter; however, it must still be unique and abide by the naming rules we described above. Foreign-key relationships are created either when a table is created, or at a later date with an altering statement. For example, suppose we wanted to add a table that contains a list of all of the chapter titles for every book:
CREATE TABLE book_chapter (isbn VARCHAR(13) REFERENCES book (id), chapter_number INT NOT NULL, chapter_title VARCHAR(255))
This code creates a one-to-many relationship between the parent table book and the child table book_chapter based on the isbn field. Once this table is created, you can only add a row to it if the ISBN you specify exists in book.
