Bug #44963 SQLForeignKeys incorrect update_rule/delete_rule info of cascade rule

• Submitted: 19 May 2009 19:34
• Modified: 19 May 2009 19:50
• Reporter: Farid Zidan (Candidate Quality Contributor)
• Status: Won't fix
• Category: Connector / ODBC
• Severity: S2 (Serious)
• OS: Windows (XP SP3)
• CPU Architecture: Any

[19 May 2009 19:34] Farid Zidan

Description:
MySQL 5.0.67-community-nt via TCP/IP
MySQL ODBC 5.1 Driver 5.01.05.00 also 
MySQL ODBC 3.51 Driver 3.51.21.00

Drivers returns SQL_RESTRICT for update_rule and delete_rule for foreign key created with on update cascade and on delete cascade actions.

You can verity that the foreign key is correctly created with cascade rules by viewing table info in MySQL Administrator table editor

How to repeat:
create table test_parent(col1 int not null, primary key(col1));
create table test_child(col1 int not null, primary key(col1));
alter table test_child
   add constraint FK_COL1 foreign key (col1)
      references test_parent (col1)
      on update cascade
      on delete cascade;

ODBC driver should return SQL_CASCADE for update_rule/delete_rule columns for SQLForeignKeys resultset for foreign key FK_COL1. It incorrectly returns SQL_RESTRICT

[19 May 2009 19:50] Jim Winstead

Connector/ODBC 3.51 always reports SQL_CASCADE for UPDATE_RULE and DELETE_RULE.

Connector/ODBC 5.1 will only report the correct value for servers > 5.1. The correct value is not accessible from the server (besides parsing the output of SHOW CREATE TABLE) without INFORMATION_SCHEMA. REFERENTIAL_CONSTRAINTS. Otherwise, it also always reports SQL_CASCADE.