What are the different types of keys in MySQL?

Vaibhav Kothia May 26, 2023 0 Comments

1. Primary Key: A primary key is a field in a table which uniquely identifies each row/record in a database table. Primary keys must contain unique values. For example:

CREATE TABLE Persons (
PersonID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (PersonID)
);

2. Unique Key: A unique key is a set of one or more than one fields/columns of a table that uniquely identify a record in a database table. Unique keys can accept only one null value and it cannot have duplicate values. For example:

CREATE TABLE Persons (
PersonID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
EmailID varchar(255) UNIQUE,
PRIMARY KEY (PersonID)
);

3. Foreign Key: A foreign key is a field in a table that is used to establish a link between two tables. It is used to reference the primary key of another table. For example:

CREATE TABLE Orders (
OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);

4. Composite Key: A composite key is a combination of two or more columns in a table that can be used to uniquely identify each row in the table. For example:

CREATE TABLE Orders (
OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int NOT NULL,
ProductID int NOT NULL,
PRIMARY KEY (OrderID, ProductID),
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);

What is the difference between a primary key and a unique key?

Vaibhav Kothia May 26, 2023 0 Comments

A primary key is a special type of unique key that is used to identify a single row in a table. It is usually composed of one or more columns that contain only unique values, and cannot be NULL. For example, a table of employees may have an Employee ID column as its primary key.

A unique key is any combination of columns that contains only unique values. It is used to enforce data integrity and can be composed of one or more columns. For example, a table of customers may have a combination of first and last name columns as its unique key, ensuring that no two customers have the same name.

What are the differences between a primary key and a unique key?

Vaibhav Kothia May 26, 2023 0 Comments

A primary key is a field in a table that uniquely identifies each record in the table. It is a combination of a unique index and a not null constraint. For example, a customer table may have a primary key of customer_id.

A unique key is a field in a table that uniquely identifies each record in the table. It does not have to be the primary key, but it must contain unique values. For example, a customer table may have a unique key of email address.