Como Crear RELACIONES en ACCESS 2013, 2016, 2019 ➡️ Relacionar Tablas Correctamente ✅

Summary

This tutorial explains how to create and manage relationships between tables in Microsoft Access. It covers adding tables to the relationships window, defining primary and foreign keys, enforcing referential integrity, and updating or cascading deletions. The video also demonstrates how to create a new 'Notas' (grades) table and establish relationships with student and subject tables.

Highlights

Introduction to Table Relationships00:00:00

This video continues the Access database tutorial, focusing on creating relationships between tables. The process begins by navigating to 'Database Tools' and then 'Relationships' to add the desired tables (e.g., 'Estudiantes', 'Asignaturas', 'Profesores') to the graphical view.

Understanding Keys and Data Types for Relationships00:01:02

Table relationships involve connecting a primary key (e.g., 'ID Profesor' in 'Profesores') to a foreign key in another table (e.g., 'ID Profesor' in 'Asignaturas'). The foreign key must be of the same data type as the primary key. If a table, like 'Asignaturas', needs to relate to 'Profesores', an 'ID Profesor' field must be added to 'Asignaturas' with a 'Number' data type, as the primary 'ID Profesor' in 'Profesores' would be an autonumber type that equates to a number.

Creating a Relationship and Enforcing Referential Integrity00:02:50

To create a relationship, drag the primary key ('ID Profesor' from 'Profesores') to the foreign key ('ID Profesor' in 'Asignaturas'). It's crucial to check 'Enforce Referential Integrity' to ensure that only existing professor IDs can be used in the 'Asignaturas' table. This prevents data inconsistencies. Also, 'Cascade Update Related Fields' and 'Cascade Delete Related Records' are selected to automatically update or delete related data across tables.

Testing the Relationship00:06:38

The newly created relationship can be tested by entering data. For instance, if an 'ID Profesor' of '2' is entered in 'Asignaturas' but doesn't exist in 'Profesores', Access will prevent the entry, enforcing referential integrity. Only valid, existing professor IDs can be assigned to subjects.

Modifying and Deleting Relationships00:07:50

Relationships can be modified or deleted by right-clicking on the relationship line. Modifying allows changing parameters, while deleting removes the connection between tables. The video then proceeds to create a new table.

Creating the 'Notas' (Grades) Table00:08:18

A new table named 'Notas' is created. It includes fields for 'ID Nota' (Autonumber, Primary Key), 'Calificacion' (Number, Decimal for grades with decimals), and 'Fecha del Examen' (Date/Time with short date format). Importantly, it also includes foreign key fields 'ID Estudiante' (Number) and 'ID Asignatura' (Number) to link to the 'Estudiantes' and 'Asignaturas' tables, respectively.

Relating the 'Notas' Table00:11:09

The 'Notas' table is added to the relationships window. To avoid errors, all other open tables must be closed. The 'ID Estudiantes' from the 'Estudiantes' table is dragged to 'ID Estudiante' in 'Notas', and similarly, 'ID Asignatura' from 'Asignaturas' is dragged to 'ID Asignatura' in 'Notas'. Referential integrity and cascade options are selected for both. This creates 'one-to-many' relationships, indicating that one student can have many grades and one subject can have many grades.

Finalizing and Saving Relationships00:13:20

After establishing all relationships, the 'ID Alumno' field in 'Notas' is corrected to 'ID Estudiante' for consistency. The entire relationship layout is then saved by right-clicking and selecting 'Save'. Reopening the relationships window confirms that all established connections are present, ensuring a well-structured and logical database.