Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2003

    Unanswered: Problems with foreign keys

    I'm creating a database for a class project, and I have a 'User' table with a column called 'email', which is the primary key for the table. Oh yeah, and all my tables are InnoDB tables.

    I also have a few tables with foreign keys that refer to this field of the 'User' table.

    I've created all my tables and I'm now adding the FOREIGN KEY constraints.

    So, I typed the following into phpMyAdmin:

    But I get the following error whenever I try to add a foreign key that references 'User' (doesn't matter what table the foreign key is in...this happens for all of them:

    You have an error in your SQL syntax._ Check the manual that corresponds to your MySQL server version for the right syntax to use near 'User( email )_ ON_ DELETE_ CASCADE' at line 1
    This was odd, I thought...since all the other foreign keys were created just fine.

    First, after changing the name of the table from User to GenUser, I stopped getting that error, but I got a different error. This one said basically that I had no index for 'user_email'...which is odd because 'user_email is part of the primary key for 'Rating'.

    Well here's what I had to write in order for both foreign keys to stick:

    CREATE TABLE `Rating` (
    _`score` int(1) NOT NULL default '0',
    _`article_id` int(11) NOT NULL default '0',
    _`user_email` varchar(128) NOT NULL default '',
    _PRIMARY KEY _(`article_id`,`user_email`),
    _KEY `user_email` (`user_email`),
    _CONSTRAINT `0_111` FOREIGN KEY (`user_email`) REFERENCES `GenUser` (`email`) ON DELETE NO ACTION,
    _CONSTRAINT `0_94` FOREIGN KEY (`article_id`) REFERENCES `Article` (`id`) ON DELETE CASCADE
    ) TYPE=InnoDB
    Notice how the second foreign key is fine without an extra 'KEY', but in order to get the 'user_email' foreign key to work, I had to add that 'KEY' after the 'PRIMARY KEY'...

    Why is that? How come one would work, but not the other? Why is it that I can't have a table called 'User'

  2. #2
    Join Date
    Nov 2003
    You cannot make references from one table to another
    unless there is an index in BOTH tables on the fields
    you are referencing. Using 'PRIMARY KEY' and 'KEY'
    made indexes for your user_email and article_id
    fields so then those fields can be used to reference another
    I usually use the 'INDEX' command because I don't
    like to add extra key fields.
    INDEX user_email_idx (user_email), ...
    (this would be added in the CREATE TABLE Rating block.)

    The reason why the PRIMARY KEY index didn't work
    for field user_email is because user_email is the secondary
    key. An index with user_email as the primary key is
    required before you can use the field for referential integrity.

    I'm not sure, but I think MySQL is confusing your 'USER'
    table with the 'USER' table in database 'mysql'.
    At the mysql command line, try typing:
    use mysql;
    show tables;
    And you will see that MySQL already has created its own
    table called 'USERS'. It's best not to duplicate system
    tables, even if they should be in their separate name
    space (different databases).


Posting Permissions

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