Foreign Keys and Relationships
Foreign keys are essential to relational database design because they create meaningful connections between tables. A foreign key is a column, or set of columns, in one table that refers to the primary key of another table. This relationship enables you to link related data across tables, such as associating a student's enrollment with their record in the students table. By defining foreign keys, you ensure that the data stored in different tables remains logically connected, supporting complex queries and reports that draw from multiple sources.
CREATE TABLE enrollments (
enrollment_id INT PRIMARY KEY,
student_id INT,
course_id INT,
enrollment_date DATE,
grade CHAR(2),
FOREIGN KEY (student_id) REFERENCES students(student_id)
);
Foreign keys play a critical role in enforcing valid relationships between tables. When you define a foreign key, the database ensures that every value in the foreign key column matches an existing value in the referenced primary key column. This prevents the creation of orphaned records—rows in a child table that refer to non-existent rows in the parent table. As a result, foreign keys help maintain data consistency and integrity, so that every enrollment always corresponds to a valid student.
123-- This statement will fail if there is no student with student_id = 999 INSERT INTO enrollments (enrollment_id, student_id, course_id, enrollment_date, grade) VALUES (2001, 999, 101, '2023-09-10', 'A');
1. What is the main function of a foreign key?
2. How do foreign keys help maintain data integrity?
3. What would happen if you tried to delete a student who is referenced in the enrollments table?
Tack för dina kommentarer!
Fråga AI
Fråga AI
Fråga vad du vill eller prova någon av de föreslagna frågorna för att starta vårt samtal
Fantastiskt!
Completion betyg förbättrat till 5.56Foreign Keys and Relationships
Svep för att visa menyn
Foreign keys are essential to relational database design because they create meaningful connections between tables. A foreign key is a column, or set of columns, in one table that refers to the primary key of another table. This relationship enables you to link related data across tables, such as associating a student's enrollment with their record in the students table. By defining foreign keys, you ensure that the data stored in different tables remains logically connected, supporting complex queries and reports that draw from multiple sources.
CREATE TABLE enrollments (
enrollment_id INT PRIMARY KEY,
student_id INT,
course_id INT,
enrollment_date DATE,
grade CHAR(2),
FOREIGN KEY (student_id) REFERENCES students(student_id)
);
Foreign keys play a critical role in enforcing valid relationships between tables. When you define a foreign key, the database ensures that every value in the foreign key column matches an existing value in the referenced primary key column. This prevents the creation of orphaned records—rows in a child table that refer to non-existent rows in the parent table. As a result, foreign keys help maintain data consistency and integrity, so that every enrollment always corresponds to a valid student.
123-- This statement will fail if there is no student with student_id = 999 INSERT INTO enrollments (enrollment_id, student_id, course_id, enrollment_date, grade) VALUES (2001, 999, 101, '2023-09-10', 'A');
1. What is the main function of a foreign key?
2. How do foreign keys help maintain data integrity?
3. What would happen if you tried to delete a student who is referenced in the enrollments table?
Tack för dina kommentarer!
