A foreign key in MySQL is a field in a table that is used to link two tables together. It is used to ensure data integrity and to enforce referential integrity.
For example, if you have two tables, ‘Customers’ and ‘Orders’, you could use a foreign key in the ‘Orders’ table that references the ‘CustomerID’ field in the ‘Customers’ table. This would ensure that any order placed in the ‘Orders’ table is associated with an existing customer in the ‘Customers’ table.
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)
);
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.
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.
Vaibhav Kothia