Изучаем SQL. №15. Внешние ключи и связи между таблицами. Один к одному.

Изучаем SQL
Естественно, что все в одну таблицу не запихнуть. Да и зачем создавать монстра, которого потом не обуздать. При нормализации любая большая таблица распадается на меньшие, с которыми удобней работать в дальнейшем.
ТРИ ТИПА СВЯЗЕЙ МЕЖДУ ТАБЛИЦАМИ
1)Один к одному.
2)Один ко многим.
3)Многие ко многим.

ОДИН К ОДНОМУ:
Одна запись в одной таблице может быть связана с одной записью в другой таблице.
Попробуем создать две таблицы, одна и з которых будет содержать ссылку на вторую, так называемый внешний ключ. задается он таким способом
FOREIGN KEY (переменная ссылка) REFERENCES вторая_таблица(поле_на_на_которое_ссылаются);

Например, создадим базу данных office в ней две таблицы users и adresses , то есть, список пользователей и их адреса. Приступим.

CREATE DATABASE office;
USE office;
CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20));
CREATE TABLE adresses (user_id INT PRIMARY KEY NOT NULL,city VARCHAR(50), FOREIGN KEY(user_id) REFERENCES users(id));

И наполняем их по очереди

INSERT INTO users VALUES (null,'Степан');
INSERT INTO users VALUES (null,'Иван');
INSERT INTO users VALUES (null,'Виталий');
INSERT INTO adresses VALUES (1,'Киев');
INSERT INTO adresses VALUES (2,'Полтава');
INSERT INTO adresses VALUES (3,'Днепропетровск');

Забегая вперед покажу вам один из способов выборки, чисто, чтобы проверить результат.

SELECT u.name,a.city FROM users u LEFT JOIN adresses a ON u.id = a.user_id;
+---------+----------------+
| name    | city           |
+---------+----------------+
| Степан  | Киев           |
| Иван    | Полтава        |
| Виталий | Днепропетровск |
+---------+----------------+

7 идей о “Изучаем SQL. №15. Внешние ключи и связи между таблицами. Один к одному.

  1. Wolodymyr

    Виталий, 15 урока нет на странице «Изучаем SQL. все уроки», добавьте pls в список.

  2. Wolodymyr

    Виталий, немного критики (извините чайника за это, но хотел бы докопаться до истины 🙂
    прокнопал этот урок… всё вроде хорошо, но:
    -Я живу например в Киеве. Зовут меня Владимир. Хочу об этом сообщить в данной базе.
    mysql> insert into users values(null, ‘Владимир’);
    mysql> insert into adresses value(4, ‘Киев’);
    Имеем:

    mysql> select * from users;
    +—-+——————+
    | id | name |
    +—-+——————+
    | 1 | Степан |
    | 2 | Иван |
    | 3 | Виталий |
    | 4 | Владимир |
    +—-+——————+

    mysql> select * from adresses;
    +———+——————————+
    | user_id | city |
    +———+——————————+
    | 1 | Киев |
    | 2 | Полтава |
    | 3 | Днепропетровск |
    | 4 | Киев |
    +———+——————————+

    И вот здесь получается, что в таблице adresses могут быть
    одни и те же «города»-адреса записаны много-много-много раз, чего не должно быть.
    Как бы не совсем правильно будет судя по теории.
    Насколько я понял, одному городу должна соответствовать одна запись (в данном случае).
    А вот в users куча народа может быть связана с этим одним городом
    (ну всё как в жизни же должно быть 🙂

    Я немного попробовал изменить организацию таблиц в базе.
    Хотел узнать Ваше мнение:
    — правильно ли я организовал связь Один-К-Одному?
    — правильно ли создал структуру таблиц?

    mysql> CREATE DATABASE office;
    mysql> USE office;

    mysql> CREATE TABLE adresses(id INT AUTO_INCREMENT PRIMARY KEY, city VARCHAR(50));
    mysql> CREATE TABLE users(id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20), adresses_id INT, FOREIGN KEY(adresses_id) REFERENCES adresses(id));

    Теперь таблица users будет вот такая:
    mysql> DESCRIBE users;
    +————-+————-+——+——+———+—————-+
    | Field | Type | Null | Key | Default | Extra |
    +————-+————-+——+——+———+—————-+
    | id | int(11) | NO | PRI | NULL | auto_increment |
    | name | varchar(20) | YES | | NULL | |
    | adresses_id | int(11) | YES | MUL | NULL | |
    +————-+————-+——+——+———+—————-+

    А таблица adresses будет такой:
    mysql> DESCRIBE adresses;
    +——-+————-+——+——+———+—————-+
    | Field | Type | Null | Key | Default | Extra |
    +——-+————-+——+——+———+—————-+
    | id | int(11) | NO | PRI | NULL | auto_increment |
    | city | varchar(50) | YES | | NULL | |
    +——-+————-+——+——+———+—————-+

    Добавляю данные в таблицы:
    в adresses:
    mysql> INSERT INTO adresses VALUE(null, ‘Киев’);
    mysql> INSERT INTO adresses VALUE(null, ‘Полтава’);
    mysql> INSERT INTO adresses VALUE(null, ‘Днепропетровск’);

    и в users:
    mysql> INSERT INTO users VALUE( null, ‘Степан’, 1 );
    mysql> INSERT INTO users VALUE( null, ‘Иван’, 2 );
    mysql> INSERT INTO users VALUE( null, ‘Владимир’, 1 );
    mysql> INSERT INTO users VALUE( null, ‘Виталий’, 3 );
    mysql> INSERT INTO users VALUE( null, ‘Девушка Красивая’, 3 );
    mysql> INSERT INTO users VALUE( null, ‘Просто Девушка’, 1 );
    mysql> INSERT INTO users VALUE( null, ‘Мужик’, 3 );
    mysql> INSERT INTO users VALUE( null, ‘Агрипина Леопольдовна’, 2 );

    Получаю:
    mysql> select * from adresses;
    +—-+——————————+
    | id | city |
    +—-+——————————+
    | 1 | Киев |
    | 2 | Полтава |
    | 3 | Днепропетровск |
    +—-+——————————+

    mysql> select * from users;
    +—-+——————————————+————-+
    | id | name | adresses_id |
    +—-+——————————————+————-+
    | 1 | Степан | 1 |
    | 2 | Иван | 2 |
    | 3 | Владимир | 1 |
    | 4 | Виталий | 3 |
    | 5 | Девушка Красивая | 3 |
    | 6 | Просто Девушка | 1 |
    | 7 | Мужик | 3 |
    | 8 | Агрипина Леопольдовн | 2 |
    +—-+——————————————+————-+

    Проверяю (хотя пока эта конструкция тяжеловато пошла, но разобрался 🙂
    mysql> SELECT u.name,a.city FROM users u LEFT JOIN adresses a ON u.adresses_id = a.id;

    +——————————————+——————————+
    | name | city |
    +——————————————+——————————+
    | Степан | Киев |
    | Иван | Полтава |
    | Владимир | Киев |
    | Виталий | Днепропетровск |
    | Девушка Красивая | Днепропетровск |
    | Просто Девушка | Киев |
    | Мужик | Днепропетровск |
    | Агрипина Леопольдовн | Полтава |
    +——————————————+——————————+

    Теперь, как мне кажется, нет дублируемых записей в таблице adresses и каждому юзеру из users
    соответствует тоже одна не перегруженная лишней информацией запись с уникальным id.

    Спасибо Вам за уроки. Хорошо и легко воспринимаются.

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Этот сайт использует Akismet для борьбы со спамом. Узнайте как обрабатываются ваши данные комментариев.

×