0

I created two tables which are customer_tbl and pick_up_tbl and im trying to create the third table that its field with reference from the first table, but end up with this:

errno: 150 "Foreign key constraint is incorrectly formed"

CREATE TABLE pic_rooster_tbl (
    pic_ros_id int not null,
    pic_id int not null,
    c_id int not null,
    name varchar(255) not null,
    dat date,
    PRIMARY KEY (pic_ros_id),
    FOREIGN KEY (pic_id) REFERENCES Pickup_tbl (pic_id),
    FOREIGN KEY (c_id) REFERENCES customer_tbl (c_id),
    FOREIGN KEY (dat) REFERENCES Pickup_tbl (dat)
);
GMB
  • 216,147
  • 25
  • 84
  • 135
Kelz Biggie
  • 1
  • 1
  • 1
    You would need to show us the `create table` statement for `customer_tbl` and `pickup_tbl`. – GMB Jan 23 '20 at 22:46
  • Why are you using a `date` column as a forign key? You already have a foreign key to the ID in that table. – Barmar Jan 23 '20 at 22:46
  • The problem is probably that you don't have an index on `Pickup_tbl.dat`. – Barmar Jan 23 '20 at 22:46
  • If the date is already in Pickup_tbl, you shouldn't duplicate it here. – Barmar Jan 23 '20 at 22:47
  • You can get more detailed information on the cause of the foreign key error by running `SHOW ENGINE INNODB STATUS\G` and looking for the section "LATEST FOREIGN KEY ERROR" – Bill Karwin Jan 23 '20 at 23:34

0 Answers0