Professional Web Applications Themes

newbie: How to use foreign keys in MySql? - MySQL

I'm using MySql 4.0.27-standard-log, administered through phpMyAdmin 2.9.0.2 What's up with FKs? I've read the doentation at http://dev.mysql.com/doc/refman/5.0/en/storage-engines.html, but still unsure if I can use FKs with ISAM (non-transactional) tables or only with InnoDB (transaction-safe) tables. Currently, I'm using ISAM and see no way to identify or create an FK with phpMyAdmin. Does this mean I cannot use FKs with the ISAM storage engine? What about enforcing referential integrity, or using cascading deletes? Thanks in advance....

  1. #1

    Default newbie: How to use foreign keys in MySql?

    I'm using MySql 4.0.27-standard-log, administered through phpMyAdmin 2.9.0.2

    What's up with FKs?

    I've read the doentation at
    http://dev.mysql.com/doc/refman/5.0/en/storage-engines.html, but still unsure if
    I can use FKs with ISAM (non-transactional) tables or only with InnoDB
    (transaction-safe) tables.

    Currently, I'm using ISAM and see no way to identify or create an FK with
    phpMyAdmin.

    Does this mean I cannot use FKs with the ISAM storage engine? What about
    enforcing referential integrity, or using cascading deletes?

    Thanks in advance.

    deko Guest

  2. #2

    Default Re: newbie: How to use foreign keys in MySql?

    deko wrote: 

    You can set up foreign keys with MyISAM (see "Relationships" in
    PhPMyAdmin on the table structure page). But they really won't do you
    any good as they are ignored.

    If you don't want them to be ignored you need to use InnoDB.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  3. #3

    Default Re: newbie: How to use foreign keys in MySql?

    > You can set up foreign keys with MyISAM (see "Relationships" in PhPMyAdmin on 

    Are they ignored because they don't serve any purpose unless associated with a
    transaction - such as cascading deletes or enforcing referential integrity -
    which require a database engine that supports transactions (i.e. InnoDB)?

    deko Guest

  4. #4

    Default Re: newbie: How to use foreign keys in MySql?

    deko wrote: 
    >
    > Are they ignored because they don't serve any purpose unless associated
    > with a transaction - such as cascading deletes or enforcing referential
    > integrity - which require a database engine that supports transactions
    > (i.e. InnoDB)?[/ref]

    Cascading deletes and enforced referential integrity have nothing to do
    with transactions. They can be implemented without transaction support.

    It just so happens that MyISAM does not support either.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  5. #5

    Default Re: newbie: How to use foreign keys in MySql?

    >>> You can set up foreign keys with MyISAM (see "Relationships" in PhPMyAdmin 
    >>
    >> Are they ignored because they don't serve any purpose unless associated with
    >> a transaction - such as cascading deletes or enforcing referential
    >> integrity - which require a database engine that supports transactions (i.e.
    >> InnoDB)?[/ref]
    >
    > Cascading deletes and enforced referential integrity have nothing to do with
    > transactions. They can be implemented without transaction support.
    >
    > It just so happens that MyISAM does not support either.[/ref]

    If there's no support for cascading deletes or enforced referential integrity
    then there's no reason to use foreign keys. Why else would I want a foreign
    key? The point is that MySql/MyISAM does not support foreign keys, and thus you
    cannot use referential constraints.

    deko Guest

  6. #6

    Default Re: newbie: How to use foreign keys in MySql?

    deko wrote: 
    >>
    >> Cascading deletes and enforced referential integrity have nothing to
    >> do with transactions. They can be implemented without transaction
    >> support.
    >>
    >> It just so happens that MyISAM does not support either.[/ref]
    >
    > If there's no support for cascading deletes or enforced referential
    > integrity then there's no reason to use foreign keys. Why else would I
    > want a foreign key? The point is that MySql/MyISAM does not support
    > foreign keys, and thus you cannot use referential constraints.[/ref]

    No, you cannot depend on referential constraints when using MyISAM. But
    that doesn't mean it requires transactions to support referential
    integrity. The two are independent.

    You can define them - that is your statements won't fail just because
    you use MyISAM. But I agree there is little use for foreign keys other
    than for doentation purposes.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  7. #7

    Default Re: newbie: How to use foreign keys in MySql?

    >>>>> You can set up foreign keys with MyISAM (see "Relationships" in PhPMyAdmin 
    >>
    >> If there's no support for cascading deletes or enforced referential integrity
    >> then there's no reason to use foreign keys. Why else would I want a foreign
    >> key? The point is that MySql/MyISAM does not support foreign keys, and thus
    >> you cannot use referential constraints.[/ref]
    >
    > No, you cannot depend on referential constraints when using MyISAM. But that
    > doesn't mean it requires transactions to support referential integrity. The
    > two are independent.
    >
    > You can define them - that is your statements won't fail just because you use
    > MyISAM. But I agree there is little use for foreign keys other than for
    > doentation purposes.[/ref]

    Thanks for the clarification. When you say "for doentation purposes" do you
    mean that someone might indicate (in a table definition, for example) a certain
    field as an FK - as a suggested constraint - if the database engine allowed for
    it? I've seen some MySql table definitions that indicate FKs... this added to
    my confusion.

    In any case, when using MySql(ISAM) I assume I must rely on application logic to
    perform operations that would otherwise be the responsibility of
    constraints/relationships... not to be confused with transactions...

    deko Guest

  8. #8

    Default Re: newbie: How to use foreign keys in MySql?

    deko wrote: 
    >>
    >> No, you cannot depend on referential constraints when using MyISAM.
    >> But that doesn't mean it requires transactions to support referential
    >> integrity. The two are independent.
    >>
    >> You can define them - that is your statements won't fail just because
    >> you use MyISAM. But I agree there is little use for foreign keys
    >> other than for doentation purposes.[/ref]
    >
    > Thanks for the clarification. When you say "for doentation purposes"
    > do you mean that someone might indicate (in a table definition, for
    > example) a certain field as an FK - as a suggested constraint - if the
    > database engine allowed for it? I've seen some MySql table definitions
    > that indicate FKs... this added to my confusion.
    >
    > In any case, when using MySql(ISAM) I assume I must rely on application
    > logic to perform operations that would otherwise be the responsibility
    > of constraints/relationships... not to be confused with transactions...
    >[/ref]

    Yes, you must rely on application logic when using MyISAM. InnoDB is
    better in this respect - but slower.

    And I always indicate the FK constraints in my tables, even if the
    hosting company only allows MyISAM tables (a lot of shared hosts are
    that way). But if they can migrate to InnoDB, they can just
    export/import the tables (as long as the tables are consistent, anyway).

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

Similar Threads

  1. Foreign keys tut
    By frizzle in forum MySQL
    Replies: 2
    Last Post: July 18th, 05:45 PM
  2. foreign keys and indexes
    By Bill in forum Informix
    Replies: 3
    Last Post: February 9th, 05:00 AM
  3. foreign keys
    By Sybrand Bakker in forum Microsoft Access
    Replies: 2
    Last Post: July 25th, 10:04 PM
  4. DMO + Foreign Keys
    By oj in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 8th, 05:58 AM
  5. SQL DMO + Foreign Keys
    By Steven Campbell in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 4th, 09:55 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139