Errors when creating SQL DB

Question

Errors when creating SQL DB

Navigation

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

1

I am using MySQL Workbench to create a BD and it gives me the following error, I am still new in this I hope you can help me. The error is as follows:

Executing SQL script in server
ERROR: Error 1005: Can't create table 'bd_easymaker_0'.'equipos' (errno: 121 "Duplicate key on write or update")
SQL Code:


    -- -----------------------------------------------------
        -- Table 'BD_EasyMaker_0'.'Equipos'
        -- -----------------------------------------------------
        CREATE TABLE IF NOT EXISTS 'BD_EasyMaker_0'.'Equipos' (
          'ID_Equipo' INT NOT NULL AUTO_INCREMENT,
          'ID_Proyecto' INT NOT NULL,
          'ID_Usuario1' INT NOT NULL,
          'ID_Usuario2' INT NOT NULL,
          'ID_Imagen' INT NULL,
          'ID_Usuario3' INT NULL,
          'ID_Usuario4' INT NULL,
          'ID_Usuario5' INT NULL,
          PRIMARY KEY ('ID_Equipo'),
          INDEX 'ID_Usuario1_idx' ('ID_Usuario1' ASC),
          INDEX 'ID_Usuario2_idx' ('ID_Usuario2' ASC),
          INDEX 'ID_Usuario3_idx' ('ID_Usuario3' ASC),
          INDEX 'ID_Usuario4_idx' ('ID_Usuario4' ASC),
          INDEX 'ID_Usuario5_idx' ('ID_Usuario5' ASC),
          INDEX 'ID_Imagen_idx' ('ID_Imagen' ASC),
          INDEX 'ID_Proyecto_idx' ('ID_Proyecto' ASC),
          CONSTRAINT 'ID_Usuario1'
            FOREIGN KEY ('ID_Usuario1')
            REFERENCES 'BD_EasyMaker_0'.'Usuarios' ('ID_Usuario')
            ON DELETE NO ACTION
            ON UPDATE NO ACTION,
          CONSTRAINT 'ID_Usuario2'
            FOREIGN KEY ('ID_Usuario2')
            REFERENCES 'BD_EasyMaker_0'.'Usuarios' ('ID_Usuario')
            ON DELETE NO ACTION
            ON UPDATE NO ACTION,
          CONSTRAINT 'ID_Usuario3'
            FOREIGN KEY ('ID_Usuario3')
            REFERENCES 'BD_EasyMaker_0'.'Usuarios' ('ID_Usuario')
            ON DELETE NO ACTION
            ON UPDATE NO ACTION,
          CONSTRAINT 'ID_Usuario4'
            FOREIGN KEY ('ID_Usuario4')
            REFERENCES 'BD_EasyMaker_0'.'Usuarios' ('ID_Usuario')
            ON DELETE NO ACTION
            ON UPDATE NO ACTION,
          CONSTRAINT 'ID_Usuario5'
            FOREIGN KEY ('ID_Usuario5')
            REFERENCES 'BD_EasyMaker_0'.'Usuarios' ('ID_Usuario')
            ON DELETE NO ACTION
            ON UPDATE NO ACTION,
          CONSTRAINT 'ID_Imagen'
            FOREIGN KEY ('ID_Imagen')
            REFERENCES 'BD_EasyMaker_0'.'Imagenes' ('ID_Imagen')
            ON DELETE NO ACTION
            ON UPDATE NO ACTION,
          CONSTRAINT 'ID_Proyecto'
            FOREIGN KEY ('ID_Proyecto')
            REFERENCES 'BD_EasyMaker_0'.'Proyectos' ('ID_Proyectos')
            ON DELETE NO ACTION
            ON UPDATE NO ACTION)
        ENGINE = InnoDB

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

Fetching back view definitions in final form.
Nothing to fetch

/// I leave the original above for future reference.

Now I have fewer errors but I can not find where the error is in this fragment:

    -- -----------------------------------------------------
    -- Table 'BD_EasyMaker_0'.'Usuarios'
    -- -----------------------------------------------------
    CREATE TABLE IF NOT EXISTS 'BD_EasyMaker_0'.'Usuarios' (
      'ID_Usuario' INT NOT NULL AUTO_INCREMENT,
      'Nombre' VARCHAR(45) NOT NULL,
      'Ocupacion' VARCHAR(45) NOT NULL,
      'Facultad' VARCHAR(45) NULL,
      'Universidad' VARCHAR(45) NULL,
      'Rank' INT NULL,
      'Habilidades' VARCHAR(45) NOT NULL,
      'Sexo' INT NOT NULL,
      'Email' VARCHAR(45) NOT NULL,
      'Equipo' INT NULL,
      'Imagen' INT NOT NULL,
      'Contrasena' VARCHAR(45) NOT NULL,
      PRIMARY KEY ('ID_Usuario'),
      INDEX 'ID_Imagen_idx' ('Imagen' ASC),
      INDEX 'ID_Equipo_idx' ('Equipo' ASC),
      CONSTRAINT 'FK_ID_Equipo'
        FOREIGN KEY ('Equipo')
        REFERENCES 'BD_EasyMaker_0'.'Equipos' ('ID_Equipo')
        ON DELETE NO ACTION
        ON UPDATE NO ACTION,
      CONSTRAINT 'FK_ID_Imagen'
        FOREIGN KEY ('Imagen')
        REFERENCES 'BD_EasyMaker_0'.'Imagenes' ('ID_Imagen')
        ON DELETE NO ACTION
        ON UPDATE NO ACTION)
    ENGINE = InnoDB

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

mysql

asked by eduardo Castillo 08.09.2017 в 18:36

source

2 answers

0

The table can not be created because your CONSTRAINT have the same name as your columns. You must use a different identifier for the CONSTRAINT for example fk_idUsuario1

CONSTRAINT 'FK_ID_Usuario1'
            FOREIGN KEY ('ID_Usuario1')
            REFERENCES 'BD_EasyMaker_0'.'Usuarios' ('ID_Usuario')
            ON DELETE NO ACTION
            ON UPDATE NO ACTION,

answered by 08.09.2017 в 18:56

Create a TRIGGER to insert a record in a table Problems opening Jupyter Notebook - Python

score 1 · Accepted Answer

The problem is in the foreign keys by the name, try changing the names of the constraints, this error usually happens because the constraints have the same identifier as the columns

Change them by adding fk_ to each constraint

  CREATE TABLE IF NOT EXISTS 'BD_EasyMaker_0'.'Equipos' (
          'ID_Equipo' INT NOT NULL AUTO_INCREMENT,
          'ID_Proyecto' INT NOT NULL,
          'ID_Usuario1' INT NOT NULL,
          'ID_Usuario2' INT NOT NULL,
          'ID_Imagen' INT NULL,
          'ID_Usuario3' INT NULL,
          'ID_Usuario4' INT NULL,
          'ID_Usuario5' INT NULL,
          PRIMARY KEY ('ID_Equipo'),
          INDEX 'ID_Usuario1_idx' ('ID_Usuario1' ASC),
          INDEX 'ID_Usuario2_idx' ('ID_Usuario2' ASC),
          INDEX 'ID_Usuario3_idx' ('ID_Usuario3' ASC),
          INDEX 'ID_Usuario4_idx' ('ID_Usuario4' ASC),
          INDEX 'ID_Usuario5_idx' ('ID_Usuario5' ASC),
          INDEX 'ID_Imagen_idx' ('ID_Imagen' ASC),
          INDEX 'ID_Proyecto_idx' ('ID_Proyecto' ASC),

          CONSTRAINT 'fk_ID_Usuario1'
            FOREIGN KEY ('ID_Usuario1')
            REFERENCES 'BD_EasyMaker_0'.'Usuarios' ('ID_Usuario')
            ON DELETE NO ACTION
            ON UPDATE NO ACTION,
          CONSTRAINT 'fk_ID_Usuario2'
            FOREIGN KEY ('ID_Usuario2')
            REFERENCES 'BD_EasyMaker_0'.'Usuarios' ('ID_Usuario')
            ON DELETE NO ACTION
            ON UPDATE NO ACTION,
          CONSTRAINT 'fk_ID_Usuario3'
            FOREIGN KEY ('ID_Usuario3')
            REFERENCES 'BD_EasyMaker_0'.'Usuarios' ('ID_Usuario')
            ON DELETE NO ACTION
            ON UPDATE NO ACTION,
          CONSTRAINT 'fk_ID_Usuario4'
            FOREIGN KEY ('ID_Usuario4')
            REFERENCES 'BD_EasyMaker_0'.'Usuarios' ('ID_Usuario')
            ON DELETE NO ACTION
            ON UPDATE NO ACTION,
          CONSTRAINT 'fk_ID_Usuario5'
            FOREIGN KEY ('ID_Usuario5')
            REFERENCES 'BD_EasyMaker_0'.'Usuarios' ('ID_Usuario')
            ON DELETE NO ACTION
            ON UPDATE NO ACTION,
          CONSTRAINT 'fk_ID_Imagen'
            FOREIGN KEY ('ID_Imagen')
            REFERENCES 'BD_EasyMaker_0'.'Imagenes' ('ID_Imagen')
            ON DELETE NO ACTION
            ON UPDATE NO ACTION,
          CONSTRAINT 'fk_ID_Proyecto'
            FOREIGN KEY ('ID_Proyecto')
            REFERENCES 'BD_EasyMaker_0'.'Proyectos' ('ID_Proyectos')
            ON DELETE NO ACTION
            ON UPDATE NO ACTION)
        ENGINE = InnoDB