SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Making a row always exist?

    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
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  2. #2
    SitePoint Evangelist
    Join Date
    Aug 2007
    Posts
    566
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Code:
    CREATE TRIGGER trigger_name
    or BEFORE DELETE ON table_name
    FOR EACH ROW
    BEGIN
      -- Do your check here
    END

  3. #3
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    change the parent rows from 0 to NULL and you can use FOREIGN KEYS. no triggers required.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  4. #4
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Ah, so you're suggesting not having a physical root node at all?
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  5. #5
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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:
    Code:
    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:
    Code:
    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:
    Code:
    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:
    Code:
    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:
    Code:
    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:
    Code:
    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.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Wow.

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

    Thanks guys!
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •