Primary Key in SGBM

Introduction

With focus on data integrity and effective recovery, this article offers a complete description of the primary keys in a database management system (SGBS). It covers types of primary keys, their creation and implementation and practical applications. The article also analyzes the best practices to choose and manage the primary keys, highlighting their advantages such as uniqueness, referential integrity, improvement of performance and simplified database design.

Overview

• Learn the role of the primary keys that play in maintaining data integrity and rapid data recovery.
• Recognizes scenarios where each type of primary key is appropriate.
• Create primary keys during the initial creation of the table.
• Maintain consistency and minimalism in the selection of primary key.
• Understands how primary keys simplify database design and enhance data security.

What is the main key in the SGB?

The main key is an essential component of an SGB that is necessary for the design and administration of relational databases. It guarantees the uniqueness of all records of a database, protecting the integrity of the data and allowing the rapid recovery of data.

A primary key is a column, or a set of columns, in a database table that exclusively identifies each row of that table. The primary key must contain unique values ​​and cannot contain null values.

Purpose of the primary key

• Uniqueness: Make the value of the primary key (or columns) column in a table to be unique in all rows.
• Integrity: guarantees the unique identity of all records, so to defend the integrity of the entity.
• Indexing: Often automatically indexed by the database system, which speeds up the performance of the query.
• Relationships: It serves as a point of reference for foreign keys in other tables, helping to establish relationships between tables.

Characteristics of the primary keys

• Unique values: Each value in primary key (s) should be unique.
• Not null: Primary key columns cannot contain null values.
• Immutable: The values ​​of a primary key should not change over time.
• Minimum: Composed of the lower number of columns needed to ensure uniqueness.

Types of primary keys

Let’s now explore in detail the types of primary keys and with an example.

One -column primary key

A single column key is the main key to a database table that has only one column. This column ensures that each entry in the table is different and that no two rows have the same value. The easiest type of primary key is a column key, which is often used when a record can be identified only by a feature.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(50),
    position VARCHAR(50)
);

Composite primary key

A composite primary key is a primary key in a database table that has two or more columns. Combining these columns, it is possible to identify each entry into the table individually. When a single column is insufficient to ensure the uniqueness of each record, the compound primary keys are used. When used in union tables and connection tables from many to many, this type of primary key is quite useful.

CREATE TABLE orders (
    order_id INT,
    product_id INT,
    PRIMARY KEY (order_id, product_id)
);

Creating a primary key

We have now learned to create a primary key.

During the creation of the table

You can specify a primary key on the Create table line when making a new table. This guarantees that as soon as the table is formed, the main key restriction is implemented.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100),
    birthdate DATE
);

By defining the main key during table creation, the database ensures from the beginning that each student has a single student_idwhich helps maintain data integrity and optimizes the performance of the query.

Adding to an existing table

If a table does not already contain a main key restriction, it can be added with the alter table instruction. This is useful when you realize that you need a main key restriction after the table was created and loaded with data.

ALTER TABLE students
ADD CONSTRAINT pk_student_id PRIMARY KEY (student_id);

The database guarantees that the Student_id column now causes the uniqueness and non -null values ​​to add the primary key restriction to an existing table. This may require verification and perhaps change the existing data to meet these restrictions.

Example

Consider a simple database with two tables: students and enrollments. Each student has a single student_id And you can register for various courses.

Table of Students

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100),
    birthdate DATE
);

Table of registration

CREATE TABLE enrollments (
    enrollment_id INT PRIMARY KEY,
    student_id INT,
    course_id INT,
    FOREIGN KEY (student_id) REFERENCES students(student_id)
);

Insert data into the student table

INSERT INTO students (student_id, name, birthdate) VALUES
(1, 'Alice Johnson', '2000-05-15'),
(2, 'Bob Smith', '1999-11-23'),
(3, 'Charlie Brown', '2001-02-10');

Insert data into the registration table

INSERT INTO enrollments (enrollment_id, student_id, course_id) VALUES
(101, 1, 101),
(102, 2, 101),
(103, 1, 102),
(104, 3, 102);

Data recovery

Now, we will recover data from both tables to see the output:

Check to retrieve data

SELECT * FROM students;

Departure:

+------------+---------------+------------+
| student_id | name          | birthdate  |
+------------+---------------+------------+
| 1          | Alice Johnson | 2000-05-15 |
| 2          | Bob Smith     | 1999-11-23 |
| 3          | Charlie Brown | 2001-02-10 |
+------------+---------------+------------+

See to retrieve data from the Table of Registration

SELECT * FROM enrollments;

Departure:

+---------------+------------+-----------+
| enrollment_id | student_id | course_id |
+---------------+------------+-----------+
| 101           | 1          | 101       |
| 102           | 2          | 101       |
| 103           | 1          | 102       |
| 104           | 3          | 102       |
+---------------+------------+-----------+

Best practices

• Choose natural keys when possible: Use the main key if there is a single attribute that occurs naturally (such as the Social Security number or ISBN for publications).
• Use subrogated keys when needed: Use a subrogated key (as an integer with self-inclement) if there is no natural key.
• Coherence: Keep the names of the constant primary keys on all tables.
• Normalization: To prevent redundancy and dependency problems, make sure the database is properly standardized.

Advantages of the primary key in SGBM

Here are the key advantages of using primary keys in a database management system, summarized at simple points:

• Uniqueness
  • Ensures that each record is identifiable exclusively.
  • Prevents duplicate records.
• EFFICIENT DATA RECOVERY
  • Automatically indexed by the SGBM.
  • Speeds up search queries.
• Supports referential integrity
  • Used to establish relationships between tables.
  • Maintains the integrity of the data through foreign keys.
• Prevents null values
  • Primary keys cannot contain null values.
  • Ensures that all records have a valid identifier.
• Integrity and Consistency of Data
  • Comply with unique and not null restrictions.
  • Keep data consisting of the database.
• Simplify Data Base Design
  • It offers a clear structure for the database.
  • Easy to understand and manage the database scheme.
• Improves performance
  • Optimizes queries that filter, attach or order data.
  • Improves overall database performance.
• Supports data integrity into distributed systems
  • Ensures a unique identification in different nodes.
  • Useful in distributed databases.
• Facilitates updates and eliminations
  • Efficient and simple to update or delete records.
  • Avoid ambiguity in data modifications.
• Allow data consistency between transactions
  • Ensure transactions consisting of transactions.
  • Important for transactional integrity.
• Offers a base for normalization
  • Essential to reduce redundancy.
  • Help in the organization of data logically.
• Improve data security
  • Improves access control mechanisms.
  • Ensure only authorized modifications.

Conclusion

In the relational database architecture, the primary keys play a critical role in guaranteeing data integrity, uniqueness and fast recovery. To ensure consistency between records, they are defined during the construction or updating of tables. Improves the speed of the database and maintain referential integrity. Following the best practices results in a more robust and reliable SGBS by rationalizing database design, increasing performance and strengthening data security.

Frequent questions