What is a Primary Key Foreign Key and Composite Key?

Question

Puneet Kankar · Answer

Primary Key A primary key uniquely identifies each record in a database table. Any individual key that does this can be called a candidate key but only one can be chosen by database engineers as a primary key. Composite Key The composite key which is composed of two or more attributes that collectively uniquely identify each record. An example would be a list of homes on a real estate market. In a well-ordered database there should be a primary key that uniquely identifies each record. Foreign Key if there is a key in a linked table such as a buyer’s table that references the primary key that will be a foreign key. While a primary key and a composite key might do the same things the primary key will consist of one column where the composite key will consist of two or more columns. The relationship between a primary key and a foreign key is quite different. The key thing to understand here is that the primary key in one database table becomes a foreign key in another database table and vice versa. A foreign key in a database table is taken from some other table and applied in order to link database records back to that foreign table.

Tuhin Paul · Answer

1. Primary Key A Primary Key is a column (or a set of columns) in a database table that uniquely identifies each row in the table. It ensures that no two rows have the same value in the primary key column(s) and it cannot contain NULL values. Key Characteristics: Uniqueness: Every value in the primary key column(s) must be unique. Non-Null: The primary key column(s) cannot have NULL values. Single or Composite: A primary key can consist of a single column or multiple columns (composite key). CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY -- Single-column primary key FirstName VARCHAR(50) LastName VARCHAR(50) ); Here EmployeeID is the primary key ensuring that each employee has a unique identifier. 2. Foreign Key A Foreign Key is a column (or a set of columns) in one table that refers to the Primary Key of another table. It establishes a relationship between two tables enforcing referential integrity. Key Characteristics: Referential Integrity: Ensures that the value in the foreign key column must exist in the referenced primary key column. Optional or Mandatory: Foreign key columns can allow NULL values unless explicitly restricted. One-to-One One-to-Many Many-to-Many: Foreign keys are used to define relationships between tables. CREATE TABLE Departments ( DepartmentID INT PRIMARY KEY DepartmentName VARCHAR(100) ); CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY FirstName VARCHAR(50) LastName VARCHAR(50) DepartmentID INT FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID) ); Here DepartmentID in the Employees table is a foreign key that references the DepartmentID primary key in the Departments table. 3. Composite Key A Composite Key is a combination of two or more columns in a table that together uniquely identify each row. It is used when no single column can uniquely identify a record. Key Characteristics: Multiple Columns: A composite key consists of two or more columns. Uniqueness: The combination of values in the composite key columns must be unique. No Single Column Uniqueness: Individual columns in a composite key may not be unique on their own. CREATE TABLE CourseEnrollments ( StudentID INT CourseID INT EnrollmentDate DATE PRIMARY KEY (StudentID CourseID) -- Composite key ); Here the combination of StudentID and CourseID uniquely identifies each enrollment. Neither StudentID nor CourseID alone is sufficient to ensure uniqueness. Primary Key: Think of a Social Security Number (SSN) for individuals. Each person has a unique SSN. Foreign Key: Think of a company ID card that references your SSN in the government database. Composite Key: Think of a university enrollment system where a student's ID and course ID together uniquely identify their enrollment in a specific course.

Jaish Mathews · Answer

Here's a tabular comparison of Primary Key Foreign Key and Composite Key : Feature Primary Key Foreign Key Composite Key Definition A unique identifier for a record in a table. A key in one table that refers to the Primary Key of another table. A combination of two or more columns used as a unique identifier for a record. Uniqueness Must be unique for each row. Can have duplicate values in the referencing table. The combination of values must be unique but individual columns may have duplicates. Null Values Cannot be NULL. Can have NULL values if not a mandatory relationship. Cannot have NULL values in any part of the key. Number of Keys One per table. Can have multiple Foreign Keys in a table. One per table but consists of multiple columns. Relation with Other Tables Defines the unique identity of a row in its table. Establishes a relationship between two tables. Ensures uniqueness when a single column cannot do so. Indexing Automatically indexed. Not automatically indexed (but can be indexed manually). Automatically indexed if declared as a Primary Key. Example EmployeeID in an Employees table. DepartmentID in an Employees table referencing DepartmentID in a Departments table. ( OrderID  ProductID ) in an OrderDetails table.

What is a Primary Key, Foreign Key, and Composite Key?