mysql error 1215 can not add foreign key constraint

Navigation
0

Personally I've researched several other topics about this error and none solved my problem so I'm posting here for you. It is as follows: I have a table called "members" that already has content and I am creating a new table called member_photos which has nothing in it. In this table I have the following columns: id, member_id, photo. Both the member_id and the id of the members table are in the following configuration: int-11 and both tables are in the InnoDB engine. My problem is that when I try to create the Foreign key in the member_photo table it gives me the 1512 error. Why does this happen? and how to solve? 

mysql> desc membros;
+---------------------+------------------+------+-----+---------+----------------
| Field               | Type             | Null | Key | Default | Extra          |
+---------------------+------------------+------+-----+---------+----------------+
| id                  | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| nome                | varchar(100)     | YES  |     | NULL    |                |
| nascimento          | varchar(10)      | YES  |     | NULL    |                |
| falecimento         | varchar(10)      | YES  |     | NULL    |                |

mysql> desc membro_images;
+---------------+--------------+------+-----+---------+-----------------------------+
| Field         | Type         | Null | Key | Default | Extra                       |
+---------------+--------------+------+-----+---------+-----------------------------+
| id            | int(11)      | NO   | PRI | NULL    | auto_increment              |
| membro_id     | int(11)      | YES  | MUL | NULL    |                             |
| name          | varchar(255) | YES  |     | NULL    |                             |
| created_at    | timestamp    | YES  |     | NULL    | on update CURRENT_TIMESTAMP |
| updated_at    | timestamp    | YES  |     | NULL    | on update CURRENT_TIMESTAMP |
+---------------+--------------+------+-----+---------+-----------------------------+
    
asked by anonymous 15.03.2017 / 18:55

2 answers

2

The problem is in the membro_id field that accepts and defaults to a nulo , in addition to the id field in the members table is set to not accept negative numbers, type int(11) unsigned . How do I expect mysql to do this type of constraint? Fields must have the same data type / length.

I used the same fields as the tables you provided and did a quick test, passing the value to membro_id as not null. 

create table membros (
  id int not null auto_increment comment 'pk_membros_id',
  nome varchar (100) not null,
  nascimento varchar (10),
  falecimento varchar (10),
  constraint pk_membros_id primary key (id)
  );

  insert into membros
    (nome,nascimento,falecimento)
  values
    ('Maria Joaquina Amaral Pereira', '1914-03-23', '1993-10-07');

 create table membros_fotos (
   id int not null auto_increment comment 'pk_membros_ft_id',
   membro_id int not null,
   name varchar (255),
   created_at timestamp not null default current_timestamp,
   update_at timestamp not null default current_timestamp on update current_timestamp,
   constraint pk_membros_ft_id primary key (id),
   constraint fk_membros_id_ft foreign key (membro_id) references membros(id)
 );

 insert into membros_fotos
   (membro_id,name)
 values (1,'images/membro_id_001.jpg');

See working in SQL Fiddle

    
15.03.2017 / 21:26
0

John, To establish a constraint between two tables, the fields MUST BE THE SAME TYPE AND SIZE. You need to check if the member table exists for the "member_id".

    
15.03.2017 / 19:04
Is there any way to include the Wordpress header in an external page that I created? Is there a way to use a MySQL table in developing an app with ionic and cordova?