SQL Code works fine on an online compiler but fails when compiled by MariaDB in Linux Terminal (errno: 150)

Question

Searched through the forums, tried a bunch of solutions, but nothing seems to be working. Below is a small snippet of the code and the online compiler I used.

I get (errno: 150 "Foreign Key constraint is incorrectly formed"). Do note, this error appears through out all of the tables in the code, whenever I try to add a foreign key.

Online Complier: https://paiza.io/en/languages/mysql

Code:

-- CREATING AND INSERTING VALUES INTO THE BOOKINGS TABLE WITHOUT FK--
create table Bookings(
Booking_ID varchar(9) NOT NULL,
Client_ID varchar(6) NOT NULL,
PT_ID varchar(4) NOT NULL,
Booking_Date Date NOT NULL, 
Start_Time time NOT NULL,
End_Time time NOT NULL,
Focus_ID varchar(3) NOT NULL,
Staff_ID varchar(4) NOT NULL,
PRIMARY KEY (Booking_ID)
);

INSERT INTO Bookings
VALUES
('B00000001','C00001','T001','2020-01-1','19:30:00','20:15:00','F01','S002'),
('B00000002','C00023','T001','2020-01-1','09:00:00','09:30:00','F02','S001'),
('B00000007','C00156','T003','2020-01-1','10:00:00','11:00:00','F04','S003');


-- CREATING AND INSERTING VALUES INTO THE CLIENT TABLE --
create table Client(
Client_ID varchar(6) NOT NULL,
Client_Name varchar(20) NOT NULL,
Height_cm decimal(5,2) NOT NULL,
Weight_kg decimal(6,2) NOT NULL,
Ph_Num varchar(14) NOT NULL,
PRIMARY KEY (Client_ID),
FOREIGN KEY (Client_ID) 
    REFERENCES Bookings(Client_ID)
);

-- ALTERING BOOKINGS TABLE WITH FOREIGN KEYS --

ALTER TABLE Bookings
ADD
FOREIGN KEY (Client_ID) 
    REFERENCES Client(Client_ID);

As I mentioned in the title, the code works fine online, but fails when compiled via MariaDB on Linux Terminal.

MariaDB Server Version is 10.3.17-MariaDB

What am I doing wrong? any help would be greatly appreciated.

you'll have to forgive me, as I am quite new to sql, but what is a storage engine and how do I find it? — EltoCode, Jan 31 '20 at 19:10
You do not need to create the foreign key on both tables, just the one that references the Clients table from the Bookings table. A foreign key references the primary key of another table. Therefore, you're foreign key from Clients to Bookings is not valid, since Client_ID is not a primary key on Bookings. — derek.wolfe, Jan 31 '20 at 20:12
@drakin8564 This was what fixed it thanks! I always thought it was weird that both tables needed a foreign key! — EltoCode, Feb 01 '20 at 15:33
150: Change the order of declaring the tables _OR_ disable before creating the tables, then reenable _OR_ apply all the FKs after creating all the tables. — Rick James, Feb 07 '20 at 06:43

Beach Chicken · Answer 1 · 2020-01-31T19:22:29.483

Alright, first thing I said about the storage engine is that not all storage engines support foreign key constraints.

A storage engine is a system that manages the table and data, there multiple storage engines available like: MyISAM or InnoDb. (From MySQL 5.6 InnoDB is default)

Each storage engine has it own features and unique characteristics that can be used in specific situations. Most of the time you dont "care" much about and storage engine, unless you want to use some specific features.

If you are not sure which storage engine to chose, just stick with the default and use InnoDB

For more information about storage engines, check the MySQL reference manual. (I would strongly suggest you to if you want to learn and understand)

https://dev.mysql.com/doc/refman/8.0/en/innodb-introduction.html https://dev.mysql.com/doc/refman/8.0/en/myisam-storage-engine.html

I think the issue is that column Client_ID on Bookings is not a indexed field. If I change the table definition and add a INDEX constraint to it, it all works.

create table Bookings( Booking_ID varchar(9) NOT NULL, Client_ID varchar(6) NOT NULL,  PT_ID varchar(4) NOT NULL, Booking_Date Date NOT NULL, Start_Time time NOT NULL, End_Time time NOT NULL, Focus_ID varchar(3) NOT NULL, Staff_ID varchar(4) NOT NULL,  PRIMARY KEY (Booking_ID), INDEX (Client_ID) );

INSERT INTO Bookings
VALUES
('B00000001','C00001','T001','2020-01-1','19:30:00','20:15:00','F01','S002'),
('B00000002','C00023','T001','2020-01-1','09:00:00','09:30:00','F02','S001'),
('B00000007','C00156','T003','2020-01-1','10:00:00','11:00:00','F04','S003');

create table Client(
Client_ID varchar(6) NOT NULL,
Client_Name varchar(20) NOT NULL,
Height_cm decimal(5,2) NOT NULL,
Weight_kg decimal(6,2) NOT NULL,
Ph_Num varchar(14) NOT NULL,
PRIMARY KEY (Client_ID),
FOREIGN KEY (Client_ID) 
    REFERENCES Bookings(Client_ID)
);

score 1 · Answer 2 · answered Jan 31 '20 at 19:34

If you append e.g. show create table Bookings; at the end of the script you get:

CREATE TABLE `Bookings` ([…]) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4
                              ^^^^^^^^^^^^^

That means that https://paiza.io is configured to use MyISAM as default engine so that's what you get when you don't set any yourself. Make sure to set ENGINE=InnoDB in your code so you don't get foreign keys ignored.

score 0 · Answer 3 · answered Jan 31 '20 at 19:34

Previous answer is correct.

MariaDB requires the referenced column be PK or indexed (preferably unique indexed).

If adding the index does not help solve the issue, then check your default storage engine to see if it is set to MyISAM and not InnoDB.

show global variables like '%storage%';

SQL Code works fine on an online compiler but fails when compiled by MariaDB in Linux Terminal (errno: 150)

3 Answers3