Why do I get an error while adding a foreign key constraint to a table?

1 Views

Table wp_users wordpress have this syntax: 

CREATE TABLE `wp_users` (
  `ID` bigint(20) unsigned NOT NULL auto_increment,
  `user_login` varchar(60) NOT NULL default '',
  `user_pass` varchar(64) NOT NULL default '',
  `user_nicename` varchar(50) NOT NULL default '',
  `user_email` varchar(100) NOT NULL default '',
  `user_url` varchar(100) NOT NULL default '',
  `user_registered` datetime NOT NULL default '0000-00-00 00:00:00',
  `user_activation_key` varchar(60) NOT NULL default '',
  `user_status` int(11) NOT NULL default '0',
  `display_name` varchar(250) NOT NULL default '',
  PRIMARY KEY  (`ID`),
  KEY `user_login_key` (`user_login`),
  KEY `user_nicename` (`user_nicename`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;

And I wanted to integrate a system already implemented for wordpress but I’m having problems with foreign keys to connect the table wp_users.

Read More

I have this table: 

CREATE TABLE attach (
  attach_id int unsigned NOT NULL auto_increment,     
  email varchar(60) NOT NULL default '',
  PRIMARY KEY  (attach_id),
  FOREIGN KEY (email) REFERENCES wp_users(user_login_key) 
  ON UPDATE CASCADE ON DELETE CASCADE
)Engine=INNODB;

I wanted to connect to user_email from wp_users, but user_login and user_email will be the same.. And it’s totally impossible to define attach_id as a FK to ID from wp_users, due to other situations in other tables.

When I submit gives error # 1005

Post Views: 1

Related posts

Leave a Reply

2 comments

  1. In your second create statement for the table CREATE TABLE attach, you have the foreign key reference like this 

    FOREIGN KEY (email) REFERENCES wp_users(user_login_key)

    In the above statement, the name user_login_key references to a constraint in the table wp_users. While creating foreign constraints, you need to reference a field name in another table that you would like to create the relation and not the constraint name. Change the reference statement in the CREATE TABLE attach as following to fix the issue. In this statement user_login refers to the column of the same name

    FOREIGN KEY (email) REFERENCES wp_users(user_login)

    Click here to view that both tables are created successfully in SQL Fiddle.

    Script:

        CREATE TABLE attach 
(
        attach_id   int unsigned NOT NULL auto_increment
    ,   email varchar(60) NOT NULL default ''
    ,   PRIMARY KEY  (attach_id)
    ,   FOREIGN KEY (email) REFERENCES wp_users(user_login) 
        ON UPDATE CASCADE ON DELETE CASCADE
)Engine=INNODB;

  2. PLEASE CHECK YOUR PARENT AND CHILD COLUMN DATA TYPES MUST BE SAME OR NOT?

    For Example:

    in your parent table have column like 

      Fb_user_id INT(4) NOT NULL,

    in your child table also u must give the same data type like
    ID INT(4) NOT NULL,

    don’t add any extra attributes like
    unsigned
    like that.