PHP.SU

Программирование на PHP, MySQL и другие веб-технологии

PHP.SU Портал

Главная

Помощь

Поиск

Поиск Яндекс Вакансии

Пользователи

Здравствуйте, Гость

( Вход · Регистрация · Правила форума )

Забыли пароль?

Не работают механизмы внешних ключей.

Форумы портала PHP.SU » PHP » SQL и Архитектура БД (Модератор: SAD)

Страниц (1): [1]

Без описания

Поиск в теме | Версия для печати

maximushka	Отправлено: 05 Февраля, 2011 - 21:26:07
Новичок Покинул форум Сообщений всего: 40 Дата рег-ции: Нояб. 2009 Помог: 0 раз(а)	Не могу на реальной практике испытать возможности механизма внешних ключей, не работает ни один из 3ех возможностей: 1. вывод ошибки при добавлении записи которая во внешнем ключе содержит значение, которого нет в поле другой таблицы, на которое ссылается он. 2. каскадное удаление. 3. каскадное обновление. Вот мой пример: CODE (SQL): скопировать код в буфер обмена CREATE TABLE `users` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT , `nik` VARCHAR(15) NOT NULL, `password` CHAR(32) NOT NULL, `time` INT UNSIGNED NOT NULL, PRIMARY KEY (`id`), UNIQUE INDEX (`nik`) )CHARACTER SET=UTF8; CREATE TABLE `messages` ( `time` INT UNSIGNED DEFAULT 6 , `no` INT NOT NULL AUTO_INCREMENT, `user_id` INT UNSIGNED NOT NULL, `text` BLOB, PRIMARY KEY (`time`,`no`), KEY (`user_id`), FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE )CHARACTER SET=UTF8; INSERT INTO `users` (`nik`,`password`,`time`) VALUES ('MAX','8f95be5ec3c0c103e51de269d39f5592', 1296120857), ('ANNA','d9681d05860552e9c3113da381f916fc', 1294423538); INSERT INTO `messages` (`user_id`, `text`) VALUES (3,'Без связки!'),(1,'Со связкой'),(2,'И этот со связкой'); DELETE FROM `users` WHERE `id`=1; UPDATE `users` SET `id`=4 WHERE `id`=2; SELECT * FROM `messages`; Он успешно проходит вместо того чтобы дать сообщение об ошибке при первой вставке, и выдаёт такую таблицу (справа я написал комментарий к строкам что меня не устраивает, и что я ожидал): Цитата: time no user_id text 6 1 3 Без связки! - Вывод об ошибке, т.к. нет пользователя с номером 3 6 2 1 Со связкой - это сообщения должно быть удалено с удалением пользователя с номером 1 6 3 2 И этот со связкой - у этого должен каскадно обновиться user_id и стать равным четырём

Мелкий	Отправлено: 05 Февраля, 2011 - 21:29:05
Активный участник Покинул форум Сообщений всего: 11926 Дата рег-ции: Июль 2009 Откуда: Россия, Санкт-Петербург Помог: 618 раз(а)	дефолтно создаются таблицы MyISAM, не поддерживающие внешние ключи. Используйте innodb ----- PostgreSQL DBA

maximushka	Отправлено: 06 Февраля, 2011 - 11:30:37
Новичок Покинул форум Сообщений всего: 40 Дата рег-ции: Нояб. 2009 Помог: 0 раз(а)	Мелкий пишет: дефолтно создаются таблицы MyISAM, не поддерживающие внешние ключи. Используйте innodb Спасибо, вы пожалуй правы. Но в этом случае на запрос: CODE (SQL): скопировать код в буфер обмена CREATE TABLE `messages` ( `time` INT UNSIGNED DEFAULT 6 , `no` INT UNSIGNED NOT NULL AUTO_INCREMENT, `user_id` INT UNSIGNED NOT NULL, `text` BLOB, PRIMARY KEY (`time`,`no`), FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE )TYPE=INNODB CHARACTER SET=UTF8; Выдаётся ошибка: Цитата: 1075 Incorrect table definition; there can be only one auto column and it must be defined as a key Когда я убираю AUTO_INCREMENT или убираю инкрементовое поле `no` или поле `time` из первичного ключа, то запрос проходит без ошибки и таблица создаётся. Видимо INNODB почему-то не любит если в составе составного первичного ключа используется одно поле с AUTO_INCREMENT, а для меня этот инкремент нужен именно в таком качестве, потому что в одну секунду может поступить несколько сообщений, а уникальность каждого соблюсти надо. Я понимаю что можно обойтись одним инкрементным полем `no` в качестве первичного ключа, но хочется знать можно ли сделать именно по своей задумке? Зачем? Ну, например, для того чтобы использовать поле `no` меньшего размера для экономии памяти, например TINYINT, если известно, что за 1 секунду не может поступить более 255 сообщений. Однако за всю историю существования чата сообщений может быть куда более 255, и разумеется нужно делать ключ для сообщений побольше чем в 1 байт(TINYINT), а время каждого сообщения в секундах от 1970 года тоже требуется хранить, и получается уже не совсем рационально.

TM123	Отправлено: 06 Февраля, 2011 - 11:40:15
Новичок Покинул форум Сообщений всего: 35 Дата рег-ции: Нояб. 2010 Откуда: Москва Помог: 0 раз(а)	Ну вообще если у вас одно поле AUTO_INCREMENT, то у вас комбинация всегда будет уникальной!!!, за исключением принудительной вставки AUTO_INCREMENT поля своим значением, но если есть такая возможность, тогда зачем делать поле AUTO_INCREMENT. Такой логики придерживается MySQL и это в общем то правильная логика, поэтому вас и обругивает база. ----- Программим потихоньку http://www[dot]altycon[dot]ru

garvey	Отправлено: 06 Февраля, 2011 - 11:43:02
Частый посетитель Покинул форум Сообщений всего: 528 Дата рег-ции: Май 2010 Откуда: Minsk Помог: 3 раз(а)	Вот таким способом нужно создавать InnoDB сущности: CODE (SQL): скопировать код в буфер обмена CREATE TABLE `table_name` ( ...FIELDS ) ENGINE=InnoDB; Согласно формам нормализации, таблица всегда должна иметь уникальный первичный ключ. У вас его нет. ----- http://plutov[dot]by

maximushka	Отправлено: 06 Февраля, 2011 - 11:57:32
Новичок Покинул форум Сообщений всего: 40 Дата рег-ции: Нояб. 2009 Помог: 0 раз(а)	TM123 пишет: за исключением принудительной вставки AUTO_INCREMENT поля своим значением, но если есть такая возможность, тогда зачем делать поле AUTO_INCREMENT. я такой возможностью в таблице `messages` и не планировал пользоваться, поле с инкрементом всегда само устанавливается во вставляемой записи. Не пойму с чего вы это взяли? garvey пишет: ENGINE=InnoDB; а чем ENGINE отличается от TYPE? garvey пишет: таблица всегда должна иметь уникальный первичный ключ. У вас его нет. Как это у меня его нет? я же указал: CODE (SQL): скопировать код в буфер обмена PRIMARY KEY (`time`,`no`) , он вполне уникальный и первичный ключ. Правда составной.

maximushka	Отправлено: 06 Февраля, 2011 - 18:58:34
Новичок Покинул форум Сообщений всего: 40 Дата рег-ции: Нояб. 2009 Помог: 0 раз(а)	Достиг желаемого на голом SQL, правда несколько тяжеловато вышло. Значит проблема была в том, что таблицы InnoDB не поддерживают инкрементное поле в составе составного ключа, которое мне вообщем-то нужно было позарез, в обход этой ситуации я наметил такую конструкцию вставки, ведь инкремент работает при вставки: CODE (SQL): скопировать код в буфер обмена INSERT INTO `messages`(`time`,`no`,`user_id`, `text`) SELECT @p:=123, IFNULL(MAX(`m2`.`no`),0)+1,1,'Без связки!' FROM `messages` AS `m2` WHERE `m2`.`time`=@p; Т.е. в работе можно убедиться выполнив, например: CODE (SQL): скопировать код в буфер обмена INSERT INTO `messages`(`time`,`no`,`user_id`, `text`) SELECT @p:=123, IFNULL(MAX(`m2`.`no`),0)+1,1,'Без связки!' FROM `messages` AS `m2` WHERE `m2`.`time`=@p; INSERT INTO `messages`(`time`,`no`,`user_id`, `text`) SELECT @p:=123, IFNULL(MAX(`m2`.`no`),0)+1,1,'Без связки!' FROM `messages` AS `m2` WHERE `m2`.`time`=@p; INSERT INTO `messages`(`time`,`no`,`user_id`, `text`) SELECT @p:=123, IFNULL(MAX(`m2`.`no`),0)+1,1,'Без связки!' FROM `messages` AS `m2` WHERE `m2`.`time`=@p; INSERT INTO `messages`(`time`,`no`,`user_id`, `text`) SELECT @p:=125, IFNULL(MAX(`m2`.`no`),0)+1,1,'Без связки!' FROM `messages` AS `m2` WHERE `m2`.`time`=@p; INSERT INTO `messages`(`time`,`no`,`user_id`, `text`) SELECT @p:=125, IFNULL(MAX(`m2`.`no`),0)+1,1,'Без связки!' FROM `messages` AS `m2` WHERE `m2`.`time`=@p; и получив правильно идущие значения искусственного автоинкремента в поле `no`: Цитата: time no 123 1 123 2 123 3 125 1 125 2

Поиск в теме | Версия для печати

Страниц (1): [1]

Сейчас эту тему просматривают: 0 (гостей: 0, зарегистрированных: 0)

« SQL и Архитектура БД »

Все гости форума могут просматривать этот раздел.
Только зарегистрированные пользователи могут создавать новые темы в этом разделе.
Только зарегистрированные пользователи могут отвечать на сообщения в этом разделе.