Есть БД, в которой следующие поля
id, parent_id, name
При этом parent_id = 0, если нет родителя, или = id родителя. Как правильно сделать индекс, чтобы нельзя было воткнуть невалидную запись (без родителя)?
При этом parent_id = null нельзя делать, с null не будет работать проверка на уникальность
UNIQUE INDEX `UQ_CATEGORY` (`parent_id` ASC, `name` ASC))
Так:
create table tree (
id int primary key,
parent_id int,
CONSTRAINT FOREIGN KEY (`parent_id`) REFERENCES `tree` (`id`)
);
insert into tree (id, parent_id) values
(0,0), -- нулевой элемент - корень дерева
(1,0),
(2,1);
insert into tree (id, parent_id) values (3,100500); -- тут будет ошибка
@QuAzI, можно еще следующим образом сделать.
Если в запрос передаются вместо paramId и paramParent конкретные значения
INSERT INTO a
SELECT paramId, paramParent FROM a
WHERE a.id = paramParent
Если предполагаемого родителя не найдется (нет такого id, которое равно paramParent), то и никаких изменений не произойдет.
UPD:
Опять же, если есть возможность реализовать алгоритм с помощью хранимой процедуры, то можно в теле процедуры проверять наличие записей по условию с помощью SELECT EXISTS:
DELIMITER $
CREATE PROCEDURE myProc(id int, parent int)
BEGIN
IF (SELECT EXISTS(SELECT * FROM table1 WHERE table1.id = parent)) THEN
INSERT INTO table1 VALUES(id, parent);
END IF;
END $
Если необходимо, то остается только прикрутить проверку на равенство id и parent и проверку на существование id.
Другой вопрос - скорость я не измерял.