Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gswartz
Starting Member
2 Posts |
 Posted - 2008-02-07 : 16:33:40
|
| I haven't used cascading deletes in the past but we're starting a new database and it seems like a good way to go to keep data clean. Or at least it did seem like a good way until I ran some tests. I have 3 tables. People PeopleEmails Email(pk)peopleid (pk)peopleid (pk)emailidfname (pk)emailid addresslname emailtypepasswordIn this structure, the peopleemails table is simply an association table between the email and people table. I have setup up relationships in a diagram so that when a person is deleted, it cascades to peopleemails and removes the entry there. I also had a cascade set up hoping that when an entry was deleted from peopleemails, it would remove it from the email table but this is not happening. The relationship between email and peopleemail is primary key table email.emailid and foreign key table peopleemail.emailid. Is there a way to get this to work to remove the email address if a peopleemail entry is removed? Thanks. |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-02-07 : 20:25:18
|
| You have to specify cascade delete when create key. |
 |
|
|
gswartz
Starting Member
2 Posts |
Posted - 2008-02-08 : 08:34:45
|
quote:
Originally posted by rmiao
You have to specify cascade delete when create key.
If that's the case why can I go in and modify the relationship rules and set it for the delete option to cascade after I've created, saved and closed the table? It doesn't make sense that I am only able to create the cascade rule at the time that I define whatever field is the pk. Or am I misunderstanding you? |
|
|
|
pootle_flump
1064 Posts |
Posted - 2008-02-08 : 08:38:34
|
| You'll need to use a trigger. The cascade delete will go from parent to child, not child to parent. If you want an email address deleting when you delete the person associated with it then it suggests that this is a one-to-one relationship, in which case you don't need the intermediate table anyway. |
|
|
|
pootle_flump
1064 Posts |
Posted - 2008-02-08 : 08:44:43
|
| Sorry - this is either a one-to-one or one-to-many relationship right? You only need three tables to model a many-to-many relationship.If this does not make sense please could you script out the DDL including all PKs and FKs (your desc above is ambiguous)?Ta! |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-02-08 : 22:05:25
|
| >> why can I go in and modify the relationship rules and set it for the delete option to cascade after I've created, saved and closed the table?Sql will recreate keys if it ever works. |
|
|
|
|
|
|
help with cascading deletes