What are primary and foreign keys, and what constraints do they have?

Primary Key — this is a column or a set of columns in an entity (database table) that uniquely identifies each record in that entity.

• Constraints:
  • Uniqueness: The primary key values must be unique within the table.
  • Not NULL: The primary key cannot contain NULL values.

Foreign Key — this is a column or a set of columns in one entity (child table) that references the primary key in another entity (parent table). It establishes a relationship between two tables.

• Constraints:
  • Referential integrity: The foreign key values must either match an existing primary key value in the parent table or be NULL (if allowed).

SQL Example:

// Creating a table with a primary key
CREATE TABLE Users (
    UserID INT PRIMARY KEY, // UserID - primary key
    UserName VARCHAR(255)
);

// Creating a table with a foreign key
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    UserID INT,
    OrderDate DATE,
    FOREIGN KEY (UserID) REFERENCES Users(UserID) // UserID - foreign key referencing Users.UserID
);