There is a database in which the following fields

id, parent_id, name

In this case, parent_id = 0, if there is no parent, or = id of the parent. How to make an index correctly so that an invalid entry cannot be inserted (without a parent)?

In this case, parent_id = null cannot be done, the check for uniqueness will not work with null

 UNIQUE INDEX `UQ_CATEGORY` (`parent_id` ASC, `name` ASC))
  • Something did not quite understand. Why not declare parent_id NOT NULL? - smackmychi
  • This has already been done, but how does this protect against the insertion of parent_id = 34563456, which has never happened before? - QuAzI
  • Probably, it is possible through the stored procedure to check and insert. delimiter $$ create procedure inst (id int, parent int) begin If (select count (a.id) from a where a.id = parent)> 0 then insert into a values ​​(id, parent); end if; end $$ But this needs to be finished because there are still questions about incorrect input (there are no entries with the id parameter, id = parent). But is it not simpler not to let the user directly enter these parameters and, accordingly, do not allow him to send harmful values ​​at all? Or is it a web? - smackmychi
  • What DBMS is used? It is possible to write a trigger when inserting a new record that would analyze the existence of id if it was specified in parent_id. - IntegralAL
  • @IntegralAL, mysql. But the triggers, as I understand it, do not have the ability to interrupt the course of the operation. - smackmychi

4 answers 4

So:

 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); -- тут будет ошибка

http://sqlfiddle.com/#!2/debf5f

  • Unless the case id = parent_id is possible. - smackmychi
  • It does not matter, the person himself said that he tightens the screws, and at least a situation is possible where the parent_id will not exist, so one cannot guess here. - smackmychi
  • @Yura Ivanov And then what? What is your option? Create an index, and then clean gigabytes? If a person says that he is tightening the screws, then in your case you will have to do something else besides creating an index, although one question is brewing - why do a lot of things to do when you can limit yourself to one structural unit and immediately foresee options? That's precisely because I only repelled by what the vehicle says and propose a definite solution that can be freely expanded and not climbed into other wilds. - smackmychi

@QuAzI , you can still do this as follows.

If specific values ​​are passed to the request instead of paramId and paramParent

 INSERT INTO a SELECT paramId, paramParent FROM a WHERE a.id = paramParent

If the intended parent is not found (there is no such id, which is equal to paramParent), then no changes will occur.

UPD:
Again, if it is possible to implement an algorithm using a stored procedure, then it is possible in the body of the procedure to check the presence of records by condition using 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 $

If necessary, it remains only to fasten a check for equality of id and parent and a check for the existence of id.

Another question - the speed I did not measure.

    Nate, the good old article on working with trees in MySQL. Triggers also advised you - here are good examples: one and two .

    Enter fictitious parent with id = 0.