Keys in a Database Management System (DBMS) are crucial for establishing relationships between tables and ensuring data integrity. They act as unique identifiers and constraints within the database structure.
Keys in DBMS Explained
Keys are attributes or sets of attributes that help you uniquely identify a tuple (row) in a relation (table). They are fundamental in designing relational databases.
Types of Keys in DBMS
Here's a breakdown of the different types of keys:
| Key Type | Description | Example |
|---|---|---|
| Primary Key | Uniquely identifies each record in a table. A table can only have one primary key. According to the provided reference, a primary key is a column or a group of columns in a table that uniquely identifies the rows of data in that table. | CustomerID in a Customers table. |
| Candidate Key | An attribute or set of attributes that can uniquely identify a tuple in a relation. A table can have multiple candidate keys. From these, one is selected as the primary key. | CustomerID, PassportNumber in a Customers table. |
| Super Key | A set of attributes that uniquely identifies a tuple in a relation. It is a superset of a candidate key. | {CustomerID, Name}, {PassportNumber, Address} in a Customers table. |
| Foreign Key | A column (or columns) in one table that refers to the primary key in another table. Establishes a link between two tables. | CustomerID in an Orders table referencing the Customers table. |
| Alternate Key | Candidate keys that are not selected as the primary key are called alternate keys. | If PassportNumber is a candidate key, and CustomerID is the primary key, PassportNumber becomes the alternate key. |
| Surrogate Key | An artificially created key to uniquely identify records, especially when a natural primary key is not available or suitable. | An auto-incrementing ID column. |
| Composite Key | A primary key composed of two or more attributes that uniquely identify a record. | {OrderID, ProductID} in an OrderItems table. |
| Compound Key | A key that uses multiple attributes to uniquely identify a record. Similar to a composite key, but the attributes might not be primary keys themselves. Often used when referring to keys involving foreign keys. | {OrderID, ProductID} in an OrderItems table where both OrderID and ProductID are foreign keys referencing other tables. |
