Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Posts
    65

    Unanswered: How to get MSSQL style relationships going?

    I figured out how to configure phpMyAdmin so I can use the "relation view" to relate tables together via PK/FK. What I can't figure out is how to set up cascade deletes, so if, for example, there's a table full of dog owners, and a table full of dogs, and I delete an owner, I want all the dogs that person owned to also get deleted.

    How do I do this using phpMyAdmin? (or if I have to get down & dirty with straight SQL, I can handle that...)

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    do a SHOW CREATE TABLE on each of your two tables, paste that here, and we can supply the necessary SQL to add your relationship
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2004
    Posts
    65
    phpMyAdmin's SQL is cutting off the text result.. so I did an export instead

    CREATE TABLE `words` (
    `word_id` int(10) unsigned NOT NULL auto_increment,
    `word` varchar(30) NOT NULL,
    PRIMARY KEY (`word_id`),
    KEY `word` (`word`)
    ) ENGINE=MyISAM

    CREATE TABLE `answers` (
    `word_id` int(10) unsigned NOT NULL,
    `matters` tinyint(3) unsigned NOT NULL,
    `feeling` tinyint(3) unsigned NOT NULL
    ) ENGINE=MyISAM

    Is that enough info? So words contains a list of words.. and answers (which is lacking a primary key, I know) has a FK word_id. I'd like it so when I delete a word, all the answers that refer to it are deleted too.

  4. #4
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Yes that's plenty.
    What you need to realise is that MySQL only supports Foreign Keys using the InnoDB engine type. This is where your problem is occuring. Specify those tables as follows :

    CREATE TABLE `words` (
    `word_id` int(10) unsigned NOT NULL auto_increment,
    `word` varchar(30) NOT NULL,
    PRIMARY KEY (`word_id`),
    KEY `word` (`word`)
    ) ENGINE=InnoDB

    CREATE TABLE `answers` (
    `word_id` int(10) unsigned NOT NULL,
    `matters` tinyint(3) unsigned NOT NULL,
    `feeling` tinyint(3) unsigned NOT NULL
    FOREIGN KEY (`word_id`) REFERENCES words(`word_id`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    unless you have to use phpmyadmin have you though of using MySQL Administrator form MySQL.......
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jan 2004
    Posts
    65
    Awesome, thanks guys

Posting Permissions

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