Terminology in DBMS

Table of Contents hide

A Primary key is a unique value in a column/field for a record in a table.
It serves the data integrity and efficient data retrieval.
Each value within the primary key column/field must be unique.
No two/more records can have the same primary key value.
Primary key columns cannot contain null values.
The primary key column always contains not null values or must contain a unique value.
In other words, the Primary keys concept enforces uniqueness by rejecting attempts to insert duplicate key values into the table’s column.
Primary keys are often automatically indexed by the database system.
Primary keys provide a reliable way to identify individual records within a table.
Primary keys are often used to establish relationships between tables in a relational database via the foreign keys concept.
Types of Primary Keys: Two types-
1. Single-Column Primary Key: A primary key is composed of a single column in the table having a unique column to identify a record.
2. Composite Primary Key: A primary key that consists of two or more columns combined to uniquely identify a record. This is useful when no single column can uniquely identify a record.

Secondary keys in a database system refer to indexes created on columns other than the primary key column(s) to improve query performance and facilitate efficient or speed-up data retrieval for commonly used queries.
They are also known as non-clustered indexes in some database systems.
They allow faster access to data by creating a separate index structure/columns that point to the actual data rows in the table.
Unlike primary keys, secondary keys may contain duplicate values because they are not required to enforce uniqueness.
Secondary keys are particularly beneficial for improving the performance of search operations, joins, and sorting tasks.
Secondary keys can be created manually using SQL commands (CREATE INDEX) or through database management tools.
They need to be maintained and periodically updated to reflect changes in data at regular intervals to ensure optimal performance.

A foreign key is a column or a set of columns within a table that establishes a link or a relationship between two tables using the primary key of one table.
It creates a logical association between rows in different tables based on the values of those columns.
Foreign keys ensure referential integrity, i.e., values in the referencing column (or columns) [2nd or child table] must exist in the referenced column (usually the primary key) of the parent/first table, preventing orphaned or inconsistent data.
Foreign keys can define different types of relationships, including one-to-one, one-to-many, or many-to-many relationships between tables.
Database systems enforce foreign key constraints to maintain data consistency. They restrict operations that would violate referential integrity, such as inserting a value that doesn’t exist in the referenced table.
Foreign keys play a vital role in relational databases by maintaining data consistency and integrity through relationships between tables, ensuring accuracy and reliability in complex data structures.

Records are organized forms of data within tables and are composed of individual data values associated with each field/column/attribute.
Each row of a table represents a record in the database.
Each record in a database table represents a specific entity or instance, such as a customer, product, employee, teacher, student, etc.
Records are stored as rows within a database table, where each column represents a specific attribute of the entity.
Records are the fundamental building blocks of databases, organizing and storing data in a structured manner.
They represent real-world entities, and their effective management is critical for accurate data storage, retrieval, and analysis.
Records in different tables can be related/linked through the foreign keys concept, establishing connections between entities.

Related Posts