MySQL Foreign Keys

Question

MySQL Foreign Keys

Navigation

#1 by (1 votes)

0

I have a problem when I try to create a table with two "foreign key", I have a user table:

CREATE TABLE IF NOT EXISTS usuario (
  id int(4) NOT NULL AUTO_INCREMENT,
  email varchar(20) NOT NULL,
  nombre varchar(20) NOT NULL,
  contrasena varchar(40) NOT NULL,
  rol varchar(10),
  PRIMARY KEY (id, email)
) ENGINE=InnoDB AUTO_INCREMENT=1;

and another table called chat, which has the two "foreign keys":

CREATE TABLE IF NOT EXISTS chat (
  id int(4) NOT NULL AUTO_INCREMENT,
  nombreChat varchar(20) NOT NULL,
  idUsuario int(4) NOT NULL,
  emailUsuario varchar(20) NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (idUsuario) REFERENCES usuario (id),
  FOREIGN KEY (emailUsuario) REFERENCES usuario (email)
) ENGINE=InnoDB AUTO_INCREMENT=1;

When I try to execute it MySQl gives me this error:

Error: 150 "Foreign key constraint is incorrectly formed"

I've tried this other syntax too:

ALTER TABLE chat
  ADD CONSTRAINT chat_fk1 FOREIGN KEY (idUsuario) REFERENCES usuario (id) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT chat_fk2 FOREIGN KEY (emailUsuario) REFERENCES usuario (email) ON DELETE CASCADE ON UPDATE CASCADE;

mysql

asked by DavidZam 25.02.2018 в 20:10

source

1 answer

What is the reason for this error in Ajax? Message asking if I am sure of this action in html and php

score 1 · Accepted Answer

first:

You need to complete the sentence on foreign keys in this way:

CONSTRAINT pk_chat_users FOREIGN KEY (idUsuario) REFERENCES usuario(id)

Second you must have only a foreign key linked to a primary key between two tables, I mean that connecting users with chat by the primary key of users, you have to declare a foreign key in chat for users.

Create two tables with the considerations that I mention

I think your example should be as follows

CREATE TABLE IF NOT EXISTS usuario (
  id int(4) NOT NULL AUTO_INCREMENT,
  email varchar(20) NOT NULL,
  nombre varchar(20) NOT NULL,
  contrasena varchar(40) NOT NULL,
  rol varchar(10),
  PRIMARY KEY (id, email)
) ENGINE=InnoDB AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS chat (
  id int(4) NOT NULL AUTO_INCREMENT,
  nombreChat varchar(20) NOT NULL,
  idUsuario int(4) NOT NULL,
  emailUsuario varchar(20) NOT NULL,
  PRIMARY KEY (id),
  CONSTRAINT FOREIGN KEY fk_chat_usuario (idUsuario) REFERENCES usuario (id)
  -- FOREIGN KEY (emailUsuario) REFERENCES usuario (email) creo que esta línea no es necesaria
) ENGINE=InnoDB AUTO_INCREMENT=1;