Win a copy of Learn Spring Security (video course) this week in the Spring forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

automatically delete or insert

 
jonathan Greens
Ranch Hand
Posts: 139
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
in sql, suppose i have two tables a and b, is it possible to make the deletion of a row in table a triggers the deletion of a corresponding row in b? same for insertion. is this related to foreign key constraint?
thanks
 
Masoud Kalali
Author
Ranch Hand
Posts: 531
Java Mac OS X Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
a good solution for this job , in case that your database support Triggers is to use before Insert and after Delete trigers.

you may set these triggers to be fired when you insert a row into that main table to insert the same row into the secondary table .

also on deletition of a row from table one you can delete the same row from table two.

you can not use a foreign key for this case , foreign key usage is difference .
 
stu derby
Ranch Hand
Posts: 333
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by jonathan Greens:
in sql, suppose i have two tables a and b, is it possible to make the deletion of a row in table a triggers the deletion of a corresponding row in b? same for insertion. is this related to foreign key constraint?
thanks


Triggers are one way to do this; they're more flexible than foreign key constraints, an "on delete" trigger on table a can do whatever you're able to program on table b.

Foreign key constraints can also do automatic deletion, for example if table b has a FK reference to table a and the refered to row in a is deleted, normally the database will return an error and prevent the deletion from being committed. However, if the foreign key constraint was established with cascade deletion, then instead of throwing the error on a delete in a, the refering row(s) in b will be deleted.

Here's how such a foreign key constraint is set in MySQL, most other DBs that support foriegn keys do it similarly, I think that's how it's specified in the SQL standard...
http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html
 
stu derby
Ranch Hand
Posts: 333
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by jonathan Greens:
in sql, suppose i have two tables a and b, is it possible to make the deletion of a row in table a triggers the deletion of a corresponding row in b? same for insertion. is this related to foreign key constraint?
thanks


Oh, foreign keys can't do an insert though; for that, you have to use a trigger (if your database supports them, not all do).
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic