Create Table Foreign key failure

0

I want to create 3 foreign keys in a table such as this:

DROP TABLE IF EXISTS puo;CREATE TABLE IF NOT EXISTS puo (
    ano INT ( 4 )NOT NULL,
    empresa CHAR ( 8 )NOT NULL,
    acro_proyecto CHAR ( 10 )NOT NULL,
    partida VARCHAR ( 100 ) NOT NULL,
    proveedor VARCHAR (100) NOT NULL,
    formadepago VARCHAR (100) NOT NULL,
    solicitado_por CHAR (4) NOT NULL,
    aprobado_por CHAR (4) NOT NULL,
    puo VARCHAR ( 50 )NOT NULL,
    posicion VARCHAR (59) NOT NULL,
    producto_servicio VARCHAR ( 150 ) NOT NULL,
    cantidad INT NOT NULL,
    precio_unitario VARCHAR ( 10 ) NOT NULL,
    descuento_porcentaje VARCHAR ( 10 ) NOT NULL,
    iva VARCHAR ( 10 ) NOT NULL,
    precio_total VARCHAR ( 20 ) NOT NULL,
    descuento VARCHAR ( 20 ) NOT NULL,
    precio_uni_descuento VARCHAR ( 20 ) NOT NULL,
    iva_total VARCHAR ( 20 ) NOT NULL,
    total VARCHAR ( 20 ) NOT NULL,
    fecha TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY(puo),
    FOREIGN KEY (empresa) REFERENCES compania(acronimo) on DELETE CASCADE,
    FOREIGN KEY (solicitado_por) REFERENCES autorizados(acro_usuario) on DELETE CASCADE,
    FOREIGN KEY (aprobado_por) REFERENCES usuarios(acro_usuario) on DELETE CASCADE,
    FOREIGN KEY (acro_proyecto) REFERENCES proyectos(acro_proyecto) on DELETE CASCADE)
            Engine = INNODB

And I miss this line:

FOREIGN KEY (solicitado_por) REFERENCES autorizados(acro_usuario) on DELETE CASCADE,

To create the foreign keys, use these tables:

DROP TABLE IF EXISTS autorizados;CREATE TABLE IF NOT EXISTS autorizados (
    acro_usuario CHAR ( 4 )NOT NULL,
    nombre VARCHAR ( 15 )NOT NULL,
    apellidos VARCHAR ( 50 )NOT NULL,
    area_ui VARCHAR ( 30 )NOT NULL,
    email VARCHAR ( 50 )NOT NULL,
    empresa CHAR ( 8 ),
    fecha TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY(acro_usuario),
    FOREIGN KEY (empresa) REFERENCES compania(acronimo) on DELETE CASCADE)

DROP TABLE IF EXISTS compania;CREATE TABLE IF NOT EXISTS compania (
    acronimo CHAR ( 8 ),    
    compania VARCHAR ( 50 )NOT NULL,
    fecha TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
    PRIMARY KEY(acronimo))

I use two different user tables because each one is focused to perform different functions

    
asked by Alberto Cepero de Andrés 21.07.2017 в 02:28
source

1 answer

0

What I can think of is that the charset or the collation are different in the two tables, since the column you reference is PRIMARY KEY in the other table and therefore has a created INDEX.

As indicated by the documentation :

  

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.

Translation:

  

The corresponding columns in the foreign key and the key   referenced should have similar data types. The size and   sign of the integers must be the same. The length of the types   of chains does not have to be the same. For string columns there is no   binaries (character), the character set and the collation   They must be the same.

Another thing that occurs to me is that you are not creating the tables in the correct order and there is no table that you want to reference in the FOREIGN KEY

    
answered by 21.07.2017 в 10:44