Как правильно организовать внешние ключи в MySQL?

Question

[sacred1]

Есть две таблицы..первая таблица эта "Люди",где буду содержатся :

CREATE TABLE peoples(
id_people INT(255) NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARACHAR(15),
surname VARACHAR(15),
patronymic VARACHAR(15)
);

И вторая таблица собственно "Комментарии" этих самых пользователей:

CREATE TABLE comments(
id_comment INT(255) NOT NULL AUTO_INCREMENT PRIMARY KEY,
comment TEXT(),
...(и как здесь связать?)
);

Внешний ключ как предполагается будет id_people соответственно,и хотелось бы понять как лучше всего организовать эту связку.

Следующий вопрос,как организовать передачу этого самого id_people в таблицу комментарии?Я так понимаю мы обращаемся к двум таблицам,причем id_people надо как то передать в таблицу с отзывами,и как это сделать?функциями mysql(что-то типо LAST_INSERT_ID),либо хранимыми процедурами,или еще как.

Заранее благодарю!

Answer 1

[Rsa97]

10²⁵⁵ людей? Где же Вы столько наберёте?
Создание внешнего ключа:

ALTER TABLE `comments` 
    ADD COLUMN `peoples_id` INT(255) NOT NULL AFTER `comment`,
    ADD INDEX `peoples_id_idx` (`peoples_id` ASC);

ALTER TABLE `comments` 
    ADD CONSTRAINT `comments_peoples`
        FOREIGN KEY (`peoples_id_fk`)
        REFERENCES `peoples` (`id_people`)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION;

В зависимости от того, что укажете в ON DELETE и ON UPDATE будет меняться поведение базы при попытке удалить запись в `peoples` или изменить `peoples`.`id_people`.

Comments

[sacred1]

А почему вы поставили индекс на имени "peoples_id_idx",для чего это?вроде же можно было просто ADD INDEX "peoples_id" сделать.

И самый главный вопрос,при добавление отзыва получается мне надо соед. с двумя таблицами?как мне этот внешний ключ переносить в таблицу "отзывов",пока только идея с хранимыми процедурами.

[Rsa97]

_idx - потому, что индекс. Мне, например, так удобнее потом читать.
А соединяться с двумя таблицами не надо. Внешний индекс с точки зрения таблицы `comments` - обычное поле, в котором указан id человека, написавшего отзыв. Чаще всего этот id при авторизации записывают в сессию и берут оттуда. Единственное ограничение - запись с таким id должна существовать в таблице `peoples`.

[sacred1]

Rsa97: Спасибо!но..а если авторизация не предусмотрена ? есть просто форма с полями(имя,фамилия,отчество и отзыв сам),и надо как то раскидать данные по таблицам.

[Rsa97]

Тогда непонятен смысл в раскидывании по таблицам - если я каждый раз буду представляться новым именем, то сущность `peoples` вырождается, проще записывать поля прямо в `comments`.
Но если хочется, то да. Сначала придётся проверить, нет ли в `peoples` записи с такими ФИО, если есть - взять её id, если нет - вставить её туда и взять last_insert_id. Затем уже этот id использовать в `comments`.

[sacred1]

Rsa97: Не ну естественно там будет соответствующая валидация .Насчет разбивание не знаю,щас конечно прямо такой необходимости нету,просто в будущем будут прибавляться поля ...например данные о пользователе(город,дата рождение и .т.д) и ведь надо будет как то распределять,не в одной же таблице все.Или как вы считаете?

[Rsa97]

Я то как раз начинаю все свои проекты с планирования - какие будут сущности в базе, что с чем и как связано, нормализация базы. Если необходима авторизация - по какой базе (sq, ldap, pam), какие категории пользователей будут, какие права будут у каждой категории...
Тщательное планирование позволяет на порядок сократить время на переписывание проекта когда в середине работы оказывается, например, что отношение будет не "многие к одному", а "многие к многим".

[sacred1]

Rsa97:Ну да,вы правы. Кстати,скорее всего мне не подойдет метод все в одну таблицу,т.к отзыва ведь может быть два и более)

Answer 2

[Vicom]

Следующий вопрос,как организовать передачу этого самого id_people в таблицу комментарии?

вопрос поставлен не совсем корректно, но, где-то уже через полгода Вы даже не будете мыслить подобным образом, так что проехали. думаю, Вам нужна информация о возможности делать условную выборку из одной таблицы по найденному значению из другой. если так, то копайте в сторону JOIN, на днях осилил, 20 ссылок, небольшое перестроение головы и временная смена рода мозговой деятельности помогают, если сразу не воткнёте.

PS чтобы подойти с нужной стороны определите сначала для себя в голове такие понятия как:
- реляционная СУБД
- структура таблицы реляционной СУБД
- поля таблицы
- колонки таблицы
- внешний ключ (одна из тех самых колонок, связывающий те самые поля одних таблиц с другими через их значение)

доки из моих закладок по JOIN
100 различных вариантов толкования одного и того же (максимально доходчивых для меня)
- MySQL немного о JOIN'ах
- Наглядное объяснение принципа объединения таблиц в...
- Объяснение SQL объединений JOIN: LEFT/RIGHT/INNER/OUTER
- MySQl: использоваение операторов JOIN на примерах
- Разработка → MySQL и JOINы

ну и когда подтянитесь уже - Top 20+ MySQL Best Practices

но начинайте именно и только с освоения понятий, описанных выше, это база, тут ссылки давать бесполезно, да и нет их у меня давно, курить книжки PDF и покупать в реале - самое то для нормалього понимания сути.

удачи!

Comments

[Bjornie]

Vicom кстати, понятие "внешний ключ" можно использовать для связей, где нет явной прописи Foreign key? А есть лишь ссылка на другую таблицу посредством id, например.

Answer 3

[Вячеслав Успенский]

При создании таблицы:

CREATE TABLE comments(
id_comment INT(255) NOT NULL AUTO_INCREMENT PRIMARY KEY,
comment TEXT not null,
id_people int(8) unsigned not null,
foreign key(id_people) references peoples(people_id) on update cascade on delete cascade);

Ну или как товарищ выше предложил.

ЗЫ Не путаетесь в таких наименованиях? First, Middle, Last name не лучше? )