What is the difference between an Index and a Foreign Key?

Question

I want to create a database with 3 tables. One for posts and one for tags and one that links posts to tags with the post_id and tag_id functioning as foreign key references.

Can you explain what an Index would be in this scenario and how it differs from a Foreign Key and how that impacts my database design?

Answer 1

an index on a table is a data structure that makes random access to the rows fast and efficient. It helps to optimize the internal organization of a table as well.

A foreign key is simply a pointer to a corresponding column in another table that forms a referential constraint between the two tables.

Answer 2

An index is added as a fast look up for data in the table. An index can have constraints, in that the column or columns that are used to make the index might have to be unique (unique: only one row in the database is returned for that index, or non-unique: multiple rows can be returned). The primary key for the table is a unique index, and usually only has one column.

A foreign key is a value in a table that references a unique index in another table. It is used as a way to relate to tables together. For example, a child table can look up the one parent row via its column that is a unique index in the parent table.

Answer 3

You'll have foreign keys in the third table. Indexes are not necessary, you need them if you have lots of data where you want to find something by Id quickly. Maybe you'll want an index on posts primary key, but DBMS will probably create it automatically.

Index is a redundant data structure which speeds up some queries. Foreign key, for practical matters, is a way to make sure that you have no invalid pointers between the rows in your tables (in your case, from the relationship table to posts and tags)

Answer 4

Question: Can you explain what an Index would be in this scenario and how it differs from a Foreign Key and how that impacts my database design?

Your foreign keys in this case are the two columns in your Posts_Tags table. With a foreign key, Each foreign key column must contain a value from the main table it is referencing. In this case, the Posts and Tags tables.

Posts_Tags->PostID must be a value contained in Posts->PostID

Posts_Tags->TagID must be a value contained in Tags->TagID

Think of an index as a column that has been given increased speed and efficiency for querying/searching values from it, at the cost of increased size of your database. Generally, primary keys are indexes, and other columns that require querying/searching on your website, in your case, probably the name of a post (Posts->PostName)

In your case, indexes will have little impact on your design (they are nice to have for speed and efficiency), but your foreign keys are very important to avoid data corruption (having values in them that don't match a post and/or tag).

Answer 5

You describe a very common database construct; it's called a "many-to-many relation".

Indexes shouldn't impact this schema at all. In fact, indexes shouldn't impact any schema. Indexes are a trade-off between space and time: indexes specify that you're willing to use extra storage space, in exchange for faster searches through the database.

Wikipedia has an excellent article about what database indexes are: Index (database)

Answer 6

To use foreign keys in mysql, you need to create indexes on both tables. For example, if you want the field a_id on table b to reference the id field on the table a, you have to create indexes on both a.id and b.a_id before you can create the reference.

Update: here you can read more about it: http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html