Error running SQL script in MySQL: Error Code: 1215. Can not add foreign key constraint

Navigation
5

I am trying to execute this script, and it does not allow me to add the foreign keys, and I do not understand why. Can someone help me? 

CREATE TABLE IF NOT EXISTS 'clientes' (
  'numclie' int(11) NOT NULL,
  'nombre' varchar(20) CHARACTER SET utf8 NOT NULL,
  'repclie' int(11) DEFAULT NULL,
  'limitecredito' int(11) NOT NULL,
  PRIMARY KEY ('numclie')
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS 'oficinas' (
  'oficina' int(11) NOT NULL,
  'ciudad' varchar(20) CHARACTER SET utf8 NOT NULL,
  'region' varchar(20) CHARACTER SET utf8 DEFAULT NULL,
  'dir' int(11) DEFAULT NULL,
  'objetivo' decimal(19,4) DEFAULT NULL,
  'ventas' decimal(19,4) DEFAULT '0.0000',
  PRIMARY KEY ('oficina')
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS 'empleados' (
  'numemp' int(11) NOT NULL,
  'nombre' varchar(20) CHARACTER SET utf8 NOT NULL,
  'edad' int(11) DEFAULT NULL,
  'oficina' int(11) DEFAULT NULL,
  'titulo' varchar(20) CHARACTER SET utf8 DEFAULT NULL,
  'contrato' datetime DEFAULT NULL,
  'jefe' int(11) DEFAULT NULL,
  'cuota' decimal(19,4) DEFAULT NULL,
  'ventas' decimal(19,4) NOT NULL DEFAULT '0.0000',
  'empleadoscol' varchar(45) DEFAULT NULL,
  PRIMARY KEY ('numemp')

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS 'productos' (
  'idfab' varchar(20) CHARACTER SET utf8 NOT NULL,
  'idproducto' varchar(20) CHARACTER SET utf8 NOT NULL,
  'descripcion' varchar(20) CHARACTER SET utf8 NOT NULL,
  'precio' decimal(19,4) DEFAULT NULL,
  'existencias' int(11) NOT NULL,
  PRIMARY KEY 'CPProd' ('idfab','idproducto')
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS 'pedidos' (
  'codigo' int(11) NOT NULL AUTO_INCREMENT,
  'numpedido' varchar(20) NOT NULL,
  'fechapedido' datetime NOT NULL,
  'clie' int(11) NOT NULL,
  'rep' int(11) NOT NULL,
  'fab' varchar(20) NOT NULL,
  'producto' varchar(20) NOT NULL,
  'cant' int(11) NOT NULL,
  'importe' decimal(19,4) DEFAULT NULL,
  'pedidoscol' varchar(45) DEFAULT NULL,
  PRIMARY KEY ('codigo'),
  UNIQUE KEY 'UX_numpedido' ('codigo'),
FOREIGN KEY ('rep') REFERENCES EMPLEADOS ('numemp'),
 FOREIGN KEY ('clie') REFERENCES CLIENTES('numclie'),
FOREIGN KEY ('fab','producto') REFERENCES PRODUCTOS('idfab','idproducto')
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=31 ;

alter table 'empleados'
add FOREIGN KEY ('jefe') REFERENCES EMPLEADOS ('numemp');

 alter table 'empleados'
 add FOREIGN KEY ('oficina') REFERENCES OFICINAS ('oficina');
alter table 'clientes'
add FOREIGN KEY ('repclie') REFERENCES EMPLEADOS ('numemp');
alter table 'oficinas'
add FOREIGN KEY ('dir') REFERENCES EMPLEADOS ('numemp');

CREATE TABLE IF NOT EXISTS 'sysdiagrams' (
  'diagram_id' int(11) NOT NULL AUTO_INCREMENT,
  'name' varchar(128) CHARACTER SET utf8 NOT NULL,
  'principal_id' int(11) NOT NULL,
  'version' int(11) DEFAULT NULL,
  'definition' longblob,
  PRIMARY KEY ('diagram_id'),
  UNIQUE KEY 'UK_principal_name' ('principal_id','name')
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;
    
asked by Cricket 05.05.2016 в 09:25
source

4 answers

1

The sensitivity to uppercase and lowercase of MySQL is given by the file system of the operating system. Extracted from the documentation .

  

the case sensitivity of the underlying operating system plays a part   in the case sensitivity of database, table, and trigger names. Este   means such names are not case sensitive in Windows, but are case   sensitive in most varieties of Unix.

On the other hand, ANSI SQL as a language is indifferent to uppercase and lowercase.

So there are two alternatives:

  • Use lowercase letters for the names of tables and triggers in every SQL statement

  • Configure MySQL using the variable lower_case_table_names so that it is case-insensitive to table names and use lowercase for the names of triggers

Keep in mind that the first point may not be easily realizable with existing code that does not have this MySQL theme present.

    
answered by 09.09.2016 в 04:41
1

The names of the tables must be in lowercase, they are case sensitive, then you were directly referring to another totally different table. The sensitivity of the operating system influences that in mysql the databases, tables and triggers are also case sensitive.

For example, this would not work: 

mysql> SELECT * FROM my_table WHERE MY_TABLE.col=1;

For the following explanation:

  

Even though the database, the table and the triggers are not sensitive in some platforms, one of these cases should not be referenced using upper and lower case within the same statement. The following statement would not work because it refers to a different table both when we put my_table and then MY_TABLE

I leave you the official manual where this is explained.

link

    
answered by 06.07.2017 в 21:00
0

The names of the tables must be in lowercase, mysql is case sensitive, if you put the names in uppercase you are referencing another table.

For example 

FOREIGN KEY ('rep') REFERENCES EMPLEADOS ('numemp'),
 FOREIGN KEY ('clie') REFERENCES CLIENTES('numclie'),
FOREIGN KEY ('fab','producto') REFERENCES PRODUCTOS('idfab','idproducto')

It should be 

FOREIGN KEY ('rep') REFERENCES empleados ('numemp'),
 FOREIGN KEY ('clie') REFERENCES clientes('numclie'),
FOREIGN KEY ('fab','producto') REFERENCES productos('idfab','idproducto')
    
answered by 17.05.2016 в 16:05
0

Something similar happened to me and I found the problem had a certificate field in the main table and in another foreign chart, of the same type but of different collation, I changed the collation according to the main table and it worked wonderfully

    
answered by 21.06.2017 в 15:04
Ruby on rails 4: Render and / or redirect called multiple times in action. using Devise Does not C ++ work well?