MySQL: what's the difference between INDEX, UNIQUE, FOREIGN KEY, and PRIMARY KEY?

Question

Ok, so i'm a newbie here at SQL..

I'm settings up my tables, and i'm getting confused on indexes, keys, foreign keys..

I have a users table, and a projects table.

I want to use the users (id) to attach a project to a user.

This is what I have so far:

DROP TABLE IF EXISTS projects;

CREATE TABLE projects (
    id int(8) unsigned NOT NULL,
    user_id int(8),
    name varchar(120) NOT NULL,
    description varchar(300),
    created_at date,
    updated_at date,
    PRIMARY KEY (id),
    KEY users_id (user_id)
) ENGINE=InnoDB;

ALTER TABLE projects (
    ADD CONSTRAINT user_projects,
    FOREIGN KEY (user_id) REFERENCES users(id),
    ON DELETE CASCADE
)

So what I'm getting lost on is what is the differences between a key, an index, a constraint and a foreign key?

I've been looking online and can't find a newbie explanation for it.

PS. I'm using phpactiverecord and have the relationships set up in the models

user-> has_many('projects');

projects -> belongs_to('user');

Not sure if that has anything to do with it, but thought i'd throw it in there..

Thanks.

EDIT:

I thought it could possible be something to do with Navicat, so I went into WampServer -> phpMyAdmin and ran this...

DROP TABLE IF EXISTS projects;

CREATE TABLE projects (
    id int(8) unsigned NOT NULL,
    user_id int(8) NOT NULL,
    name varchar(120) NOT NULL,
    description varchar(300),
    created_at date,
    updated_at date,
    PRIMARY KEY (id),
    KEY users_id (user_id),
    FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB;

Still nothing... :(

Answer 1

Expanding on Shamil's answers:

INDEX is similar to the index at the back of a book. It provides a simplified look-up for the data in that column so that searches on it are faster. Fun details: MyISAM uses a hashtable to store indexes, which keys the data, but is still linearly proportional in depth to the table size. InnoDB uses a B-tree structure for its indexes. A B-tree is similar to a nested set - it breaks down the data into logical child groups, meaning search depth is significantly smaller. As such, lookups by ranges are faster in a InnoDB, whereas lookups of a single key are faster in MyISAM (try to remember the Big O of hashtables and binary trees).

UNIQUE INDEX is an index in which each row in the database must have a unique value for that column or group of columns. This is useful for preventing duplication, e.g. for an email column in a users table where you want only one account per email address. Important note that in MySQL, an INSERT... ON DUPLICATE KEY UPDATE statement will execute the update if it finds a duplicate unique index match, even if it's not your primary key. This is a pitfall to be aware of when using INSERT... UPDATE statements on tables with uniques. You may wind up unintentionally overwriting records! Another note about Uniques in MySQL - per the ANSI-92 standard, NULL values are not to be considered unique, which means you can have multiple NULL values in a nullable unique-indexed column. Although it's a standard, some other RDBMSes differ on implementation of this.

PRIMARY KEY is a UNIQUE INDEX that is the identifier for any given row in the table. As such, it must not be null, and is saved as a clustered index. Clustered means that the data is written to your filesystem in ascending order on the PK. This makes searches on primary key significantly faster than any other index type (as in MySQL, only the PK may be your clustered index). Note that clustering also causes concerns with INSERT statements if your data is not AUTO_INCREMENTed, as MySQL will have to shift data around on the filesystem if you insert a new row with a PK with a lower ordinal value. This could hamper your DB performance. So unless you're certain you know what you're doing, always use an auto-incremented value for your PK in MySQL.

FOREIGN KEY is a reference to a column in another table. It enforces Referential Integrity, which means that you cannot create an entry in a column which has a foreign key to another table if the entered value does not exist in the referenced table. In MySQL, a FOREIGN KEY does not improve search performance. It also requires that both tables in the key definition use the InnoDB engine, and have the same data type, character set, and collation.