mysql tutorial for beginners (7/8) : Foreign Keys (FK) & Joins
mysql tutorial for beginners (7/8) : Foreign Keys (FK) & Join
Foreign key relationships involve a parent table that holds the central data values, and a child table with identical values pointing back to its parent.
The FOREIGN KEY clause is specified in the child table.
The parent and child tables must use the same storage engine.
Corresponding columns in the foreign key and the referenced key must have similar data types. The size and sign of integer types must be the same.
Now, we can say: MySQL supports foreign keys, which let you cross-reference related data across tables, and foreign key constraints, which help keep this spread-out data consistent.
How Foreign keys help guarantee referential integrity?
For storage engines supporting foreign keys, MySQL rejects any INSERTor UPDATE operation that attempts to create a foreign key value in a child table if there is no a matching candidate key value in the parent table.
When an UPDATE or DELETE operation affects a key value in the parent table that has matching rows in the child table,
The result depends on the referential action specified using ON UPDATE and ON DELETE subclauses of the FOREIGN KEY clause.
MySQL supports 4 options regarding the action to be taken:
CASCADE: Delete or update the row from the parent table, and automatically delete or update the matching rows in the child table.
SET NULL: Delete or update the row from the parent table, and set the foreign key column or columns in the child table to NULL.
If you specify a SET NULL action, make sure that you have not declared the columns in the child table as NOT NULL.
RESTRICT: Rejects the delete or update operation for the parent table.
NO ACTION: A keyword from standard SQL.
In MySQL, equivalent to RESTRICT.
The MySQL Server rejects the delete or update operation for the parent table if there is a related foreign key value in the referenced table.
For an ON DELETE or ON UPDATE that is not specified, the default action is always RESTRICT.
FK constraint can be added using CREATE TABLE or ALTER TABLE statement
First off let’s create the parent tables,
I’ll begin with Tests table:
CREATE TABLE Tests
(
id_test INT NOT NULL AUTO_INCREMENT,
title VARCHAR(25) NOT NULL,
date DATE NOT NULL,
PRIMARY KEY(id_test)
);
And Questions table:
CREATE TABLE Questions
(
id_question INT NOT NULL AUTO_INCREMENT,
question TEXT NOT NULL,
answer TEXT NOT NULL,
PRIMARY KEY(id_question)
);
Now, let’s create the child table, Question_in_test:
Id_test column will be FK reference to the id_test in Tests table, don’t forget both columns should be the same data types, and same size,
id_question column will be FK reference to the id_question in Questions table,
CREATE TABLE Question_in_test
(
id_qt INT NOT NULL AUTO_INCREMENT,
number TINYINT NOT NULL,
value TINYINT NOT NULL,
id_test INT,
id_question INT,
FOREIGN KEY(id_test)
REFERENCES Tests(id_test)
ON DELETE RESTRICT
ON UPDATE CASCADE,
FOREIGN KEY(id_question)
REFERENCES Questions(id_question)
ON DELETE NO ACTION
ON UPDATE CASCADE,
PRIMARY KEY(id_qt)
);
The last child table is the Grade_per_question
This table contains 2 FKs
Id_student FK reference to the id_student in Students table,
And id_question per test reference to the id question per test in Question_in_test table,
ALTER TABLE grade_per_question ADD FOREIGN KEY(id_qt)
REFERENCES Question_in_test(id_qt)
ON DELETE NO ACTION
ON UPDATE CASCADE,
FOREIGN KEY(id_student)
REFERENCES Students(id_student) ON DELETE RESTRICT ON UPDATE CASCADE,
If you wish to specify the column on which to join two tables, use the JOIN ON construct:
Students table joined with grade_per_question on id_student
And grade_per_question joined with question_in_test on id_qt
Question_in_test joined with tests on id_test:
SELECT name,title,date,sum(grade)
FROM students
join grade_per_question on students.id_student = grade_per_question.id_student
join question_in_test on question_in_test.id_qt = grade_per_question.id_qt
join tests on tests.id_test = question_in_test.id_test
Whatever kind of data you are storing in your database, it must have some value to you,
therefore, it’s important that you keep backups, to protect your investment also, there will be times when you have to migrate your database over to a new server, in the next tutorial we will learn how to make backing up and restoring data in mysql.
Subscribe for more:
----------------------------------------------------------------------------
https://www.youtube.com/subscription_...
SWE.Safaa Al-Hayali - saf3al2a
Смотрите видео mysql tutorial for beginners (7/8) : Foreign Keys (FK) & Joins онлайн без регистрации, длительностью часов минут секунд в хорошем качестве. Это видео добавил пользователь Safaa Al-Hayali (Safaa Alaa) 01 Февраль 2016, не забудьте поделиться им ссылкой с друзьями и знакомыми, на нашем сайте его посмотрели 35,63 раз и оно понравилось 19 людям.