0

errno: 150 "Foreign key constraint is incorrectly formed")

DROP DATABASE IF EXISTS AgenceAssurance;
CREATE DATABASE IF NOT EXISTS AgenceAssurance;
USE AgenceAssurance;

CREATE TABLE Logininfo (
  idLogin    INT(3)  PRIMARY KEY  NOT NULL AUTO_INCREMENT,
  userName       VARCHAR(50) NOT NULL,
  pass       VARCHAR(50),
  usertype   VARCHAR(50)          DEFAULT NULL,
  GrantedKey VARCHAR(8)           DEFAULT NULL
)ENGINE = InnoDB;

CREATE TABLE Operation (
  idOpt    INT PRIMARY KEY  NOT NULL AUTO_INCREMENT,
  libelOpt TEXT,
  dateOpt  DATETIME DEFAULT now(),
  userName VARCHAR(50),
   CONSTRAINT fk_login FOREIGN KEY (userName) REFERENCES Logininfo (userName)
)ENGINE = InnoDB;

Error SQL query:

CREATE TABLE Operation (
  idOpt    INT PRIMARY KEY  NOT NULL AUTO_INCREMENT,
  libelOpt TEXT,
  dateOpt  DATETIME DEFAULT now(),
  userName VARCHAR(50),
  CONSTRAINT fk_login FOREIGN KEY (userName) REFERENCES Logininfo (userName)
)ENGINE = InnoDB

MySQL said:

1005 - Can't create table agenceassurance.operation (errno: 150 "Foreign key constraint is incorrectly formed") (Details…)

Community
  • 1
  • 1
AbdeAMNR
  • 165
  • 1
  • 5
  • 18
  • In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. - https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html - not something your fk has – P.Salmon May 09 '18 at 13:30
  • how i can correct that – AbdeAMNR May 09 '18 at 13:39
  • You have composite primary key and you are using one of the column for foreign key Check this https://stackoverflow.com/questions/10565846/use-composite-primary-key-as-foreign-key?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa – Kedar Limaye May 09 '18 at 13:42
  • please check the changes above – AbdeAMNR May 09 '18 at 13:57
  • @KedarLimaye i have made only one Primery key i altered the table to add the foreign key Separately – AbdeAMNR May 09 '18 at 14:04

2 Answers2

0

CONSTRAINT pk_Log PRIMARY KEY (idLogin, userName): try as below: CONSTRAINT pk_Log PRIMARY KEY (userName) because: at a time two fields with PK, cannot use as FK.

Hosain Ahmed
  • 115
  • 6
0

The field userName in Logininfo table must be UNIQUE. You can only have a foreign key referencing a unique field.

Just like this:

userName VARCHAR(50) NOT NULL UNIQUE
Bentaye
  • 9,403
  • 5
  • 32
  • 45
ShadMaN
  • 1
  • 2