1

In MySQL I have two tables

roles

role_id | role_name

user_roles

user_id | role_id

in user_roles table role_id is a FOREIGN KEY. Now I want to restrict on deletion of row in roles table if role_id is also exists on user_roles table

I think this could be done either from Integrity setting while creating table. I don't know what does CASCADE or RESTRICT do and how they works.

I read somewhere that FOREIGN KEY does not supported by Storage Engine MyISAM so please guide me for both Storage Engine (MyISAM and InnoDB)

PHP Ferrari
  • 15,754
  • 27
  • 83
  • 149

2 Answers2

1

everything is here: http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

update: about myisam: How to use delete cascade on MySQL MyISAM storage engine?

Community
  • 1
  • 1
long
  • 3,692
  • 1
  • 22
  • 38
1

You read it right that MyISAM engine does not support the foreign key integrity checks. My Advice is to go for innodb.

There is another option which most of the times, I use when I want to stick to MyISAM table, i.e Manage all these at the front end. But At times this is not good as your adding more headaches at front end.

Go for InnoDB

azzaxp
  • 697
  • 5
  • 11
  • 26