Ошибка при создании Foreign Key?

Question

[Дмитрий]

Хочу создать для таблицы "Номера гостинницы" (rooms) внешний ключ к таблице "Размер номера"(roomsize).
Создаю через админку phpmyadmin, сгенерированный SQL-запрос выглядит следующе:

ALTER TABLE `rooms` 
ADD CONSTRAINT `room_size` 
FOREIGN KEY (`id_room_size`) 
REFERENCES `hotel`.`roomsize`(`id_room_size`) 
ON DELETE RESTRICT 
ON UPDATE RESTRICT;

В итоге выдает вот такую ошибку:

Как исправить эту ошибку?

UPDATE:
Также пробовал вот так связь сделать, но опять же ошибка

Answer 1

[d-stream]

Для начала:

select * from rooms where rooms.id_room_size not in (
select id_room_size from hotel.roomsize
)

Comments

[Дмитрий]

И как тогда быть?

[d-stream]

Дмитрий: вот и причина: в hotel.roomsize ОТСУТСТВУЮТ строки на которые должна была бы ссылаться таблица rooms

лечение: создать недостающие записи и создать констрэйн, в дальнейшем уже контроль целостности не позволит добавлять строки в rooms с дедопустимыми id_room_size

[Дмитрий]

d-stream, понял, спасибо большое :)

Answer 2

[RidgeA]

Возможно таблицы уже не консистентны, и та таблица, в которую добавляется ключ уже содержит id, которого нет в в таблице, на которую ссылается ключ

Comments

[Дмитрий]

Дак вроде бы все id идентичны, типы совпадают. Даже больше: однажды у меня получилось создать, но потом я через диаграмму удалил связь и попробовал создать заново по той же схеме – не получилось. Т.е. один раз оно создалось, а потом, удалив, со второго уже не получается.

[RidgeA]

Нет. Я к тому, что в поле rooms.id_room_size есть значения, которых нет в `hotel`.`roomsize`.`id_room_size`

[Дмитрий]

RidgeA, значений нету. Есть лишь дефолтное значение 0, которое автоматом устанавливается для INT, как я понял из ошибки на скриншоте

[entermix]

Дмитрий: а ведь 0 это и есть ваш внешний ключ, он есть в другой таблице? Если нет, то дефолтное значение должно быть NULL

[Дмитрий]

entermix, нет, нету. Мне уже объяснили. В другой таблице значениями являются 1..5, потому связь и не устанавливалась

[entermix]

Дмитрий: установите дефолтное значение в NULL, вместо 0, тогда будет работать

[Дмитрий]

entermix, логика такова, что NULL там быть никак не может. Номер априори должен иметь количество мест. Но вот установив 1, всё заработает как нужно. Или я чего-то не знаю/не понимаю о использовании NULL?)

[entermix]

Дмитрий: но ведь у вас есть 0 по умолчанию, это как?

[Дмитрий]

entermix, ну я не знаю) По умолчанию я не указывал каких-либо значений. Когда я удалил и заново создал колонку, то в ячейках автоматически установилось значение 0. Когда я попытался удалить 0 из ячейки, то у меня отображалась ошибка приведенная на скриншоте выше. Как я понял из ошибки, INT не может иметь пустое значение и автоматически устанавливает 0

[entermix]

Дмитрий: В данном случае, id_room_size должен указывать на ID записи во второй таблице, если его не существует, должно быть NULL

Зачем Вы размер номера вынесли в другую таблицу, если там содержится всего одна цифра, это уже другой вопрос

[Дмитрий]

entermix, у меня размер привязывается к "типу номера". Т.е. "Стандарт" может иметь 1, 2х, 3х местный вариант. Также есть "Люкс", с 1, 2х, 3х местным вариантом.

И кажется я понял что мне не нужна эта таблица, а я могу просто брать значения из поля room_sizeв таблице rooms. Мдэ, заполночь отдает свое

[entermix]

Дмитрий: должно быть как то так:

rooms
id (PK, AI, unsigned INT), number (unsigned INT), type_id (FK, unsigned INT DEFAULT NULL), ..
room_types
id (PK, AI, unsigned INT), name, ...

Тогда будет:

rooms:
1, 1, 1
2, 2, 2
3, 3, NULL
4, 4, 1
5, 5, 3

room_types
1, Стандарт (2 места)
2, Стандарт (3 места)
2,  Люкс (1 место)

Естественно, если тип должен быть указан обязательно, поле type_id не должно быть DEFAULT NULL