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 a primary key in MySQL?

Vaibhav Kothia May 26, 2023 0 Comments

A primary key in MySQL is a unique identifier for each row in a table. It is used to identify a particular row in a table and ensure that each row is distinct from all other rows. It is typically a single column, but can also be composed of multiple columns.

For example, if you have a table of customers, you could use the customer ID as the primary key. This way, each customer will have a unique ID that can be used to identify them.

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

Vaibhav Kothia May 26, 2023 0 Comments

A primary key is a unique identifier for a row in a table. It is used to uniquely identify each row and ensure data integrity. For example, an employee table might have an EmployeeID field as the primary key.

A foreign key is a column or set of columns in a table that references the primary key of another table. This is used to create relationships between tables. For example, an Order table might have a CustomerID foreign key that references the primary key of the Customer table.

What is a primary key in MySQL?

Vaibhav Kothia May 26, 2023 0 Comments

A primary key in MySQL is a column or group of columns that uniquely identifies each row in a table. It is a unique identifier which ensures that the data in a table is never duplicated. For example, a table of customers may have a primary key of customer_id, which is an auto-incrementing integer that is unique for each customer.

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.

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

Vaibhav Kothia May 26, 2023 0 Comments

A primary key is a unique identifier for a row in a table. It can be a single column or a combination of columns. For example, a table of students may have a primary key of student_id, which is a unique identifier for each student.

A foreign key is a field in a table that is used to link two tables together. It is a reference to the primary key of another table. For example, a table of students may have a foreign key of class_id, which is a reference to the primary key of the class table.