CODEWITHSUNDEEP
mysqlruby-on-rails
mark deeprotye
mark deeprotye

Reputation: 35

mysql alter table FOREIGN KEY!

(I using workbench) i have table questions with id, user_id, text and table users with fields id, name

I need to relate this 2 tables!

I write following:

ALTER TABLE `mydb_development`.`questions`
ADD CONSTRAINT fk_QueUsers_1
FOREIGN KEY (`user_id`)
REFERENCES `mydb_development`.`users`(`id`);

but i get:

ERROR 1046: No database selected
SQL Statement:
ALTER TABLE  `questions` 
ADD FOREIGN KEY (`user_id`) REFERENCES `users`(`id`)

ERROR: Error when running failback script. Details follow.

ERROR 1046: No database selected
SQL Statement:
CREATE TABLE `questions` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) unsigned NOT NULL,
  `text` text NOT NULL,
  `security_token` varchar(40) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=373 DEFAULT CHARSET=utf8

ERROR 1046: No database selected
SQL Statement:
ALTER TABLE  `questions` 
ADD FOREIGN KEY (`user_id`) REFERENCES `users`(`id`)

ERROR: Error when running failback script. Details follow.

.....................

EDIT: I tried to do:

USE `mydb_development`;

ALTER TABLE  `mydb_development`.`questions`

ADD CONSTRAINT `fk_QueUsers_1`

FOREIGN KEY (`user_id`)

REFERENCES  `mydb_development`.`users`(`id`);

and i get error:

Error Code: 1005
Can't create table 'survey_development.#sql-4ad_45' (errno: 150)

DOnt understand:S

EDIT:

my user table:

DROP TABLE IF EXISTS `mydb_development`.`users`;
CREATE TABLE  `mydb_development`.`users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

Upvotes: 2

Views: 3102

Answers (4)

sameera207
sameera207

Reputation: 16629

This seems to be mysql bug:

Read http://forums.mysql.com/read.php?22,19755,19755

Try to index the 'user_id' and run the script again.

Upvotes: 2

shevski
shevski

Reputation: 1022

enter:

use `mydb_development`;

change the constraint name 'user_id' to something else like 'fk_user_id'

Upvotes: 0

James C
James C

Reputation: 14169

First of all you want to get rid of the ERROR 1046: No database selected errors.

To do this make sure that you either:

  • Select a database with USE mydb_development;
  • Modify the ALTER/CREATE statements to include the db. E.g. ALTER TABLE mydb_development.questions

Upvotes: 0

Ben Rowe
Ben Rowe

Reputation: 28721

Try selecting your database to see if it makes any difference

USE `mydb_development`;

Upvotes: 0

Related Questions