CODEWITHSUNDEEP
mysqlsqlforeign-keysforeign-key-relationship
MikeG
MikeG

Reputation: 1275

Foreign Key not working: Error code 1005, SQL state HY000: Can't create table

I have two tables I have created and I'm adding the foreign key constraint after the fact.

The two tables are defined as such:

CREATE TABLE `user` (
  `user_id` int(11) NOT NULL auto_increment,
  `user_ad_id` varchar(500) default NULL,
  `user_name` varchar(100) NOT NULL,
  `login_id` varchar(100) default NULL,
  `email` varchar(256) NOT NULL,
  `personal_config` int(10) NOT NULL,
  PRIMARY KEY  (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

and

CREATE TABLE IF NOT EXISTS personal_config (
    config_id INT(10) NOT NULL AUTO_INCREMENT,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    configuration TEXT(25600) NOT NULL,
    PRIMARY KEY (config_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE personal_config ADD CONSTRAINT personal_config_fk_user FOREIGN KEY 
(config_id) REFERENCES user(personal_config);

And I keep getting the same error but can't figure it out. I've searched all the related threads to this.

Upvotes: 0

Views: 2221

Answers (3)

karthik
karthik

Reputation: 1

if you set your user table field personal_config is primary key then it is possible to execute

CREATE TABLE IF NOT EXISTS personal_config (
    config_id INT(10) NOT NULL AUTO_INCREMENT,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    configuration TEXT(25600) NOT NULL,
    PRIMARY KEY (config_id), FOREIGN KEY 
(config_id) REFERENCES user(personal_config)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

Upvotes: 0

Michael Berkowski
Michael Berkowski

Reputation: 270637

Your ALTER TABLE statement is backward. Since personal_config.config_id is an auto_increment primary key, the foreign key should be defined in the users table against personal_config, not in personal_config against the users table.

ALTER TABLE users ADD CONSTRAINT user_fk_personal_config 
   FOREIGN KEY (personal_config)
   REFERENCES personal_config(config_id);

Upvotes: 1

Madbreaks
Madbreaks

Reputation: 19539

Your FK config_id can't be an autoincrement field, that doesn't make much sense right? That field reflects a value in the foreign table, it cannot be set arbitrarily in the local table.

I think this is what you want:

ALTER TABLE user ADD CONSTRAINT personal_config_fk_user FOREIGN KEY (personal_config) REFERENCES personal_config(config_id);

Upvotes: 2

Related Questions