MySQL. Links between tables. Foreign keys
In this lesson we will consider links between tables, types these links and foreign keys. Links between tables need for quickly getting data. Let’s imagine, you are in the shop and would like to buy fruits. If all fruits will be in one box you will need more time to search 10 apples, for example. If apples are in one box, oranges are in second box (and so on), it is more comfortable and quick to put fruits to your basket. The same way uses in tables.
Remember last lesson – we created table, which stored teams, players and countries. It wasn’t right way to organize data store. Getting data form such table is not comfortable. In this lesson we will allocate this data to three entities.
Code lesson
create database sport;
set names 'utf8';
use sport;
create table countries ( country_id int unsigned not null primary key auto_increment, country_name varchar(255) not null );
insert into countries (country_id, country_name) values (1, 'Россия'),(null, 'Англия'),(null, 'Испания'),(null, 'Италия'), (null, 'Германия'), (null, 'Франция');
SET NAMES utf8 COLLATE utf8_unicode_ci;
create table teams ( team_id int unsigned not null primary key auto_increment, country_id int unsigned not null, team_name varchar(255), foreign key(country_id) references countries(country_id));
insert into teams (team_id, country_id, team_name) values (1, 1, 'ЦСКА'), (null, 2, 'Манчестер Юнайтед'), (null, 3, 'Реал Мадрид'), (null, 4, 'Лацио'), (null, 5, 'Бавария'), (null, 6, 'ПСЖ');
create table players (player_id int unsigned not null primary key auto_increment, team_id int unsigned not null, country_id int unsigned not null, player_name varchar(255), foreign key(team_id) references teams(team_id), foreign key(country_id) references countries(country_id));
insert into players (player_id, team_id, country_id, player_name) values (1, 1, 1, 'Игорь Акинфеев'), (null, 3, 3, 'Криштиану Роналду'), (null, 6, 4, 'Марко Вератти');
delete from countries where country_id=3; /*Запрос завершится ошибкой, так как имеются связи между таблицами по внешнему ключу*/
0 Comments