I'm developing a DB with MySQL. I'm trying to add some tables.
CREATE TABLE Services (
srv_id int NOT NULL PRIMARY KEY,
srv_description char(100) NOT NULL
);
CREATE TABLE Categories(
ctg_id INT NOT NULL PRIMARY KEY,
ctg_description char(25) NOT NULL
);
CREATE TABLE Bathhouses(
bth_id int NOT NULL PRIMARY KEY,
bth_name char(25) NOT NULL UNIQUE,
bth_start_date date NOT NULL CHECK (MONTH(bth_start_date) > 3),
bth_end_date date NOT NULL CHECK (MONTH(bth_end_date) < 11),
bth_address char(25) NOT NULL,
ctg_id INT NOT NULL,
FOREIGN KEY (ctg_id) REFERENCES Categories(ctg_id)
);
CREATE TABLE Customers(
cus_id int NOT NULL PRIMARY KEY,
cus_name char(25) NOT NULL,
cus_surname char(25) NOT NULL,
cus_birthdate date NOT NULL,
cus_sexgender ENUM('male ', 'female', 'other')
);
CREATE TABLE CustomersServicesUses (
cussruvuses_id int NOT NULL PRIMARY KEY,
cus_id int NOT NULL,
srv_id int NOT NULL,
cussruvuses_isSubscriber tinyint(1) NOT NULL,
FOREIGN KEY (cus_id) REFERENCES Customers(cus_id),
FOREIGN KEY (srv_id) REFERENCES Services(srv_id)
);
What is the cause of this error?
Unable to create table `bathhouses`.`customersservicesuses` (errno: 150 "Foreign key constraint is incorrectly formed") (Details…)
Many questions here on Stack Overflow found that the target table had two primary keys but in my case I have only one PK. Other solutions suggest that the two fields (PK and the FK of the other table) could be different, but in my case they have the same name and the same data type.
Details:
===================================== 2022-12-16 21:19:22 0x5dbc INNODB MONITOR OUTPUT
===================================== Per second averages calculated from the last 10 seconds
----------------- BACKGROUND THREAD
----------------- srv_master_thread loops: 47 srv_active, 0 srv_shutdown, 10442 srv_idle srv_master_thread log flush and writes: 10489
---------- SEMAPHORES
---------- OS WAIT ARRAY INFO: reservation count 265 OS WAIT ARRAY INFO: signal count 212 RW-shared spins 292, rounds 6753, OS waits 173 RW-excl spins 70, rounds 1524, OS waits 27 RW-sx spins 0, rounds 0, OS waits 0 Spin rounds per wait: 23.13 RW-shared, 21.77 RW-excl, 0.00 RW-sx
------------------------ LATEST FOREIGN KEY ERROR
------------------------ 2022-12-16 21:02:05 0x5158 Error in foreign key constraint of table `bathhouses`.`customersservicesuses`: Create table `bathhouses`.`customersservicesuses` with foreign key constraint failed. Referenced table `bathhouses`.`services` not found in the data dictionary near 'FOREIGN KEY (srv_ID) REFERENCES Services(srv_ID) )'.
------------ TRANSACTIONS
------------ Trx id counter 5994 Purge done for trx's n:o < 5994 undo n:o < 0 state: running but idle History list length 12 LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 284098011705752, not started 0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 284098011701544, not started 0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 284098011697336, not started 0 lock struct(s), heap size 1128, 0 row lock(s)
-------- FILE I/O
-------- I/O thread 0 state: native aio handle (insert buffer thread) I/O thread 1 state: native aio handle (log thread) I/O thread 2 state: native aio handle (read thread) I/O thread 3 state: native aio handle (read thread) I/O thread 4 state: native aio handle (read thread) I/O thread 5 state: native aio handle (read thread) I/O thread 6 state: native aio handle (write thread) I/O thread 7 state: native aio handle (write thread) I/O thread 8 state: native aio handle (write thread) I/O thread 9 state: native aio handle (write thread) Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] , ibuf aio reads:, log i/o's:, sync i/o's: Pending flushes (fsync) log: 0; buffer pool: 0 718 OS file reads, 1276 OS file writes, 559 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX
------------------------------------- Ibuf: size 1, free list len 0, seg size 2, 0 merges merged operations: insert 0, delete mark 0, delete 0 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 4441, node heap has 0 buffer(s) Hash table size 4441, node heap has 0 buffer(s) Hash table size 4441, node heap has 0 buffer(s) Hash table size 4441, node heap has 0 buffer(s) Hash table size 4441, node heap has 0 buffer(s) Hash table size 4441, node heap has 0 buffer(s) Hash table size 4441, node heap has 0 buffer(s) Hash table size 4441, node heap has 1 buffer(s)
0.00 hash searches/s, 0.30 non-hash searches/s
--- LOG
--- Log sequence number 2228333 Log flushed up to 2228333 Pages flushed up to 2228333 Last checkpoint at 2228324 0 pending log flushes, 0 pending chkp writes 307 log i/o's done, 0.00 log i/o's/second
---------------------- BUFFER POOL AND MEMORY
---------------------- Total large memory allocated 33554432 Dictionary memory allocated 87080 Buffer pool size 1003 Free buffers 746 Database pages 256 Old database pages 0 Modified db pages 0 Percent of dirty pages(LRU & free pages): 0.000 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s Pages read 681, created 267, written 893
0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead
0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 256, unzip_LRU len: 0 I/O sum[2]:cur[0], unzip sum[0]:cur[0]
-------------- ROW OPERATIONS
-------------- 0 queries inside InnoDB, 0 queries in queue 0 read views open inside InnoDB Process ID=13700, Main thread ID=11024, state: sleeping Number of rows inserted 0, updated 15, deleted 0, read 574
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.30 reads/s Number of system rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
---------------------------- END OF INNODB MONITOR OUTPUT
============================
