Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    1

    Exclamation Unanswered: Implementing Foreign Keys in mysql control center

    Does anyone know how to add a constraint for using a foreign key to an already created table or even add it while creating a table.
    I can not seem to figure it out, i would appreciate any help on this.
    The only way is to actually write the SQL to add the constraint to the table.
    can it be done through the GUI?
    any experiments with that?


    Thanks,
    -Ash

  2. #2
    Join Date
    Mar 2004
    Location
    Germany
    Posts
    15

    Re: Implementing Foreign Keys in mysql control center

    Originally posted by ashrafmetry
    Does anyone know how to add a constraint for using a foreign key to an already created table or even add it while creating a table.
    I can not seem to figure it out, i would appreciate any help on this.
    The only way is to actually write the SQL to add the constraint to the table.
    can it be done through the GUI?
    any experiments with that?


    Thanks,
    -Ash

    deleted
    Last edited by archm; 03-03-04 at 10:36.

  3. #3
    Join Date
    Mar 2004
    Location
    Germany
    Posts
    15

    oops, i was wrong

    Starting from version 3.23.43b InnoDB features foreign key constraints. InnoDB is the first MySQL table type which allows you to define foreign key constraints to guard the integrity of your data.

    The syntax of a foreign key constraint definition in InnoDB:

    [CONSTRAINT [symbol]] FOREIGN KEY (index_col_name, ...)
    REFERENCES table_name (index_col_name, ...)
    [ON DELETE {CASCADE | SET NULL | NO ACTION
    | RESTRICT}]
    [ON UPDATE {CASCADE | SET NULL | NO ACTION
    | RESTRICT}]

    Both tables have to be InnoDB type, in the table there must be an INDEX where the foreign key columns are listed as the FIRST columns in the same order, and in the referenced table there must be an INDEX where the referenced columns are listed as the FIRST columns in the same order. InnoDB does not auto-create indexes on foreign keys or referenced keys: you have to create them explicitly. The indexes are needed for foreign key checks to be fast and not require a table scan.

    Corresponding columns in the foreign key and the referenced key must have similar internal datatypes inside InnoDB so that they can be compared without a type conversion. The size and the signedness of integer types has to be the same. The length of string types need not be the same. If you specify a SET NULL action, make sure you have not declared the columns in the child table NOT NULL.

    If MySQL gives the error number 1005 from a CREATE TABLE statement, and the error message string refers to errno 150, then the table creation failed because a foreign key constraint was not correctly formed. Similarly, if an ALTER TABLE fails and it refers to errno 150, that means a foreign key definition would be incorrectly formed for the altered table. Starting from version 4.0.13, you can use SHOW INNODB STATUS to look at a detailed explanation of the latest InnoDB foreign key error in the server.

    Starting from version 3.23.50, InnoDB does not check foreign key constraints on those foreign key or referenced key values which contain a NULL column.

    A deviation from SQL standards: if in the parent table there are several rows which have the same referenced key value, then InnoDB acts in foreign key checks like the other parent rows with the same key value would not exist. For example, if you have defined a RESTRICT type constraint, and there is a child row with several parent rows, InnoDB does not allow the deletion of any of those parent rows.

    Starting from version 3.23.50, you can also associate the ON DELETE CASCADE or ON DELETE SET NULL clause with the foreign key constraint. Corresponding ON UPDATE options are available starting from 4.0.8. If ON DELETE CASCADE is specified, and a row in the parent table is deleted, then InnoDB automatically deletes also all those rows in the child table whose foreign key values are equal to the referenced key value in the parent row. If ON DELETE SET NULL is specified, the child rows are automatically updated so that the columns in the foreign key are set to the SQL NULL value.

    A deviation from SQL standards: if ON UPDATE CASCADE or ON UPDATE SET NULL recurses to update the SAME TABLE it has already updated during the cascade, it acts like RESTRICT. This is to prevent infinite loops resulting from cascaded updates. A self-referential ON DELETE SET NULL, on the other hand, works starting from 4.0.13. A self-referential ON DELETE CASCADE has always worked.

    An example:

    CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB;
    CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id),
    FOREIGN KEY (parent_id) REFERENCES parent(id)
    ON DELETE SET NULL
    ) TYPE=INNODB;

    A complex example:

    CREATE TABLE product (category INT NOT NULL, id INT NOT NULL,
    price DECIMAL,
    PRIMARY KEY(category, id)) TYPE=INNODB;
    CREATE TABLE customer (id INT NOT NULL,
    PRIMARY KEY (id)) TYPE=INNODB;
    CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT,
    product_category INT NOT NULL,
    product_id INT NOT NULL,
    customer_id INT NOT NULL,
    PRIMARY KEY(no),
    INDEX (product_category, product_id),
    FOREIGN KEY (product_category, product_id)
    REFERENCES product(category, id)
    ON UPDATE CASCADE ON DELETE RESTRICT,
    INDEX (customer_id),
    FOREIGN KEY (customer_id)
    REFERENCES customer(id)) TYPE=INNODB;

    Starting from version 3.23.50, InnoDB allows you to add a new foreign key constraint to a table through

    ALTER TABLE yourtablename
    ADD [CONSTRAINT [symbol]] FOREIGN KEY (...) REFERENCES anothertablename(...)
    [on_delete_and_on_update_actions]

    Remember to create the required indexes first, though.

    Starting from version 4.0.13, InnoDB supports

    ALTER TABLE yourtablename DROP FOREIGN KEY internally_generated_foreign_key_id

    You have to use SHOW CREATE TABLE to determine the internally generated foreign key ID when you want to drop a foreign key. In InnoDB versions < 3.23.50 ALTER TABLE or CREATE INDEX should not be used in connection with tables which have foreign key constraints or which are referenced in foreign key constraints: Any ALTER TABLE removes all foreign key constraints defined for the table. You should not use ALTER TABLE to the referenced table either, but use DROP TABLE and CREATE TABLE to modify the schema. When MySQL does an ALTER TABLE it may internally use RENAME TABLE, and that will confuse the foreign key costraints which refer to the table. A CREATE INDEX statement is in MySQL processed as an ALTER TABLE, and these restrictions apply also to it.

    When doing foreign key checks, InnoDB sets shared row level locks on child or parent records it has to look at. InnoDB checks foreign key constraints immediately: the check is not deferred to transaction commit.

    If you want to ignore foreign key constraints during, for example for a LOAD DATA operation, you can do SET FOREIGN_KEY_CHECKS=0.

    To make it easier to reload dump files for tables that have foreign key relationships, mysqldump includes a statement in the dump output to set FOREIGN_KEY_CHECKS to 0. This avoids problems with tables having to be reloaded in a particular order when the dump is reloaded. mysqldump includes this statement as of MySQL 4.1.1. For earlier versions, you can disable the variable manually within mysql when loading the dump file like this:

    mysql> SET FOREIGN_KEY_CHECKS = 0;
    mysql> SOURCE dump_file_name;
    mysql> SET FOREIGN_KEY_CHECKS = 1;

    InnoDB allows you to drop any table even though that would break the foreign key constraints which reference the table. When you drop a table the constraints which were defined in its create statement are also dropped.

    If you re-create a table which was dropped, it has to have a definition which conforms to the foreign key constraints referencing it. It must have the right column names and types, and it must have indexes on the referenced keys, as stated above. If these are not satisfied, MySQL returns error number 1005 and refers to errno 150 in the error message string.

    Starting from version 3.23.50 InnoDB returns the foreign key definitions of a table when you call

    SHOW CREATE TABLE yourtablename

    Then also mysqldump produces correct definitions of tables to the dump file, and does not forget about the foreign keys.

    You can also list the foreign key constraints for a table T with

    SHOW TABLE STATUS FROM yourdatabasename LIKE 'T'

    The foreign key constraints are listed in the table comment of the output.

  4. #4
    Join Date
    Mar 2004
    Posts
    11

    THANK YOU

    Thanks so much... I've been working on this problem for days, and this reply solved it completely.

    In the ControlCenter, when you edit a table and view the 'Indexes' tab, you can see foreign keys. However, if you have more than one (ex: a foreign key on columns A and B) only one of the columns shows up in the pane for the foreign key. I was totally confused, but the SHOW TABLE STATUS FROM db LIKE 'table-name' stmt cleared things up -- both keys were created and are working fine, but just don't appear in the command center.

    Anyway, THANKS

  5. #5
    Join Date
    Mar 2004
    Location
    Germany
    Posts
    15

    Talking no problem

    go to mysql.com and download the my-sql-book in pdf.

    VERY useful!

Posting Permissions

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