What is a promise in Javascript?

Question

Manolait

Asked: 2020-11-17 00:41:17 +0800 CST 2020-11-17 00:41:17 +0800 CST 2020-11-17 00:41:17 +0800 CST

"Cannot add foreign key constraint" error when relating two varchar fields

772

I am learning about relational databases and I have a problem when relating two tables with a helper with a many-to-many relationship.

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

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

2º 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. Thank you and greetings.

EDIT1:

I 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

2 Answers

Voted

A. Cedano · Answer 1 · 2020-11-17T06:13:52+08:00

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 referenced key must have similar data types. The size and sign of integer types must be the same. The length of the string types does not have to be the same. For non-binary (character) string columns, the character set ( CHARSET) and collation ( COLLATE) must be equal .

_{Source: MySQL Documentation . (Translation and bold are mine.)}

So the tables:

sensorsand sensors_has_greenhousehave the following COLLATEin the CREATE TABLE:

COLLATE = utf8_spanish_ci

But sensors_has_greenhouseit is related to table greenhouse, which does not haveCOLLATE = utf8_spanish_ci .

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

Solution

Either you grant COLLATE = utf8_spanish_cithe table greenhouse (already users, because otherwise you would have the same problem between the latter two), or you remove the COLLATE = utf8_spanish_cifrom the two tables that have it.

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

Cannot add foreign key constraint

What if it still fails?

Since you have been running a CREATE TABLEbefore, some of the tables may have already been created, before the relationship creation failed. So you have several possibilities.

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

Either you can modify the COLLATE through another type of query such as ALTER TABLE, or directly in the graphical interface of your DBMS.

Note:

In general, it is not recommended to relate the tables using columns of type VARCHAR. To do this you must be very sure that this value uniquely represents each record. And they also have to assess the cost that this may entail in terms of database performance.

lordmiaux · Answer 2 · 2020-11-17T06:01:36+08:00

Always use relationships with a numeric identifier, it is much better at sorting the data in the NM table. Yes, it is possible, as you have been told, but it is not the most recommended, in addition to the fact that it would not have much logic since you want that when you see that table there is a logical order of the data, and you achieve that by ordering that relation with a ID that brings together both foreign keys.

A quick example I have lying around of NM table:

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;

"Cannot add foreign key constraint" error when relating two varchar fields

Solution

What if it still fails?

HTML button that sends you to another page

Why do I get the error "Call to undefined function mysql_connect()"?

How to create an HTML button that works as a link?

How to separate a String in Java. How to use split()

Filter by dates in sql server

How to limit the number of decimal places in a double?

For each in JavaScript?

Position footer ALWAYS glued to the footer

Definitive Guide to Type Conversion in Java

How to properly compare Strings (and objects) in Java?