Error Code: 1215. Can not add foreign key constraint

Question

Error Code: 1215. Can not add foreign key constraint

Navigation

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

0

I try to create a couple of tables in MySQL, this is the sentence to create the first one:

----- Creamos la Tabla de Usuarios del Sistema

Create table if not exists USUARIO
(
ID_USUARIO     BIGINT UNSIGNED not null auto_increment comment 'Id de la Tabla de Usuarios',
FICHA_LARGA    varchar(10)     not null                comment 'Representa el numero del usuario',
FICHA_CORTA    varchar(10)     not null                comment 'Representa el numero del usuario',
NOMBRE         varchar(50)     not null                comment 'Nombre Completo del Usuario',
CORREO         varchar(50)     not null                comment 'Correo de notificacion del usuario',
USUARIO_ACTIVO char(1)         not null default 'Y'    comment 'Bandera que indica si el usuario esta activo',
Primary Key(ID_USUARIO)
);

However, when trying to create the following table, I get the error:

(Error Code: 1215. Can not add foreign key constraint).

----- Creamos la Tabla con la Cabecera de Venta del Sistema

Create table if not exists CABECERA_VENTA
(
ID_CABECERA_VENTA  Bigint UNSIGNED not null auto_increment            comment 'Id de la Tabla de Cabecera de Ventas',
ID_USUARIO         Bigint          not null                           comment 'Id del Usuario que realiza la compra',
FECHA_CREADO       Datetime        not null default CURRENT_TIMESTAMP comment 'Fecha y Hora de Venta',
MONTO_TOTAL        Decimal         not null                           comment 'Monto Total de la Venta Realizada',
PROCESADA          Char(1)         not null default 'N'               comment 'Bandera para indicar si la Venta ya fue procesada en un Corte',
FECHA_CORTE        Date                     default null              comment 'Fecha Corte en la que fue incorporada la Venta',
Primary Key(ID_CABECERA_VENTA),
Constraint ID_USUARIO
Foreign Key(ID_USUARIO) 
References USUARIO(ID_USUARIO)
);

Thank you in advance for the help provided.

mysql constraints

asked by user50469 06.07.2017 в 06:35

source

3 answers

Export DataGridView headers like hide keyboard when you play outside and inside a textfield when you have a scrollview

score 2 · Answer 1

Your problem is a pretty simple detail, you can notice that when you define the field ID_USUARIO in the table USUARIO you do it like this:

ID_USUARIO BIGINT UNSIGNED not null auto_increment comment 'Id de la Tabla de Usuarios'

However when you define the field ID_USUARIO that will serve as foreign in the table CABECERA_VENTA you do it like this:

ID_USUARIO Bigint not null comment 'Id del Usuario que realiza la compra'

You will notice that the first one describes them as UNSIGNED while the last one does not, you must understand that the foreign field must be of the same type as the one of origin and therefore your sentence fails.

score 0 · Answer 2

in the table HEAD_HEAD no longer use USER_ID, but another name eg. ID_US And where to do the constraints add FOREIGN KEY (ID_US) REFERENCES user (USER_ID); And if you will make modifications or delete some detail of the sale I recommend you do it in cascade ex.

FOREIGN KEY (ID_US) REFERENCES user (USER_ID) ON DELETE CASCADE ) ENGINE = #TU DB #

I hope and serve you:)

score 0 · Answer 3

0

is for the value UNSIGNED that you have declared in the table where is primary key in the new table where it happens to be foreign has to have the same attributes

answered by 08.07.2017 в 02:16