Setting foreign key constraint...

[kkeyan]

Hi All,

I have a table, ACL which have 3 fields each of which referring to the primary key field of another table.

Syntax:
------

create table ACL(module varchar(30) NOT NULL,
>operation varchar(35) NOT NULL,
>role varchar(30) NOT NULL,
>CONSTRAINT f_1 FOREIGN KEY (module) REFERENCES module(module_name) ON DELETE CASCADE,
>CONSTRAINT f_2 FOREIGN KEY (operation) REFERENCES operation(operation_name) ON DELETE CASCADE,
>CONSTRAINT f_3 FOREIGN KEY (role) REFERENCES role(role_name) ON DELETE CASCADE);

Table was created as thus. But when I tried inserting values not existing in either of the table-column, it still inserts into the table.

Could someone enlighten me what could be the problem and what is the way of achieving this.

I am using MySQL version 3.23.52.

Thanks in advance for any valuable response.
Karthikeyan

[kkeyan]

Hi All,

I found that foreign key constraint is not available with version 3.23.52. Its only available with 3.23.56 upwards with InnoDB tables. All the tables referenced must also be of InnoDB type.

Also MySQL -Max binary should be installed for proper functioning of InnoDB.

Check here for more details..

----
karthikeyan