CODEWITHSUNDEEP
mysqlphpmyadmin
WpDoe
WpDoe

Reputation: 474

Foreign key cannot be created id MySQL: missing index on column(s)

I have a few foreign keys set up, however phpMyAdmin would not let me to create one more. Here are table in question:

Groups Table

id
name
address


Tasks Table

id
group_id
name

I need a foreign key on group_id in Tasks Table, but when I try to create in it provides the following error: Missing index on column(s). If I add a unique constrain on group_id I am then able to create the foreign key, but the ralation then becomes One to One, which is not the expected result.

Following are table create statements:

CREATE TABLE IF NOT EXISTS `groups` (
  `id` int(11) NOT NULL,
  `name` varchar(256) NOT NULL,
  `address` varchar(256) NOT NULL,
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

ALTER TABLE `groups`
  ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `id` (`id`), ADD UNIQUE KEY `id_2` (`id`);


CREATE TABLE IF NOT EXISTS `tasks` (
  `id` int(11) NOT NULL,
  `group_id` int(11) NOT NULL,
  `name` varchar(256) NOT NULL,
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

ALTER TABLE `fixed_tasks`
  ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `id` (`id`);

Any help or guidance is much appreciated.

Upvotes: 2

Views: 14478

Answers (1)

Chong Tang
Chong Tang

Reputation: 2146

The following code can create two tables with relative foreign keys:

CREATE TABLE `Groups` (
  `id` INT NOT NULL,
  `name` VARCHAR(45) NULL,
  `address` VARCHAR(45) NULL,
  PRIMARY KEY (`id`)
);


CREATE TABLE `Tasks` (
  `id` INT NOT NULL,
  `group_id` INT NULL,
  `name` VARCHAR(45) NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `group_id`
    FOREIGN KEY (`group_id`)
    REFERENCES `Groups` (`id`)
);

Upvotes: 2

Related Questions