Error "Can not add foreign key constraint" when relating two varchar fields

Question

Error "Can not add foreign key constraint" when relating two varchar fields

Navigation

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

2

I am learning about relational databases and I have a problem when relating two tables with an auxiliary with a relationship of many to many.

I am trying to relate two tables that have a varchar field as the primary key generating an auxiliary table but it gives me error Cannot add foreign key constraint

I have a series of doubts: 1º Is it possible to make this relationship with a varchar field type? I've tried changing fields by INT and if it works correctly.

2nd I have tried to relate a table with another from 1 to many and with the varchar data type if it works.

Attached image of the model. Thanks and best regards.

EDIT1:

Copy the creation is the sentence.

-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema inver
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema inver
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS 'inver' DEFAULT CHARACTER SET utf8 ;
USE 'inver' ;

-- -----------------------------------------------------
-- Table 'inver'.'users'
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS 'inver'.'users' (
  'id_user' INT(11) NOT NULL AUTO_INCREMENT,
  'name' VARCHAR(45) NULL DEFAULT NULL,
  'surname' VARCHAR(45) NULL DEFAULT NULL,
  'email' VARCHAR(45) NULL DEFAULT NULL,
  'password' VARCHAR(230) NULL DEFAULT NULL,
  'role' VARCHAR(45) NULL DEFAULT NULL,
  PRIMARY KEY ('id_user'))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table 'inver'.'greenhouse'
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS 'inver'.'greenhouse' (
  'fiwareServicePath' VARCHAR(45) NOT NULL,
  'metros' VARCHAR(45) NULL,
  'lugar' VARCHAR(45) NULL,
  'users_id_user' INT(11) NOT NULL,
  INDEX 'fk_greenhouse1_users1_idx' ('users_id_user' ASC),
  PRIMARY KEY ('fiwareServicePath'),
  CONSTRAINT 'fk_greenhouse1_users1'
    FOREIGN KEY ('users_id_user')
    REFERENCES 'inver'.'users' ('id_user')
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table 'inver'.'sensors'
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS 'inver'.'sensors' (
  'entityId' VARCHAR(45) NOT NULL,
  'sensor_name' VARCHAR(45) CHARACTER SET 'utf8' COLLATE 'utf8_spanish_ci' NULL DEFAULT NULL,
  PRIMARY KEY ('entityId'))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_spanish_ci;


-- -----------------------------------------------------
-- Table 'inver'.'sensors_has_greenhouse'
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS 'inver'.'sensors_has_greenhouse' (
  'sensors_entityId' VARCHAR(45) NOT NULL,
  'greenhouse_fiwareServicePath' VARCHAR(45) NOT NULL,
  PRIMARY KEY ('greenhouse_fiwareServicePath', 'sensors_entityId'),
  INDEX 'fk_sensors_has_greenhouse_greenhouse1_idx' ('greenhouse_fiwareServicePath' ASC),
  INDEX 'fk_sensors_has_greenhouse_sensors1_idx' ('sensors_entityId' ASC),
  CONSTRAINT 'fk_sensors_has_greenhouse_sensors1'
    FOREIGN KEY ('sensors_entityId')
    REFERENCES 'inver'.'sensors' ('entityId')
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT 'fk_sensors_has_greenhouse_greenhouse1'
    FOREIGN KEY ('greenhouse_fiwareServicePath')
    REFERENCES 'inver'.'greenhouse' ('fiwareServicePath')
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_spanish_ci;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

EDIT2

I add the error:

Executing SQL script in server
ERROR: Error 1215: Cannot add foreign key constraint
SQL Code:
        -- -----------------------------------------------------
        -- Table 'inver'.'sensors_has_greenhouse'
        -- -----------------------------------------------------
        CREATE TABLE IF NOT EXISTS 'inver'.'sensors_has_greenhouse' (
          'sensors_entityId' VARCHAR(45) NOT NULL,
          'greenhouse_fiwareServicePath' VARCHAR(45) NOT NULL,
          PRIMARY KEY ('greenhouse_fiwareServicePath', 'sensors_entityId'),
          INDEX 'fk_sensors_has_greenhouse_greenhouse1_idx' ('greenhouse_fiwareServicePath' ASC),
          INDEX 'fk_sensors_has_greenhouse_sensors1_idx' ('sensors_entityId' ASC),
          CONSTRAINT 'fk_sensors_has_greenhouse_sensors1'
            FOREIGN KEY ('sensors_entityId')
            REFERENCES 'inver'.'sensors' ('entityId')
            ON DELETE NO ACTION
            ON UPDATE NO ACTION,
          CONSTRAINT 'fk_sensors_has_greenhouse_greenhouse1'
            FOREIGN KEY ('greenhouse_fiwareServicePath')
            REFERENCES 'inver'.'greenhouse' ('fiwareServicePath')
            ON DELETE NO ACTION
            ON UPDATE NO ACTION)
        ENGINE = InnoDB
        DEFAULT CHARACTER SET = utf8
        COLLATE = utf8_spanish_ci

SQL script execution finished: statements: 8 succeeded, 1 failed

Fetching back view definitions in final form.
Nothing to fetch

mysql

asked by Manolait 16.11.2017 в 09:41

source

2 answers

3

The error is that the related tables do not have the same property COLLATE .

The documentation says the following about it:

Corresponding columns in the foreign key and the referenced key must   have similar data types. The size and sign of integer types must be   the same. The length of string types need not be the same. For   nonbinary (character) string columns, the character set and collation   must be the same .

The corresponding columns in the foreign key and the key to which it is referenced must have similar data types. The size   and the sign of the integers must be the same. The length of the   string types does not have to be the same. For string columns   non-binary (characters), the character set ( CHARSET ) and the   intercalation ( COLLATE ) must be equal .

Source: MySQL Documentation .   (The translation and the bold letters are mine.)

So the tables:

sensors and sensors_has_greenhouse have the following COLLATE in CREATE TABLE :

COLLATE = utf8_spanish_ci

But sensors_has_greenhouse is related to table greenhouse , which does not have COLLATE = utf8_spanish_ci .

Two non-binary columns (columns of type String as it is VARCHAR ) can not participate in a relationship between tables if they have different COLLATE . That is precisely what happens in your case .

Solution

Either you give COLLATE = utf8_spanish_ci to the table greenhouse (and users , because if you would not have the same problem between the last two), or you remove the COLLATE = utf8_spanish_ci to the two tables that have it .

I have done a proof of concept correcting those details and I have not thrown the error:

Can not add foreign key constraint

What happens if it still fails?

Since you have been running a CREATE TABLE previously, some of the tables may have already been created, before the creation of the relationships failed. Then you have several possibilities.

The simplest, if your tables are new and you do not already have data , would be to execute a DROP TABLE IF EXISTS ...nombre-de-tabla... of each of them, before the CREATE TABLE to start again. But run that only once , to prevent your tables from being created all over again with the correct settings.

Or you can modify the COLLATE using another type of query like ALTER TABLE , or directly in the graphic interface of your DBMS.

Note:

It is generally not advisable to relate the tables using columns of type VARCHAR . To do this you must be very sure that this value represents each record in a unique way. And they have to evaluate also the cost that this can imply in terms of the performance of the database.

answered by 16.11.2017 в 15:13

Load two or more rows to a DataTable by means of a Button Asynchronous javascript calls and asynchronous task .net

score 1 · Accepted Answer

Always use the relationships with a numerical identifier, it is much better when ordering the data in the N-M table. Yes it is possible, as you have indicated, but it is not recommended, and it would not have much logic since you want to see that table has a logical order of data, and that you get ordering that relationship with a ID that agglutinates both foreign keys.

A quick example that I have out there of table N-M:

CREATE DATABASE IF NOT EXISTS 'ra33' DEFAULT CHARACTER SET latin1 COLLATE 
latin1_swedish_ci;
USE 'ra33';

CREATE TABLE 'empresas' (
  'id' int(10) UNSIGNED NOT NULL,
  'nombre' varchar(50) NOT NULL,
  'direccion' varchar(50) NOT NULL,
  'telefono' varchar(50) NOT NULL,
 'fundacion' date NOT NULL,
  'tipo' varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE 'empresa_producto' (
  'id_empresa' int(10) UNSIGNED DEFAULT NULL,
  'id_producto' int(10) UNSIGNED DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE 'productos' (
  'id' int(10) UNSIGNED NOT NULL,
  'nombre' varchar(50) NOT NULL,
  'precio_base' float NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;