Как добавить внешний ключ в таблицу на колонку с null с уже существующими записями?

Question

[Qixing]

Добрый день. Есть таблица бренды(brand) и товары(item). В товарах есть колонка brand, которым я хочу сделать внешний ключ на brand.id. Но поле item.brand может и быть null. В таблице уже есть записи. Там где нет связей brand=null.

ALTER TABLE  `item` ADD FOREIGN KEY (  `brand` ) REFERENCES  `imstor`.`brand` (

`id`
) ON DELETE RESTRICT ON UPDATE RESTRICT ;

Ответ MySQL: Документация

#1215 - Cannot add foreign key constraint

CREATE TABLE IF NOT EXISTS `brand` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`category` int(10) NOT NULL,
`url` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
KEY `category_id` (`category`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=22 ;

-- --------------------------------------------------------

--
-- Структура таблицы `item`
--

CREATE TABLE IF NOT EXISTS `item` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`category` int(10) NOT NULL,
`url` varchar(255) NOT NULL,
`brand` int(10) DEFAULT NULL,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
KEY `category_id` (`category`),
KEY `brand` (`brand`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=420 ;

ALTER TABLE `brand`
ADD CONSTRAINT `brand_ibfk_1` FOREIGN KEY (`category`) REFERENCES `category` (`id`);

ALTER TABLE `item`
ADD CONSTRAINT `item_ibfk_1` FOREIGN KEY (`category`) REFERENCES `category` (`id`);

Comments

[Алексей Уколов]

Приведите изначальный CREATE TABLE

[Qixing]

CREATE TABLE IF NOT EXISTS `brand` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`category` int(10) NOT NULL,
`url` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
KEY `category_id` (`category`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=22 ;

-- --------------------------------------------------------

--
-- Структура таблицы `item`
--

CREATE TABLE IF NOT EXISTS `item` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`category` int(10) NOT NULL,
`url` varchar(255) NOT NULL,
`brand` int(10) DEFAULT NULL,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
KEY `category_id` (`category`),
KEY `brand` (`brand`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=420 ;

ALTER TABLE `brand`
ADD CONSTRAINT `brand_ibfk_1` FOREIGN KEY (`category`) REFERENCES `category` (`id`);

ALTER TABLE `item`
ADD CONSTRAINT `item_ibfk_1` FOREIGN KEY (`category`) REFERENCES `category` (`id`);

Answer 1

[Vinatorul]

Попробуйте вот так

ALTER TABLE  `item` ADD CONSTRAINT 'fk_brand_id' FOREIGN KEY (`brand`) REFERENCES  `imstor`.`brand`(`id`) 
ON DELETE RESTRICT ON UPDATE RESTRICT;

Comments

[Qixing]

#1452 - Cannot add or update a child row: a foreign key constraint fails (`imstor`.`#sql-26d4_14`, CONSTRAINT `fk_brand_id` FOREIGN KEY (`brand`) REFERENCES `brand` (`id`))

[Vinatorul]

Qixing: Так может дело в том, что в таблице 'item' в столбце 'brand' есть записи, которым нет соответствующих в таблице 'brand'?

[Vinatorul]

Qixing: Ну с Null проблем не должно быть. Может быть проблема с не null значениями.
Попробуйте сделать LEFT OUTER JOIN по 'brand'

[Qixing]

Действительно затерялась одна запись c item.brand не существовавшим в brand. Хотя я был уверен на 100%, что все ок. Спасибо всем!

Answer 2

[zhogar]

скорее всего is null а не равно, хотя могу ошибаться.

Comments

[Qixing]

в Mysql я могу только NULL поставить.

[zhogar]

Qixing: Для проверки поля на NULL есть специальный оператор: IS NULL, IS NOT NULL

В связанных таблицах, таблицу которую необходимо вывести с null значениями, ставят префикс outer. Например:

SELECT left_tbl.*
FROM { OJ left_tbl LEFT OUTER JOIN right_tbl ON left_tbl.id = right_tbl.id }
WHERE right_tbl.id IS NULL;

полезно посмотреть: eddnet.org/?p=1580

[Qixing]

zhogar: это понятно. Но я лишь всего хочу добавить ключ FOREIGN KEY