Making a row always exist?

[Jake Arkinstall]

Hey guys.

I'm working on a set of applications for different uses (i.e. modifying and viewing articles both on the desktop and on the webserver), all using the same MySQL database.

The way it works is that there are topics, each with a parent topic. The master parent is called 'Home' and has ID -1 and parent 0. Simple 'nuff.

However, if this master row is deleted, it makes every subtopic inaccessable, as the system works like a tree - if the root node goes, all of its children have a parent which doesn't exist. This, as you can tell, causes all kinds of problems (as a key object isn't instantiated).

Whilst I can write simple fixes for all four applications, I was wondering if it was possible to 'force' a row to exist in MySQL, to simplify things. I.E. it cannot be deleted or editted - or if it doesn't exist at any time it gets created.

If you know/think there isn't a way to do this, I'd prefer a 'no' than no answers.

Thanks guys,
Jake Arkinstall

[tripy]

Why don't you enforce this with a trigger ?
For each delete, fire that trigger before, and check which rows are going to be deleted.
http://dev.mysql.com/doc/refman/5.0/...e-trigger.html

The OLD and NEW keywords enable you to access columns in the rows affected by a trigger. (OLD and NEW are not case sensitive.) In an INSERT trigger, only NEW.col_name can be used; there is no old row. In a DELETE trigger, only OLD.col_name can be used; there is no new row. In an UPDATE trigger, you can use OLD.col_name to refer to the columns of a row before it is updated and NEW.col_name to refer to the columns of the row after it is updated.

http://www.onlamp.com/pub/a/onlamp/2.../triggers.html
Simply deny it if t deletes your "master" row.

CREATE TRIGGER trigger_name
or BEFORE DELETE ON table_name
FOR EACH ROW
BEGIN
  -- Do your check here
END

[longneck]

change the parent rows from 0 to NULL and you can use FOREIGN KEYS. no triggers required.

[Jake Arkinstall]

Ah, so you're suggesting not having a physical root node at all?

[longneck]

no, you can have a root node. just set it's parent id to NULL instead of 0. then you can use foreign keys to restrict the ability to delete rows that still have children.

here's a representation of your future family tree that demonstrates this:

mysql> create table jake (
   id int auto_increment primary key
 , parent int
 , name varchar(10)
 , index(parent)
 , constraint foreign key (parent) references jake(id) on delete restrict
) engine = innodb;
Query OK, 0 rows affected (0.14 sec)

mysql> insert jake (name, parent)
values
   ('jake', null)
 , ('jake jr', 1)
 , ('jackie', 1)
 , ('jake iii', 2);
Query OK, 4 rows affected (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 0


mysql> select * from jake;
+----+--------+----------+
| id | parent | name     |
+----+--------+----------+
|  1 |   NULL | jake     |
|  2 |      1 | jake jr  |
|  3 |      1 | jackie   |
|  4 |      2 | jake iii |
+----+--------+----------+
3 rows in set (0.00 sec)

so you have 2 children, jake jr and jackie. jake jr has a child name jake iii.

notice how i can't delete you:

mysql> delete from jake where name = 'jake';
ERROR 1451 (23000): Cannot delete or update a parent row:
a foreign key constraint fails
(`test/jake`, CONSTRAINT `jake_ibfk_1` FOREIGN KEY (`parent`) REFERENCES `jake` (`id`))

and i also can't delete jake jr until i delete your grandson:

mysql> delete from jake where name = 'jake jr';
ERROR 1451 (23000): Cannot delete or update a parent row:
a foreign key constraint fails
(`test/jake`, CONSTRAINT `jake_ibfk_1` FOREIGN KEY (`parent`) REFERENCES `jake` (`id`))

mysql> delete from jake where name = 'jake iii';
Query OK, 1 row affected (0.02 sec)

mysql> delete from jake where name = 'jake jr';
Query OK, 1 row affected (0.02 sec)

and i still can't delete you because you still have a daughter:

mysql> delete from jake where name = 'jake';
ERROR 1451 (23000): Cannot delete or update a parent row:
a foreign key constraint fails
(`test/jake`, CONSTRAINT `jake_ibfk_1` FOREIGN KEY (`parent`) REFERENCES `jake` (`id`))

and finally, notice that i can't add jake iii back because his father doesn't exist:

mysql> insert jake (name, parent) values ('jake iii', 2);
ERROR 1452 (23000): Cannot add or update a child row:
a foreign key constraint fails
(`test/jake`, CONSTRAINT `jake_ibfk_1` FOREIGN KEY (`parent`) REFERENCES `jake` (`id`))

but i can add him as a his own "root" node:

mysql> insert jake (name, parent) values ('jake iii', NULL);
Query OK, 1 row affected (0.01 sec)

oh, and make sure you're using innodb tables because myisam does not (yet) support them.

[r937]

just to reinforce the ideas regarding the foreign key...

1. use FKs -- not doing so introduces the need for humongous blocks of code (whether in the app, in triggers, etc.) to accomplish the same effects as ON DELETE and ON UPDATE

2. use NULL for no parent, not 0 -- you will find, actually, that it's impossible to use 0 if the column is a foreign key

[Jake Arkinstall]

Wow.

I've never seen this kind of restraint before, it is exactly what I was looking for

Thanks guys!