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... :(

Solution 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.

Solution 2

KEY is just another word for INDEX.

A UNIQUE index means that all values within that index must be unique, and not the same as ant other within that index. An example would be an Id column in a table.

A PRIMARY KEY is a unique index where all key columns must be defined as NOT NULL, i.e, all values in the index must be set. Ideally, each table should have (and can have) one primary key only.

A FOREIGN KEY is a referential constraint between two tables. This column/index must have the same type and length as the referred column within the referred table. An example of a FOREIGN KEY is a userId, between a user-login table and a users table. Note that it usually points to a PRIMARY KEY in the referred table.

http://dev.mysql.com/doc/refman/5.1/en/create-table.html

Comments

[Prof]

Are you getting any error messages from your framework?

[Prof]

To be honest, if you're new to MySQL, you shouldn't use InnoDB unless you know why you want to use it... Use MyISAM by default, until you have a better understanding on storage engines

[Jake Chapman]

@Prof83 it says you can only use foreign keys with INNODB though.

[Prof]

Are you sure, i changed it to MyISAM and it worked, Possibly they mean CAN'T use foreign keys on InnoDB? I might have an old version :S

[Steven Moseley]

@JakeChapman - What's the problem you're having? I just tested your latest CREATE and it works fine for me.

[Steven Moseley]

@Prof83 - Nope, MyIsam does not support FKs - it will merely create an INDEX on columns you define a FK on. Only InnoDB supports FKs.

[BellevueBob]

Jake, it seems like your "edit" is asking a new question. It's not clear what "Still nothing" means. If one of these answers is good for your original question, please accept one and then ask another. Oh, and Go Seahawks!

[Steven Moseley]

@JakeChapman - Are you sure your users table is InnoDB? If it isn't, you won't be able to make a FK reference to it.

[Jake Chapman]

@bobDuell I'm trying to figure out the correct sql to create the foreign key. nothing seems to work. Both tables are now MyISAM.

[Prof]

did you try both tables being innodb?

[Steven Moseley]

@JakeChapman - Note my answer below. Foreign Keys require both tables to be InnoDB.

[Sinthia V]

AT HIS answer is correct,insofar as it goes. For the sake of my sense of correctness however, it doesn't answer the specific question. If you want to establish a foreign key constraint you must use InnoDB. MyIsam doesn't support them. You must also reference the primary key of an existing table. The foreign key column also needs to be the same type and size.