Confusion on delete in sql

Question

I have a rather fundamental/naive question.

If I have 2 tables that are related i.e. one has a foreign key of the other.

I.e. Table A has 1-N relationship with Table B and Table B therefore has an extra column which for the id of corresponding record in Table A as foreign key.

If I delete an entry in Table A will this delete automatically delete the corresponding entry in Table B (which has the deleted idx as foreign key) or do I have to delete it programmatically?

Does this depend on the database or how the tables are created? I am not clear on this.

Answer 1

It depends on how the table were created.

There is an option for ON DELETE CASCADE (as well as others) which would delete the entries that are related to the rows you are deleting.

Here is an example of the create table script to do this:

http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.sqls.doc/sqls292.htm

And here is a better explanation on cascading and how foreign key constraints can function:

http://en.wikipedia.org/wiki/Foreign_key

Answer 2

You can use ON DELETE CASCADE flag for the foreign key which will delete the rows of the foreign key it its primary key is deleted