In database management systems (DBMS), keys are used to uniquely identify records within a table. They ensure data integrity and provide a way to establish relationships between tables. Here are the commonly used keys in DBMS:
- Primary Key: A primary key is a unique identifier for each record in a table. It ensures that each row in a table can be uniquely identified. Primary keys can be composed of one or more columns, and their values cannot be null (empty).
- Foreign Key: A foreign key is a field or a set of fields in one table that refers to the primary key in another table. It establishes a relationship between the two tables, enforcing referential integrity. The foreign key in one table references the primary key in another table.
- Candidate Key: A candidate key is a minimal set of attributes (columns) that can uniquely identify a tuple (record) in a table. It means that no subset of the candidate key can have the same uniqueness property. A table can have multiple candidate keys.
- Composite Key: A composite key is a key that consists of two or more attributes (columns) to uniquely identify a record in a table. The combination of these attributes creates a unique identifier.
- Super Key: A super key is a set of one or more attributes (columns) that can uniquely identify a record in a table. It may include additional attributes that are not necessary for uniqueness.
- Alternate Key: An alternate key is a candidate key that is not selected as the primary key. It can be used to uniquely identify records, but it is not the preferred key for identifying records in a table.
- Surrogate Key: A surrogate key is an artificially generated key used as a substitute for the natural primary key. It is typically an auto-incremented or generated value that has no meaningful information but provides a unique identifier for each record.
These keys play crucial roles in maintaining data integrity, establishing relationships, and optimizing database operations in a DBMS.