Foreign Key does not respect referential integrity

Asked

Viewed 355 times

4

I’m trying to put together tables and add one chave estrangeira, for example, a shopping list has referência of id from the buyers' table.

I did it this way:

alter table compras add foreign key (compradores_id) references compradores(id);
insert into compras (valor, recebido, data, compradores_id) values (1500, 1, '2016-06-02', 15);

*As id's of the buyers table only go up 3 *Both columns (compradores.id e compras.compradores_id) were configured as int not null

He accepts the command of insert good and records, how can I fix that ?

mysql foreign-key
  • 1

    You could post the fields and type of each field of these two tables?

  • 1

    Your table engine is innodb or mysam?

  • 1

    You need to have Constraint, you have to be of the same type, integer (11), integer (11), unsignet, not null and so on...

  • @How do I verify that ?

  • @Gumball, I’ve spelled them both the same :/ so I don’t understand why you’re bugging

  • 1

    In the creation of the table at the end has this information.

  • 1

    @rray, both are Myisam

  • 1

    Try to put Innodb

Show 3 more comments

2 answers

4


The referential integrity in Mysql works only when tables use the innoDB engine. The first step is to change the type of the tables and then apply the Foreign key, since the values must obey the constraints.

ALTER TABLE nome_da_tabela ENGINE=INNODB

Mysql - innoDB documentation

  • Thank you so much, so I switched to INNODB ran flat :D

1

According to the W3 Schools the correct command would be as follows:

ALTER TABLE compras
ADD CONSTRAINT fk_compras_compradores_20161215
FOREIGN KEY (compradores_id)
REFERENCES compradores(id)

It is also important to check whether compradores_id is of the same type as id of the buyer.

  • 2

    I may be wrong, but I don’t think W3scholls has anything to do with W3C.

  • Opa, pardon rsrsrsr. Doing a thousand things here rsrsrsr

  • Continues inserting, even with the addition of constraint

  • 1

    If you can post the structure of the tables, it will help to elucidate a little more

Browser other questions tagged mysql foreign-key

You are not signed in. Login or sign up in order to post.