cant create a MySql table

Question

I have created this table

CREATE TABLE `Overview` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ops` varchar(11) NOT NULL,
  `date` date NOT NULL,
  `title` text,
  `beneficiary_Institution` varchar(100) DEFAULT NULL,
  `description` longtext,
  `public_Expenditure_Budget` decimal(14,2) DEFAULT NULL,
  `payments` decimal(14,2) DEFAULT NULL,
  `completition_percent` int(11) DEFAULT NULL,
  `start` varchar(11) DEFAULT NULL,
  `finish` varchar(11) DEFAULT NULL,
  `sub_projects` int(11) DEFAULT NULL,
  `public_aid` decimal(14,2) DEFAULT NULL,
  `operational_programme_number` int(11) DEFAULT NULL,
  `operational_programme` varchar(100) DEFAULT NULL,
  `title_ops` text,
  `map_coordinates` varchar(15000) DEFAULT NULL,
  PRIMARY KEY (`id`,`ops`,`date`)
)ENGINE=InnoDB;

and i try to create a table that has a foreign key with that

CREATE TABLE `Proposal_Body` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `ops` VARCHAR(11) NULL,
  `title` VARCHAR(100) NULL,
  `representative` VARCHAR(45) NULL,
  `address` VARCHAR(45) NULL,
  `email` VARCHAR(45) NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_Proposal_Body_1_idx` (`ops` ASC),
  CONSTRAINT `fk_Proposal_Body_1`
    FOREIGN KEY (`ops`)
    REFERENCES `espanew`.`Overview` (`ops`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
)ENGINE=InnoDB;

This and any other attempt i tried gives me this error

ERROR 1005 (HY000): Can't create table 'Espa_Projects_Eng.Proposal_Body' (errno: 150)

I know that this is a foreign key problem, but i cant see the reason. This code is a comming from the mysql client, where i did the creation using the ui, didnt type the commands by hand, so it should work.

Thanks a lot

See http://stackoverflow.com/questions/825362/mysql-error-150-foreign-keys — Michał Perłakowski, Dec 18 '15 at 07:50
Have you checked that your first table i.e. "Overview" is present in DB? — Geeky Ninja, Dec 18 '15 at 08:00
You can refer points given [here](http://stackoverflow.com/questions/11045279/error-1005-hy000-cant-create-table-errno-150). This might help you. — Geeky Ninja, Dec 18 '15 at 08:04
I was so sure that i can have one foreign key pointing to a PK of a composite keys! using the same mysql, on my machine, it did work some time ago. dont know if i changed something since then, but now it appears that it doesnt allow me to do such a connection — Skaros Ilias, Dec 18 '15 at 09:10

score 1 · Accepted Answer · answered Dec 18 '15 at 08:07

Reading from the documentation:

If you re-create a table that was dropped, it must have a definition that conforms to the foreign key constraints referencing it. It must have the correct column names and types, and it must have indexes on the referenced keys, as stated earlier. If these are not satisfied, MySQL returns Error 1005 and refers to Error 150 in the error message, which means that a foreign key constraint was not correctly formed. Similarly, if an ALTER TABLE fails due to Error 150, this means that a foreign key definition would be incorrectly formed for the altered table.

As you can see your first table has this primary key:

PRIMARY KEY (`id`,`ops`,`date`)

but in the second table the foreign key is:

FOREIGN KEY (`ops`)

You have to correct this to make it work.

are you implying that i cant have a foreign key point to one of the PK of the refereed table? i have never heard that before — Skaros Ilias, Dec 18 '15 at 08:09
@SkarosIlias Of course, since in your Primary Key are involved more than one fields the Foreign Key has to match. — antoniodvr, Dec 18 '15 at 08:14

score 1 · Answer 2 · answered Dec 18 '15 at 08:12

Your Primary key is a composite key

PRIMARY KEY (id,ops,date)

And your foreign key is consist of only single column i.e.

FOREIGN KEY (ops)

Foreign keys have to match the primary/unique key they reference column for column. To remove this error either you need to add id and date to the Proposal_Body table or your first table Overview primary key is wrong and should just be (id).

cant create a MySql table

2 Answers2