RE: SQL - Relational issue
Just to take this one step further. As Nick says you can create a trigger which will do the deletes for you. Al you have to do is refer to the logical (conceptual) table in SQL called deleted which will hold record(s) that you have deleted from the main table. So you can write a trigger that deletes all records from the other tables based on the unique identifyer of the record(s) in the deleted table. -- Andrew Ewings Project Manager Thoughtbubble Ltd -- -Original Message- From: DeVoil, Nick [mailto:[EMAIL PROTECTED]] Sent: 11 September 2000 11:17 To: '[EMAIL PROTECTED]' Subject: RE: SQL - Relational issue 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/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=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/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
SQL - Relational issue
This is a multi-part message in MIME format. --=_NextPart_000_0086_01C01C09.22A55D80 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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. Thanks in advance. - Rudy --=_NextPart_000_0086_01C01C09.22A55D80 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable !DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" HTMLHEAD META content=3D"text/html; charset=3Diso-8859-1" = http-equiv=3DContent-Type META content=3D"MSHTML 5.00.2314.1000" name=3DGENERATOR STYLE/STYLE /HEAD BODY bgColor=3D#ff DIVFONT face=3DArial size=3D2System :nbsp;NT4, FONT face=3DArial = size=3D2MSSQL 7=20 and CF 4.5/FONT/FONT/DIV DIVFONT face=3DArial size=3D2/FONTnbsp;/DIV DIVFONT face=3DArial size=3D2Scenario:/FONT/DIV DIVFONT face=3DArial size=3D2I have 2 tables, USERS and CARS with = One-to-Many=20 relationship./FONT/DIV DIVFONT face=3DArial size=3D2If I delete a user, then all cars that = he has=20 should be deleted too./FONT/DIV DIVFONT face=3DArial size=3D2/FONTnbsp;/DIV DIVFONT face=3DArial size=3D2I use 2 queries to delete a user in the = USERS and=20 all his cars in the CARS table./FONT/DIV DIVnbsp;/DIV DIVFONT face=3DArial size=3D2Is there a more efficient way to just = delete a user=20 in USERS table and all connection to this will also be deleted = too?/FONT/DIV DIVFONT face=3DArial size=3D2Imagine if there are more thannbsp;10 = relational=20 tables./FONT/DIV DIVFONT face=3DArial size=3D2And Can setting up a Relation Diagram = in SQL help=20 me? (I did some tests and it doesn't turn out to be what I=20 expected)/FONT/DIV DIVnbsp;/DIV DIVFONT face=3DArial size=3D2Suggestions and Advices are very much=20 welcome./FONT/DIV DIVnbsp;/DIV DIVFONT face=3DArial size=3D2Thanks in advance./FONT/DIV DIVnbsp;/DIV DIVFONT face=3DArial size=3D2- Rudy/FONT/DIV/BODY/HTML --=_NextPart_000_0086_01C01C09.22A55D80-- -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
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/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
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/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
RE: SQL - Relational issue
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/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=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/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
