db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Bill <bhal...@collaborativefusion.com>
Subject Re: Cascading delete?
Date Mon, 07 Apr 2003 22:02:25 GMT
On Mon, 2003-04-07 at 17:18, Gabriel Bauman wrote:
> Okay, so...
> 
>  > <table name="CHILD_TABLE">
>  >    <column
>  >      name="child_id"
>  >      required="true"
>  >      primaryKey="true"
>  >      type="INTEGER"
>  >      autoIncrement="true"
>  >    />
> >   <foreign-key foreignTable="PARENT_TABLE"
> >     name="PARENT_TABLE_FK"
> >     onDelete="cascade">
> >     <reference local="child_id" foreign="parent_id"/>
> >   </foreign-key>
>  > </table>
> 
> ... given this example, if I delete or update the OM's ParentTable 
> object, the associated records in CHILDTABLE should be deleted, right?

This is from an online sql tutorial: http://www.firstsql.com/tutor6.htm

These actions are controlled by the referential integrity effects
declarations, called referential triggers by SQL92. The referential
integrity effect actions defined for SQL are:

* NO ACTION -- the change to the referenced (primary key) table is not
performed. This is the default.
* CASCADE -- the change to the referenced table is propagated to the
referencing (foreign key) table.
* SET NULL -- the foreign key columns in the referencing table are set
to null. 

Update and delete have separate action declarations. For CASCADE, update
and delete also operate differently:

* For update (the primary key column values have been modified), the
corresponding foreign key columns for referencing rows are set to the
new values.
* For delete (the primary key row is deleted), the referencing rows are
deleted. 

If you read this carefully, you'll see that your logic is backward. 
Cascading deletes do not cascade to parents, they cascade to children.

the 'restrict' option like the others is another SQL concept but I dont
think its quite standard in implementation.  In postgresql, it is
definitely the same as no action.  Check your db doc on this one.



Mime
View raw message