Foreign key in MySQL with Visual Basic doesn't work

I'm working in Visual Basic with MySql as data base server using innodb tables.

I made a foreign key between 2 tables with a restriction ON DELETE NO ACTION.
But when I delete a row on a parent table, the foreign key doesn't restrict the operation.

What can i do?

Thanks in advance.

Just some ideas off the top of my head.

Are you using:

1. MySQL version 3.23.43b or higher?
2. Are both tables InnoDB type?
3. Have you created an index where both the Foreign Key and the referenced key are the first columns?
4. The Foreign and referenced keys must be of the same column type (no type conversion can take place)
5. Ensure none of the keys contain NULL as I believe this prevents InnoDB from checking the constraint.

Remember that InnoDB does not auto-create indexes. You must create these manually!

What syntax did you use to create the constraint?

I made all the steps you wrote, but it doesn't work .

The syntax I used is:

ALTER TABLE cabfac
add constraint clientes
foreign key (codCliente)
references clientes (codCliente)
on delete no action
on update no action;

I can't check the syntax until I see the two tables you have referenced. Can you post the table structures (cabfac & clientes)?

Also, did you create the indexes BEFORE you made the Foreign Key constraints? That's an important step.

Hi,

Not sure whether my reply will reach you as this thread is a bit ancient, but the problem you have encountered is due to a discrepancy in the way MySQL implements the NO ACTION option compared with the ANSI SQL-92 standard and most other relational database systems.

I have put a comment about this in Section 14.4.5.2 FOREIGN KEY Constraints in the MySQL online documentation, which reads as follows:

A further deviation from ANSI SQL-92 standards, at the time of writing, is the way MySQL treats the ON DELETE NO ACTION and ON UPDATE NO ACTION clauses, should you attempt to use them.

Under the ANSI SQL-92 standard, NO ACTION means "no action" in the sense that an attempt to delete or update a primary key value will not be allowed to proceed if there is a related foreign key value in the referenced table (Gruber, 2000:181). It is therefore the ANSI syntax for explicitly enforcing referential integrity and is supported by SQL Server 2000, Oracle 9 amongst other database systems.

However, in MySQL it should be noted that NO ACTION cannot be used for this purpose at present. In fact, including an ON DELETE NO ACTION or ON UPDATE NO ACTION clause in a MySQL/InnoDB CREATE TABLE statement *allows* the deletion or update of a primary key value regardless of whether it appears as a foreign key value in the related table.

If standard referential integrity restrcitions are required for InnoDB tables in MySQL, the ON DELETE RESTRICT/ON UPDATE RESTRICT syntax should be used instead. Or, alternatively, you can omit this clause entirely and the restriction will be applied as it is the default behaviour for a FOREIGN KEY...REFERENCES clause in a MySQL InnoDB table.

This point was confirmed recently by Victoria Reznichenko on the MySQL General Discussion List (URL), following a long thread on the subject in 2003 (URL).

According to Victoria, this discrepancy will be addressed in the future, so that NO ACTION becomes a synonym for RESTRICT and acts in the ANSI standard way. In the meantime, however, other users may find it useful to be aware of this discrepancy.

I hope this helps you, or anyone else who has been puzzled by this behaviour.