MYSQL foreign key on delete set NOT NULL (default, 0) — как исправить?

Question

[Иван]

CREATE TABLE IF NOT EXISTS product_category (
	id int unsigned NOT NULL AUTO_INCREMENT,
	name varchar(100) NOT NULL,
	PRIMARY KEY (id),
	UNIQUE KEY (name),
	UNIQUE KEY (url)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS product (
	id int unsigned NOT NULL AUTO_INCREMENT,
	name varchar(100) NOT NULL,
	category_id int unsigned NOT NULL,
	PRIMARY KEY (id),
	FOREIGN KEY (category_id) REFERENCES product_category (id) ON DELETE SET NULL
) ENGINE=InnoDB;

Есть ли возможность сделать так, чтобы при удалении родительской таблицы в связанном поле дочерней выставлялось значение 0 или DEFAULT? SET NULL не устраивает, поскольку требует смены типа данных с NOT NULL на NULL, иначе выдает ошибку.
SET '0', SET 0, SET DEFAULT тоже выдают ошибку.

Comments

[Иван]

Чтобы уточнить: структуру БД делаю для приложения на Yii. Вычитал, что обеспечивать целостность данных лучше средствами БД и понеслось :)

Answer 1

[Yuri Morozov]

Нет такой возможности, а если в каких-то косячных базах и есть - это большая ошибка, поскольку нарушает ссылочную целостность данных.
Внешний ключ обязан иметь либо существующее значение, либо NULL. == "значение неизвестно".
Вам поможет смена типа данных с NOT NULL на NULL, я не понимаю, почему Вас это напрягает.

Comments

[Иван]

Почему тогда все во всех столбцах ставят not null? Я не тестировал, но мне почему-то интуитивно показалось, что если поле не проиницилизировано (= NULL), то при обращении к нему mysql выдаст ошибку.

[Иван]

И какая разница, стоит в поле значение NULL или 0? Или mysql попытается найти связанную таблицу с номером 0 и выдаст ошибку?

[Yuri Morozov]

@0neS not null во всех полях - это либо косяк (MyISAM такое спокойно жрал, поскольку не проверяет целостность), либо это поле действительно никогда не должно быть пустым (соответственно выставляется constraint типа on delete cascade).

0 в поле - именно то, о чем Вы сказали. Попытается найти ряд с PK == 0 и обломается. Собственно, оно и пытается Вам об этом сказать своей ошибкой :)

Answer 2

[Сергей Кудрин]

А зачем вам not null если вы хотите при удалении категории оставлять новости? Вы уж определитесь могут ли у вас быть новости без категорий или не могут и уже исходя из этого выбирайте "not null и on delete cascade" или "null и on delete set null".

Answer 3

[svd71]

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

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

Answer 4

[Иван]

Схема в итоге получилась следующая (буду рад любым замечаниям):

CREATE TABLE IF NOT EXISTS user (
	id int unsigned AUTO_INCREMENT,
	regdate timestamp,
	lastvisit timestamp,
	email varchar(100),
	password varchar(128),
	salt varchar(20),
	status tinyint,
	level tinyint,
	balance decimal(10,2),
	PRIMARY KEY (id),
	UNIQUE KEY (email)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS user_profile (
	id int unsigned AUTO_INCREMENT,
	uid int unsigned NOT NULL,
	firstname timestamp,
	PRIMARY KEY (id),
	UNIQUE KEY (uid),
	FOREIGN KEY (uid) REFERENCES user (id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS user_activation (
	id int unsigned AUTO_INCREMENT,
	uid int unsigned NOT NULL,
	expire timestamp,
	hash varchar(20),
	PRIMARY KEY (id),
	FOREIGN KEY (uid) REFERENCES user (id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS user_reset (
	id int unsigned AUTO_INCREMENT,
	uid int unsigned NOT NULL,
	expire timestamp,
	hash varchar(20),
	PRIMARY KEY (id),
	FOREIGN KEY (uid) REFERENCES user (id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS user_session (
	id int unsigned AUTO_INCREMENT,
	uid int unsigned NOT NULL,
	expire timestamp,
	hash varchar(20),
	ip varchar(39),
	agent varchar(200),
	PRIMARY KEY (id),
	FOREIGN KEY (uid) REFERENCES user (id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS product_category (
	id int unsigned AUTO_INCREMENT,
	name varchar(100),
	url varchar(100),
	PRIMARY KEY (id),
	UNIQUE KEY (name),
	UNIQUE KEY (url)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS product_brand (
	id int unsigned AUTO_INCREMENT,
	name varchar(100),
	url varchar(100),
	PRIMARY KEY (id),
	UNIQUE KEY (name),
	UNIQUE KEY (url)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS product (
	id int unsigned AUTO_INCREMENT,
	created timestamp,
	edited timestamp,
	name varchar(100),
	description text,
	category_id int unsigned NULL,
	brand_id int unsigned NULL,
	url varchar(100),
	cost decimal(10,2),
	quantity int unsigned,
	PRIMARY KEY (id),
	FOREIGN KEY (category_id) REFERENCES product_category (id) ON DELETE SET NULL,
	FOREIGN KEY (brand_id) REFERENCES product_brand (id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS product_photo (
	id int unsigned AUTO_INCREMENT,
	product_id int unsigned NOT NULL,
	url varchar(100),
	order_id int unsigned,
	PRIMARY KEY (id),
	UNIQUE KEY (url),
	FOREIGN KEY (product_id) REFERENCES product (id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS product_tag (
	id int unsigned AUTO_INCREMENT,
	name varchar(100),
	PRIMARY KEY (id),
	UNIQUE KEY (name)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS product_tag_relation (
	id int unsigned AUTO_INCREMENT,
	product_id int unsigned NOT NULL,
	tag_id int unsigned NOT NULL,
	PRIMARY KEY (id),
	FOREIGN KEY (product_id) REFERENCES product (id) ON DELETE CASCADE,
	FOREIGN KEY (tag_id) REFERENCES product_tag (id) ON DELETE CASCADE
) ENGINE=InnoDB;

Comments

[Yuri Morozov]

Вроде норм. Только два замечания:
1. вместо edited лучше использовать updated - традиция.
2. нужные индексы не забыли создать?

[Иван]

@metamorph
1. Точняк, спасибо.
2. Какие?)

[Yuri Morozov]

@0neS Ну, какие требуются. Откуда я знаю, какие Вы там запросы городить собираетесь и сколько у Вас в БД записей планируется.

[Иван]

@metamorph что за индексы вообще?) INDEX?
ALTER TABLE user ADD INDEX i_email (email)?
Не совсем понимаю, для чего они и нужны ли они мне в Yii.

[Yuri Morozov]

@0neS Вот это поворот.
Настало время почитать что-нибудь про БД.

[svd71]

Маленькая отсебятинка.

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

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

1. Любое каскадирование несет в себе нюансы, которые затрудняют анализ ошибок при работе с клиентским ПО. Вы не можете быть уверенным, какое значение было до изменения. Соотвественно не можете правильно реагировать на такие изменения. Одно дело, если у вас статьи на личном сайте, другое дело если в редакции крупнейшего издания или вообще речь идет об управлении роботом.

2. Не стоит забывать, что на выполнение како-либо причудливой логической цепочки будет требоватъся машинное время и соотвественно ресурсы.

Соотвественно я обхожусь только созданием индексов на соотвествующие поля.