Error creating foreign key in MariaDB

Question

Error creating foreign key in MariaDB

Navigation

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

1

I have the following 3 tables:

CREATE TABLE tEstado
  (idEstado INT NOT NULL AUTO_INCREMENT, Estado VARCHAR(100) NOT NULL,
   PRIMARY KEY(idEstado), INDEX(idEstado))ENGINE=INNODB;


CREATE TABLE tMunicipio 
   (idMunicipio INT NOT NULL AUTO_INCREMENT, 
    idMunicipioEstado VARCHAR(100) NOT NULL, 
    Municipo VARCHAR(100) NOT NULL, 
    idEstado INT NOT NULL,
    PRIMARY KEY(idMunicipio, idMunicipioEstado), 
    INDEX(idMunicipio, idMunicipioEstado), 
    FOREIGN KEY(idEstado) REFERENCES tEstado(idEstado))ENGINE=INNODB;

CREATE TABLE tColonia
  (idColonia INT NOT NULL AUTO_INCREMENT, 
   CP VARCHAR(10) NOT NULL, 
   Colonia VARCHAR(100) NOT NULL, 
   idEstado INT NOT NULL, 
   idMunicipioEstado VARCHAR(100) NOT NULL, 
   PRIMARY KEY(idColonia), 
   INDEX(idColonia),
   FOREIGN KEY (idEstado) REFERENCES tEstado(idEstado), 
   FOREIGN KEY (idMunicipioEstado) REFERENCES tMunicipio(idMunicipioEstado)
   ON UPDATE CASCADE ON DELETE SET NULL)
   ENGINE=INNODB;

The first two I can create them without any problem, even I already have records in them but when creating the third I get the following error: Can not create table proyecto . tcolonia (errno: 150 "Foreign key constraint is incorrectly formed")

I would greatly appreciate your help. Good afternoon.

mysql mariadb

asked by Guillermo Ricardo Spindola Bri 04.03.2017 в 22:49

source

2 answers

Error with one many relations RestFul spring hibernate Save multiple checkbox, inputs text together in a database form with ajax

score 1 · Answer 1

There is an inconsistency error in the third table:

CREATE TABLE tColonia
  (idColonia INT NOT NULL AUTO_INCREMENT, 
   CP VARCHAR(10) NOT NULL, 
   Colonia VARCHAR(100) NOT NULL, 
   idEstado INT NOT NULL, 
   idMunicipioEstado VARCHAR(100) NOT NULL, 
   PRIMARY KEY(idColonia), 
   INDEX(idColonia),
   FOREIGN KEY (idEstado) REFERENCES tEstado(idEstado), 
   FOREIGN KEY (idMunicipioEstado) REFERENCES tMunicipio(idMunicipioEstado)
   ON UPDATE CASCADE ON DELETE SET NULL)
   ENGINE=INNODB;

When you create the columns that will be part of the foreign key, you say that:

idEstado INT NOT NULL

idMunicipioEstado VARCHAR(100) NOT NULL

and when you create the foreign key, you say:

ON UPDATE CASCADE ON DELETE SET NULL

In the creation of the foreign key, you say that when you delete a column of the other table from the NULL value to that column in the current table, but that is not possible, because when creating the column you have asked for the same do not support null values.

Note: If the column idMunicipioEstado has to be repeated in two tables, it would not create it as VARCHAR (100), but as an integer that makes reference in the source table (that is the sense of having columns of type id). The indexes would be more efficient and would considerably reduce the size of the database and the possibility of erroneous data, thus facilitating the detection and correction of them.

In this case, I think that from the tColonia , you could perfectly relate the municipality pointing to idMunicipio of tMunicipios . Unless you want to give another use not reflected here, the column idMunicipioEstado VARCHAR(100) exceeds.

CREATE TABLE tColonia
  (idColonia INT NOT NULL AUTO_INCREMENT, 
   CP VARCHAR(10) NOT NULL, 
   Colonia VARCHAR(100) NOT NULL, 
   idEstado INT NOT NULL,

idMunicipioEstado VARCHAR(100) NOT NULL,

   idMunicipio INT NOT NULL, 
   PRIMARY KEY(idColonia), 
   INDEX(idColonia),
   FOREIGN KEY (idEstado) REFERENCES tEstado(idEstado),

FOREIGN KEY (idMunicipioEstado) REFERENCES tMunicipio(idMunicipioEstado)

   FOREIGN KEY (idMunicipio) REFERENCES tMunicipio(idMunicipio)...

You can take a look at this sqlfiddle , it's a model similar to yours, it gets access to the values only for the numerical id: with an id_municipality one can perfectly know the name of the municipality, the province, the state, the colony, the country ...

score 0 · Answer 2

Just to be clear, you receive the error for 2 reasons:

As A. Cedano explained, it is because you have defined a ON DELETE SET NULL clause in a foreign key for a column that you have defined as NOT NULL . Those 2 configurations together are incompatible.

The foreign key points to tMunicipio(idMunicipioEstado) , whose column is not indexed. That is not legal either.

There are several ways to eliminate the error, for example by adding an index to tMunicipio(idMunicipioEstado) and removing NOT NULL to tColonia (idMunicipioEstado) . But just because the error can be eliminated, does not necessarily mean that this is the correct way to solve it.

I think you have to verify your design well, and for this, it seems to me that A. Cedano's answer provides very good recommendations.