MySQL Estou tendo erro a o criar chave estrangeira no mariaDB

[Luis Henrique Justin]

Boa noite pessoal, estou tendo problema em criar chave estrangeira no mariaDB estou usando o navicat que tem a versão de mariaDB minha versão do banco de dados é 10.1. Mas toda vez que tento exportar o modelo para o banco de dados seja pelo próprio navicat ou exportando o SQL e rodando no phpmyadmin da o mesmo erro, esse é meu código SQL:

CREATE TABLE `restaurantes` (
`id` int NOT NULL AUTO_INCREMENT,
`nome` varchar(255) NULL,
PRIMARY KEY (`id`) 
);
CREATE TABLE `funcionarios` (
`id` int(11) NOT NULL,
`id_restaurante` varchar(255) NULL,
`nome` varchar(255) NULL,
`sobrenome` varchar(255) NULL,
PRIMARY KEY (`id`) 
);

ALTER TABLE `funcionarios` ADD CONSTRAINT `fk_funcionario_restaurante` FOREIGN KEY (`id_restaurante`) REFERENCES `restaurantes` (`id`);

e o erro que acontece é este: #1005 - Não pode criar a tabela `eatup`.`#sql-2d98_19c` (erro no. 150 "Foreign key constraint is incorrectly formed") (Informações…)

 

 

e quando clico em informações aparece isso:

=====================================
2018-10-20 20:05:43 1c34 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 0 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 94 srv_active, 0 srv_shutdown, 10106 srv_idle
srv_master_thread log flush and writes: 10200
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 283
OS WAIT ARRAY INFO: signal count 284
Mutex spin waits 2458, rounds 6777, OS waits 122
RW-shared spins 145, rounds 4381, OS waits 146
RW-excl spins 5, rounds 401, OS waits 13
Spin rounds per wait: 2.76 mutex, 30.21 RW-shared, 80.20 RW-excl
------------------------
LATEST FOREIGN KEY ERROR
------------------------
2018-10-20 20:04:38 1c34 Error in foreign key constraint of table `eatup`.`funcionarios`:
 FOREIGN KEY (`id_restaurante`) REFERENCES `restaurantes` (`id`):
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
See http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html
for correct foreign key definition.
Alter  table `eatup`.`funcionarios` with foreign key constraint failed. Field type or character set for column 'id_restaurante' does not mach referenced column 'id' near ' FOREIGN KEY (`id_restaurante`) REFERENCES `restaurantes` (`id`)'.
------------
TRANSACTIONS
------------
Trx id counter 6493
Purge done for trx's n:o < 6464 undo n:o < 0 state: running but idle
History list length 51
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 414, OS thread handle 0x1c34, query id 4130 localhost 127.0.0.1 root init
SHOW ENGINE INNODB STATUS
---TRANSACTION 6492, not started
MySQL thread id 413, OS thread handle 0x1c34, query id 4126 localhost 127.0.0.1 pma 
---TRANSACTION 5379, not started
MySQL thread id 1, OS thread handle 0x2080, query id 0 Waiting for requests
--------
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, 0] , aio writes: 0 [0, 0, 0, 0] ,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
694 OS file reads, 2166 OS file writes, 915 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
1000.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 2340198
Log flushed up to   2340198
Pages flushed up to 2340198
Last checkpoint at  2340198
Max checkpoint age    7782360
Checkpoint age target 7539162
Modified age          0
Checkpoint age        0
0 pending log writes, 0 pending chkp writes
442 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 17203200; in additional pool allocated 0
Total memory allocated by read views 2336
Internal hash tables (constant factor + variable factor)
    Adaptive hash index 297300 	(276764 + 20536)
    Page hash           9068 (buffer pool 0 only)
    Dictionary cache    201221 	(69928 + 131293)
    File system         420056 	(406136 + 13920)
    Lock system         22124 	(21188 + 936)
    Recovery system     0 	(0 + 0)
Dictionary memory allocated 131293
Buffer pool size        1023
Buffer pool size, bytes 16760832
Free buffers            766
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 657, created 383, written 1539
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[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
0 RW transactions active inside InnoDB
0 RO transactions active inside InnoDB
0 out of 1000 descriptors used
Main thread id 14008, state: sleeping
Number of rows inserted 5, updated 3, deleted 0, read 203
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 1000.00 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
============================

Alguém sabe o que devo fazer ?

 

[HwapX]

As colunas que fazem o relacionamento na chave estrangeira devem ter tipos idênticos.

 

No seu caso o tipo do campo id_restaurante da tabela funcionarios é varchar(255) e deveria ser int assim como a coluna id da tabela restaurantes.

[Luis Henrique Justin]

26 minutos atrás, HwapX disse:

As colunas que fazem o relacionamento na chave estrangeira devem ter tipos idênticos.

 

No seu caso o tipo do campo id_restaurante da tabela funcionarios é varchar(255) e deveria ser int assim como a coluna id da tabela restaurantes.

Obrigado não tinha me dado conta deste detalhe é, obrigado pela ajuda.

[Horse With No Name]

Parceiro a id_restaurante da table funcionário ta como varchar.

tente colocar "INT" ou "iNTEGER" na coluna do id na table restaurantes.

 

tome cuidado com isso! abraços