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.