Foreign key constraint is incorrectly formed, i dont know what to do-CodePudding

I have this two tables in phpMyAdmin:

ASSENZE:

CREATE TABLE `assenze` (
    `idAssenza` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `ename` varchar(255) NOT NULL,
    `starts` datetime NOT NULL,
    `ends` datetime NOT NULL
   )

UTENTE:

CREATE TABLE `utente` (
  `utenteID` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `cognome` varchar(255) NOT NULL,
  `nome` varchar(255) NOT NULL,
  `nomeUtente` varchar(50) NOT NULL,
  `password` varchar(255) NOT NULL
)

I have to connect them throught Foreign Key which consist in putting "nomeUtente" in "assenze" table.

I tried with:

ALTER TABLE assenze
ADD COLUMN nomeUtente varchar(50),
ADD FOREIGN KEY (nomeUtente) REFERENCES utente(nomeUtente)

but it gives me this error ->

Foreign key constraint is incorrectly formed

I also tried with add the column alone and that give it the FK attribute but doesnt work On the internet i found other solution but didnt work for me. I dont know what to try anymore.

P.S. i cannot delete the tables and redoit because they are connected to some php file and gives me error if i deleted and recreated

I just wanted to add: thanks to everyone who's helping me

CodePudding user response：

The problem is that with your ALTER query you are trying to add another column with the same name nomeUtente

This:

ALTER TABLE assenze
ADD COLUMN nomeUtente varchar(50),
ADD FOREIGN KEY (nomeUtente) REFERENCES utente(nomeUtente)

Should be:

ALTER TABLE assenze
ADD FOREIGN KEY (nomeUtente) REFERENCES utente(nomeUtente)

CodePudding user response：

Every foeign ley needs an index on the column or columns referenced

So add a KEX to the nomeUtente

CREATE TABLE `assenze` (
    `idAssenza` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `ename` varchar(255) NOT NULL,
    `starts` datetime NOT NULL,
    `ends` datetime NOT NULL
   )

CREATE TABLE `utente` (
  `utenteID` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `cognome` varchar(255) NOT NULL,
  `nome` varchar(255) NOT NULL,
  `nomeUtente` varchar(50) NOT NULL,
  `password` varchar(255) NOT NULL
  
)

ALTER TABLE utente ADD
  KEY(`nomeUtente`)

ALTER TABLE assenze
ADD COLUMN nomeUtente varchar(50),
ADD FOREIGN KEY (nomeUtente) REFERENCES utente(nomeUtente)

db<>fiddle here