You need one DELETE statement for each table that you want
to delete from. One way to make this simpler is to
put the DELETE statements for "child" tables in a TRIGGER
which fires automatically when you DELETE from the "parent"
table.
SQL Server can prevent you from creating orphan records
but it won't do cascading deletes for you unless you
have triggers set up to do it.
Nick
-----Original Message-----
From: Rudy Rustam [mailto:[EMAIL PROTECTED]]
Sent: Monday, September 11, 2000 11:03 AM
To: [EMAIL PROTECTED]
Subject: Re: SQL - Relational issue
Back to the scenario, does it mean I have to write 10 queries to delete all
related records on the rest of the tables?
Is that what you guys doing? Some comments plz...
hey, thanks Ant
----- Original Message -----
From: Anthony Geoghegan <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, September 11, 2000 4:02 PM
Subject: RE: SQL - Relational issue
> Hi Rudy
>
> [You wrote:]
>
> System : NT4, MSSQL 7 and CF 4.5
>
> Scenario:
> I have 2 tables, USERS and CARS with One-to-Many relationship.
> If I delete a user, then all cars that he has should be deleted too.
>
> I use 2 queries to delete a user in the USERS and all his cars in the =
> CARS table.
>
> Is there a more efficient way to just delete a user in USERS table and =
> all connection to this will also be deleted too?
> Imagine if there are more than 10 relational tables.
> And Can setting up a Relation Diagram in SQL help me? (I did some tests =
> and it doesn't turn out to be what I expected)
>
> Suggestions and Advices are very much welcome.
>
> [Ant says:]
> If you enforce referential integrity in MS SQL the manual says:
>
> "When you enforce referential integrity, SQL Server prevents users from:
>
> Adding records to a related table if there is no associated record in the
> primary table.
> Changing values in a primary table that result in orphaned records in a
> related table.
> Deleting records from a primary table if there are matching related
> records."
>
> This means that SQL will manage the references for you!
>
> Regards,
> Anthony Geoghegan.
> Lead Developer,
> IFTN
> http://www.wow.ie
> mailto:[EMAIL PROTECTED]
>
> --------------------------------------------------------------------------
----
> Archives: http://www.mail-archive.com/[email protected]/
> To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.
----------------------------------------------------------------------------
--
Archives: http://www.mail-archive.com/[email protected]/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.
**********************************************************************
Information in this email is confidential and may be privileged.
It is intended for the addressee only. If you have received it in error,
please notify the sender immediately and delete it from your system.
You should not otherwise copy it, retransmit it or use or disclose its
contents to anyone.
Thank you for your co-operation.
**********************************************************************
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/[email protected]/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
