Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2009
    Posts
    1

    Unanswered: Foreign key contraints

    I have set up 3 tables that have foreign keys and primary keys. I want to delete these 3 tables but I cannot delete any of them because:
    mysql> DROP TABLE POST, TOPIC, USER;
    ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

    I get that certain tables cant be deleted because other tables have keys from that table, but if I try to delete all 3 tables in the equation then why do I still run into the same problem? How can I go about deleting these 3 tables.

    mysql> DESCRIBE USER;
    +------------+-------------+------+-----+---------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +------------+-------------+------+-----+---------+-------+
    | U_ID | varchar(16) | NO | PRI | NULL | |
    | U_PASSWORD | varchar(16) | YES | | NULL | |
    | T_ID | int(11) | NO | MUL | NULL | |
    +------------+-------------+------+-----+---------+-------+
    3 rows in set (0.02 sec)

    mysql> DESCRIBE TOPIC;
    +---------+-------------+------+-----+---------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +---------+-------------+------+-----+---------+-------+
    | T_ID | int(11) | NO | PRI | NULL | |
    | POST_ID | int(11) | YES | MUL | NULL | |
    | T_TEXT | text | YES | | NULL | |
    | U_ID | varchar(16) | NO | MUL | NULL | |
    +---------+-------------+------+-----+---------+-------+
    4 rows in set (0.01 sec)

    mysql> DESCRIBE POST;
    +-----------------+-------------+------+-----+---------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +-----------------+-------------+------+-----+---------+-------+
    | POST_ID | int(11) | NO | PRI | NULL | |
    | U_ID | varchar(16) | NO | | NULL | |
    | POST_MESSAGE | text | YES | | NULL | |
    | POST_SUBJECT | varchar(35) | YES | | NULL | |
    | T_ID | int(11) | NO | | NULL | |
    | POST_COMESAFTER | int(11) | YES | | NULL | |
    +-----------------+-------------+------+-----+---------+-------+

    ERD: http://img14.imageshack.us/img14/8690/41633351.gif
    Youll see a PROFILE tables exists. It was there but it is the only one that would drop.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mevets View Post
    How can I go about deleting these 3 tables.
    you gots to drop 'em in the correct order

    i can't tell from your column names what the FKs are

    instead of DESCRIBE, please do a SHOW CREATE TABLE for each table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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