Keys

MySQL organize the data (records) surround keys.

Keys are: Primary Key, Constrains key, Foreign key, Key Index.

All of above keys is very important in building up data structure.

In this section I will introduce about Primary Key, Constrains key, and Foreign key. The Index Key will be introduce in special section because of it's important.

Primary Key

A primary key is a column or a set of columns that uniquely identifies each row in the table:

• A primary key must contain unique values. If the primary key consists of multiple columns, the combination of values in these columns must be unique.
• A primary key column cannot contain NULL values. It means that you have to declare the primary key column with the NOT NULL attribute. If you don’t, MySQL will force the primary key column as NOT NULL implicitly.
• One table can contain only one primary key.
• MySQL work faster with integer so primary key often made of columns with integer type. If primary key is single column and content type is integer, it often goes with option AUTO_INCREMENT.
• InnoDB will create a implicit primary key for your table if you do not explicit primary key for your table.

Primary key can be created with table definition or adding/updating/deleting after table created.

Primary key's also used as index key.

Constrains Key (Unique Key)

A UNIQUE KEY creates a constraint for a column whose values must be unique. Unlike the PRIMARY KEY, MySQL allows NULL values in the UNIQUE KEY. A table can also have multiple UNIQUE KEY.

Unique key's also used as index key.

Foreign Key

Foreign key is used to define how data integrity is enforced between two tables (e.g., when a table row is deleted or updated).

MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist.

Foreign key is useful when join two table, it allows access via index instead of table scan. So, the query will be process faster.

The Foreign key only can create after reference table existed.