constraint problem 150 "Foreign Key constraint is incorrectly formed"

Question

constraint problem 150 "Foreign Key constraint is incorrectly formed"

Navigation

#1 by (1 votes)
#2 by (0 votes)
#3 by (0 votes)
#4 by (0 votes)

1

I'm doing a table creation in SQL and when executing

create table discos_compactos  ( 
   id_disco_compacto int, 
   titulo_cd varchar(60) not null, 
   id_disquera int not null, 
   constraint pk_discos_compactos primary key (id_disco_compacto), 
   constraint fk_id_disquera foreign key (id_disquera) references disqueras_cd
);

I get an error "Foreign Key constraint is incorrectly formed" the example is taken from a book but we created two tables before

CREATE TABLE TIPOS_MUSICA( 
  ID_TIPO INT,
  NOMBRE_TIPO VARCHAR(20 NOT NULL,
  CONSTRAINT UN_NOMBRE_TIPO UNIQUE (NOMBRE_TIPO),
  CONSTRAINT PK_TIPOS_MUSICA PRIMARY KEY (ID_TIPO) 
);

and

CREATE TABLE DISQUERAS_CD ( 
  ID_DISQUERA INT, 
  NOMBRE_COMPAÑIA VARCHAR(60) DEFAULT 
  'Independiente' NOT NULL,
  CONSTRAINT PK_DISQUERAS_CD PRIMARY KEY (ID_DISQUERA) 
);

what could be wrong in the code ?, I have placed it as it appears in the example

mysql

asked by Gerardo Bautista 10.03.2017 в 08:08

source

4 answers

0

The fault is that you need to indicate the column to which the FK is referenced in the foreign table. It would be like this:

create table discos_compactos ( id_disco_compacto int, titulo_cd 
varchar(60) not null, id_disquera int not null, constraint 
pk_discos_compactos primary key 
(id_disco_compacto), constraint fk_id_disquera foreign key (id_disquera) 
references disqueras_cd(ID_DISQUERA));

answered by 10.03.2017 в 09:19

0

The table must have an index in the id_disquera column to be able to use it in a foreign key.

The creation of your discos_compactos table would be:

CREATE TABLE discos_compactos (
  'id_disco_compacto' int(11) NOT NULL DEFAULT '0',
  'titulo_cd' varchar(60) NOT NULL,
  'id_disquera' int(11) NOT NULL
);

ALTER TABLE discos_compactos
  ADD PRIMARY KEY ('id_disco_compacto'),
  ADD KEY 'k_id_disquera' ('id_disquera');

ALTER TABLE discos_compactos
  ADD CONSTRAINT 'discos_compactos_ibfk_1' 
  FOREIGN KEY ('id_disquera') REFERENCES DISQUERAS_CD ('ID_DISQUERA');

answered by 10.03.2017 в 12:08

0

To be able to create a foreing key , you must add beforehand an index and validity that the field has the same format to which you are going to add, if it is int (10) the foreign must be the same. if you have fields such as unsigned or zerofill also, in your case you did not add the field of the referenced table.

create table discos_compactos  ( 
   id_disco_compacto int, 
   titulo_cd varchar(60) not null, 
   id_disquera int not null, 
   primary key (id_disco_compacto), 
   INDEX fk_id_disquera (id_disquera),
   constraint fk_id_disquera foreign key (id_disquera) REFERENCES DISQUERAS_CD(id)
);

answered by 27.12.2018 в 16:21

Android Studio- save object in sharedPreferences [duplicated] Extract text from a file in R

score 1 · Accepted Answer

Note that if you are in a Unix environment, your problem might be that the name of the table is uppercase and the 'foreign key' refers to the name in lowercase.

If the MySQL service is on Windows, this is not your problem.

Take a look at Identifier Case Sensitivity .

I have not found it in Spanish, but basically it says that in MySQL, the databases correspond to directories and tables with at least one file, so the uppercase-lowercase sensitivity of the operating system determines the behavior of the database in this aspect.