This is my last resort, since I have tried everything I could find on Google and here on Stack Overflow.
I have thoroughly searched for an answer before asking this question. I have found the people have been getting the same error message from MySQL, but their solution did not worked for me, because I am not doing that particular thing (different column types) wrong.
I have a problem with foreign keys, where they are the same type (varchar(xx), where xx is same length in both cases; NOT NULL), but still gives me an error, which is described by the PHPMyAdmin's command SHOW ENGINE INNODB STATUS as:
2015-03-24 16:04:21 66c Error in foreign key constraint of table arhiva/privilegija:
FOREIGN KEY (korisnik_ime) REFERENCES korisnik(ime) ,
FOREIGN KEY (firma_naziv) REFERENCES firma(naziv)
):
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.
Here is my database schema, sorry for the non English names (by the way, the first time I encountered this problem the column names were in Cyrillic characters, but even after I converted them in Latin - what you are seeing here - my problem was not solved):
DROP DATABASE IF EXISTS arhiva;
CREATE DATABASE arhiva
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_general_ci;
USE arhiva;
CREATE TABLE `korisnik` (
ime VARCHAR(30) NOT NULL ,
lozinka CHAR(60) NOT NULL
);
CREATE TABLE `firma` (
naziv VARCHAR(50) NOT NULL
);
CREATE TABLE `privilegija` (
korisnik_ime VARCHAR(30) NOT NULL ,
firma_naziv VARCHAR(50) NOT NULL ,
pristap CHAR(1) NOT NULL ,
FOREIGN KEY (korisnik_ime) REFERENCES korisnik(ime) ,
FOREIGN KEY (firma_naziv) REFERENCES firma(naziv)
);
Can you please guide me in the right direction of solving this problem. I thought that using Cyrillic characters was the problem, but it turns out that the problem lies somewhere else.
add PRIMARY KEY (), like this:
CREATE TABLE `korisnik` (
ime VARCHAR(30) NOT NULL ,
lozinka CHAR(60) NOT NULL,
PRIMARY KEY(ime)
);
CREATE TABLE `firma` (
naziv VARCHAR(50) NOT NULL,
PRIMARY KEY(naziv)
);
Now you have an index so the foreign key constraint should work.