I am performing a table creation in SQL
and at the time of 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
);
the error appears
Foreign Key constraint is incorrectly formed
I took the example 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)
);
Y
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...
I had the same error, caused by the new tables being created with the wrong collation and a different storage engine than the rest of the database.
It was fixed by changing the collation of the new tables to the correct one (from latin_swedish to utf8) and the storage engine (MyISAM) to that of the rest of the database (innoDB).
It can be done comfortably from the phpMyAdmin interface. I just solved it so I still can't figure out why these attributes were being loaded differently than the rest of the DB.
Hello and good, I had the same problem and nothing that it says here worked for me since I realized that I had the same length of the values between the foreign and the main one, they were practically twins in that sense, and then I did the following... .
[TABLE1]: This is the main table I wanted to reference...
[TABLE2]:This is the table that contains the foreign to refer to table1
what the foreigner did not let me do with table 1 was the following:
I had different characters when I modified them and the error went away. hopefully it will serve many
Note that if you're in a Unix environment, your problem might be that the table name is uppercase and the 'foreign key' refers to the lowercase name.
If the MySQL service is on Windows, this is not your problem.
Take a look at Identifier Case Sensitivity .
I haven't found it in Spanish, but basically it says that in MySQL, databases correspond to directories and tables to at least one file, so the case sensitivity of the operating system determines the behavior of the database in this regard.
The bug is that you need to indicate the column to which the FK refers to in the foreign table. It would be done like this:
The table has to have an index on the column
id_disquera
in order to use it in a foreign key.The creation of your table
discos_compactos
would be:In order to create one
foreing key
, you must first add an index and validate that the field has the same format as the one you are going to add, if it is int(10) it mustforeign
be the same. if it has fields likeunsigned
orzerofill
also, in your case you did not add the field of the referenced table to it.I had the same problem. After many hours of experimenting, I discovered that the problem was: one table was MYISAM and the other table was INNODB.
I set them both up in MYISAM, and it allowed me to set the restrictions