Creating Indices and Relationships

Jun 16, 2009 Author: vvaswani

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.

views 4850
  1. Add New Comment