Question 63804 in PhpMyAdmin Question posted in
The official documentation can be found here.

Foreign key constraint is incorrectly formed, i dont know what to do – Phpmyadmin

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

Tags:

2

Answers


  1. 0

    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)
    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search