Results 1 to 5 of 5

Thread: InnoDB

  1. #1
    Join Date
    Feb 2005
    Location
    Sweden
    Posts
    45

    Unanswered: InnoDB

    Hi,

    What is the difference between InnoDB and MyISAM?

    My situation is that I have two tables where I join them. What I need to do is when I delete a record on the "primary" (do you call it that?) table, the other table's records, that are linked to the primary tables record, should be deleted aswell.

    I've read in another thread that you can do that in MySQL, but only on InnoDB tables. I have tried ON DELETE CASCADE, but I get the message: Error creating the foreign key.
    Check these information before creating a foreign key
    -both tables should have an Index for the columns specified
    -the size and the signedness of integer types has to be the same
    -defined ON DELETE/ON UPDATE SET TO NULL but the column is defined as NOT NULL.

    I'm using DBManager Professional 2.3.0, freeware edition.

    My tables:
    CREATE TABLE `tblindexbyobject`
    (
    `indexId` INTEGER (255) UNSIGNED NOT NULL DEFAULT 0,
    `adId` INTEGER (255) UNSIGNED NOT NULL DEFAULT 0
    ) TYPE=InnoDB

    CREATE TABLE `tblobject`
    (
    `adId` INTEGER (255) UNSIGNED NOT NULL AUTO_INCREMENT ,
    `adSubject` varchar (255),
    `adBody` longtext NOT NULL ,
    `adDate` DATE,
    PRIMARY KEY (adId)
    ) TYPE=InnoDB

    Hope you can help me.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    first of all, you have to declare an index on the adid column in the tblindexbyobject table

    then you have to declare a foreign key in the tblindexbyobject table on adid referencing table tblobject (adid) with ON DELETE CASCADE

    by the way, did you declare your integers as INTEGER(255), or was it DBManager Professional 2.3.0? because 255 is silly, INTEGER only goes up to approx 2 billion (10 digits)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2005
    Location
    Sweden
    Posts
    45
    Thank you, it worked!

    And no, it was silly me who thought that I could set integer to 255.

    Can I now use rollback with those InnoDB tables? How do I do that?

    What is the difference between InnoDB and MyISAM?

    //M

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, can't help you any further, i've never done rollback, and i don't know any mysql internals
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2005
    Location
    Sweden
    Posts
    45

    Thumbs up

    You have allready helped me enough.
    I'm on to it by searching on google.

    //M

Posting Permissions

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