RE: Delete one record, automatically delete other records...

[Chris W. Parker]

Chris 
on Monday, August 07, 2006 6:19 PM said:

> Foreign keys with an "on delete cascade" should do it.
> 
> http://dev.mysql.com/doc/refman/5.1/en/example-foreign-keys.html


Thanks everyone!

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Delete one record, automatically delete other records...

[Chris]

Chris W. Parker wrote:

Hello,

I'm not sure if this is possible (or what it's called, and how to search
for it) at the db layer or if this has to be done at the application
layer... I would like to be able to delete one record in one table and
then automatically (without making an extra call to the db) delete other
records. If I'm using this term correctly, I think I'm trying to avoid
having orphan records.

In my specific case I want to delete a product from my database and then
automatically delete all associated records like the category and
manufacturer relationships.

BTW, I'm using MySQL 4.1.20.

I'm not really looking for code snippets but rather the name for this
idea and/or links so I can rtfm. :)


You'll need to use innodb tables.

Foreign keys with an "on delete cascade" should do it.

http://dev.mysql.com/doc/refman/5.1/en/example-foreign-keys.html


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Delete one record, automatically delete other records...

[Tim Lucia]

You want cascade deletion via foreign keys.

http://dev.mysql.com/doc/refman/5.1/en/ansi-diff-foreign-keys.html

is one such place to learn more.

Tim

> -Original Message-
> From: Chris W. Parker [mailto:[EMAIL PROTECTED]
> Sent: Monday, August 07, 2006 9:11 PM
> To: mysql@lists.mysql.com
> Subject: Delete one record, automatically delete other records...
> 
> Hello,
> 
> I'm not sure if this is possible (or what it's called, and how to search
> for it) at the db layer or if this has to be done at the application
> layer... I would like to be able to delete one record in one table and
> then automatically (without making an extra call to the db) delete other
> records. If I'm using this term correctly, I think I'm trying to avoid
> having orphan records.
> 
> In my specific case I want to delete a product from my database and then
> automatically delete all associated records like the category and
> manufacturer relationships.
> 
> BTW, I'm using MySQL 4.1.20.
> 
> I'm not really looking for code snippets but rather the name for this
> idea and/or links so I can rtfm. :)
> 
> 
> 
> Thanks,
> Chris.
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Delete one record, automatically delete other records...

[Daniel da Veiga]

On 8/7/06, Chris W. Parker <[EMAIL PROTECTED]> wrote:

Hello,

I'm not sure if this is possible (or what it's called, and how to search
for it) at the db layer or if this has to be done at the application
layer... I would like to be able to delete one record in one table and
then automatically (without making an extra call to the db) delete other
records. If I'm using this term correctly, I think I'm trying to avoid
having orphan records.

In my specific case I want to delete a product from my database and then
automatically delete all associated records like the category and
manufacturer relationships.

BTW, I'm using MySQL 4.1.20.

I'm not really looking for code snippets but rather the name for this
idea and/or links so I can rtfm. :)



This is called foreign keys constraints, MySQL has that on InnoDB,
take a look at:

http://dev.mysql.com/doc/refman/4.1/en/innodb-foreign-key-constraints.html

Hope that helped.

--
Daniel da Veiga
Computer Operator - RS - Brazil
-BEGIN GEEK CODE BLOCK-
Version: 3.1
GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V-
PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++
--END GEEK CODE BLOCK--

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Delete one record, automatically delete other records...

[Chris W. Parker]

Hello,

I'm not sure if this is possible (or what it's called, and how to search
for it) at the db layer or if this has to be done at the application
layer... I would like to be able to delete one record in one table and
then automatically (without making an extra call to the db) delete other
records. If I'm using this term correctly, I think I'm trying to avoid
having orphan records.

In my specific case I want to delete a product from my database and then
automatically delete all associated records like the category and
manufacturer relationships.

BTW, I'm using MySQL 4.1.20.

I'm not really looking for code snippets but rather the name for this
idea and/or links so I can rtfm. :)



Thanks,
Chris.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]