Primary Key, Unique Key, Key, Foreign Key

A primary key is a column or multiple columns that uniquely identify each row in a table.
Basic key of primary key:
– This must contain unique values
– For multiple columns, the combination of values must be unique
– Primary key is not null
– Only one primary key will be present in a table

As MySQL works faster with integer, the primary key data type shoule be INT or similar like this i.e. BIGINT, TINYINT etc. For a unique value you can use AUTO_INCREMENT in this field.

Here is an example:

CREATE TABLE tbl(
 column_id INT AUTO_INCREMENT PRIMARY KEY,
 column_name VARCHAR(100),
 column_type VARCHAR(20)
 );

For multiple column:

CREATE TABLE tbl(
 column_id INT NOT NULL,
 column_uid NOT NULL,
 column_name VARCHAR(100),
 column_type VARCHAR(20),
 PRIMARY KEY(column_id,column_uid)
 );

A UNIQUE key creates a constraint for a column whose value must be unique. UNIQUE key allows NULL values.
For example, in a user registration table userName and userEmail must be unique.

KEY is alias for INDEX.

Foreign key is a field which matches the other field of another table.
Suppose on user table there is a useID. Also there is an another table called userOrder where each user’s order is inserted. So in this table there is also a column user_id which store the value of userID.

So the user_id field in the userOrder table relates to the userID primary key field in user table.