Primary Key, Composite Key, Foreign Key

 

1.Primary Key:

• Definition: A primary key is a unique identifier for each record (or row) in a table. It ensures that each row in a table can be uniquely identified and distinguishes it from all other rows in the table. By definition, a primary key must contain unique values and cannot contain NULL values.
• Purpose: The primary key serves as the main reference point for accessing and manipulating data within a table. It ensures data integrity by enforcing entity integrity (each row is uniquely identifiable) and can also be used as a reference in relationships between tables.
• Example: In a "Student" table, a primary key might be the "Student ID" field, which uniquely identifies each student record.

2.Composite Key:

• Definition: A composite key is a primary key that consists of multiple columns or fields. Unlike a single-column primary key, a composite key uses a combination of two or more columns to uniquely identify each row in a table.
• Purpose: Composite keys are used when a single column cannot uniquely identify each record, but a combination of multiple columns can. They are often employed in tables that represent many-to-many relationships or when natural keys (keys derived from real-world attributes) are used.
• Example: In a "Sales" table, a composite key might consist of both "Order ID" and "Product ID" columns, as a single order can contain multiple products, and a single product can appear in multiple orders.

3.Foreign Key:

• Definition: A foreign key is a column (or set of columns) in a table that establishes a link or relationship between two tables. It typically refers to the primary key of another table and enforces referential integrity.

• Purpose: Foreign keys are used to maintain consistency and integrity across related tables. They ensure that relationships between tables are valid by enforcing rules such as enforcing the existence of a related record (parent-child relationship) or ensuring that values in the foreign key column exist in the referenced table (referential integrity).
• Example: In a "Orders" table, a foreign key might be the "Customer ID" column, which refers to the primary key "Customer ID" in the "Customers" table, establishing a relationship between orders and customers.