Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Posts
    110

    Unanswered: REFERENTIAL INTEGRITY & MYSQL

    Hello everybody


    I need to know whether MySQL supports referential integrity under MyISAM tables. I want to create two tables with a one-to-many relation. The one side of the relation should not be deleted if there exists at least one record in the many side.

    e.t.c.

    CREATE TABLE SPARE_PART (
    repair_id LONGINT UNSIGNED FOREIGN KEY REFERENCES REPAIR.repair_id,
    other fields);

    Syntax like that works under Oracle (not sure this is syntax-error free as I recalled it from memory, but the concept is the same). Can I write such SQL statements under MySQL using MyISAM tables?

    thx, in advance

    George Papadopoulos

  2. #2
    Join Date
    Mar 2004
    Posts
    480
    You need to use innodb tables for this.

  3. #3
    Join Date
    Jan 2003
    Location
    Paris, France
    Posts
    320
    Try to setup INNODB table space to use referential integrity

    http://www.lasso-developpeur.net/us/...tab=db&lang=us
    Olivier Miossec
    --
    http://www.lasso-developpeur.net/
    --

  4. #4
    Join Date
    May 2004
    Posts
    1
    Hi,
    In case that i have already build up the myISAM-type db, and inserted data in. I have searched that we can not convert mySIAM-type table to innoDB-type directly.

    I want to ask whether there is a solution for this?
    Can export insert script, delete existing db, create a fresh one, run the insert sql solve it?

    Thanks

  5. #5
    Join Date
    Apr 2004
    Location
    Europe->Sweden->Stockholm
    Posts
    71
    Quote Originally Posted by addo
    Hi,
    In case that i have already build up the myISAM-type db, and inserted data in. I have searched that we can not convert mySIAM-type table to innoDB-type directly.

    I want to ask whether there is a solution for this?
    Can export insert script, delete existing db, create a fresh one, run the insert sql solve it?

    Thanks
    Either I don't understand this right, or the info you have read is seriously wrong. To me it seems that what you want to do is set up InnoDB and then

    ALTER TABLE tab ENGINE=InnoDB;

  6. #6
    Join Date
    Dec 2003
    Location
    Houston, TX
    Posts
    21
    Quote Originally Posted by omiossec
    Try to setup INNODB table space to use referential integrity

    http://www.lasso-developpeur.net/us/...tab=db&lang=us
    Your informaiton is out of date.

    InnoDB is included in binary distributions by default as of MySQL 4.0. For information about InnoDB support in MySQL 3.23, see section 16.3 InnoDB in MySQL 3.23.
    http://dev.mysql.com/doc/mysql/en/InnoDB_overview.html

Posting Permissions

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