Can you explain the difference between primary key and foreign key constraints?

A primary key is a constraint that uniquely identifies each row in a table. It is a column or set of columns that uniquely identifies each record in a table, and it must contain unique values. Primary keys ensure the integrity of the data in a table and are used as a reference by other tables that reference them. The primary key is always a unique index, and it can be used to enforce data integrity and ensure that each row is unique.

On the other hand, a foreign key is a constraint that is used to establish a relationship between two tables. It is a column or set of columns in one table that refers to a primary key in another table. A foreign key constraint ensures that the values in the column(s) of the referencing table must match the values in the referenced table. Foreign keys are used to enforce referential integrity, ensuring that data stays consistent across related tables.

Here’s an example to illustrate the difference between primary key and foreign key constraints.

Let’s say we have two tables, Customers and Orders. The Customers table has a primary key constraint on the CustomerID column, which means that every record in the Customers table has a unique value in the CustomerID column. The Orders table has a foreign key constraint on the CustomerID column, which means that the values in the CustomerID column of the Orders table must match the values in the CustomerID column of the Customers table.

CREATE TABLE Customers (
  CustomerID int PRIMARY KEY,
  FirstName varchar(50),
  LastName varchar(50),
  Email varchar(50)
)

CREATE TABLE Orders (
  OrderID int,
  OrderDate datetime,
  CustomerID int,
  FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
)

In this example, the primary key in the Customers table is the CustomerID column, and the foreign key in the Orders table is also the CustomerID column. This establishes a relationship between the two tables, where each order in the Orders table is associated with a customer in the Customers table.

Overall, primary key constraints are used to uniquely identify each row in a table, while foreign key constraints are used to establish relationships between tables by referencing the primary key of another table.