| Author |
Topic |
|
Mondeo
Constraint Violating Yak Guru
287 Posts |
 Posted - 2007-09-18 : 09:57:59
|
| My first experience with a constraint.Table 1 Customers, primary Key customerIDTable 2 Orders, has column customerID from Table 1If a customer is deleted I want to delete all orders with that customer ID.How do I do it using management studio 2005 express?Thanks |
|
|
nathans
Aged Yak Warrior
938 Posts |
Posted - 2007-09-18 : 13:59:25
|
| Not sure what you mean by "using management studio..."delete ofrom dbo.Customers cinnerjoin dbo.Orders o on c.CustomerID = o.CustomerIDwhere c.CustomerID = @CustomerIDNathan Skerl |
 |
|
|
Zoroaster
Aged Yak Warrior
702 Posts |
Posted - 2007-09-18 : 14:30:54
|
He means he wants to add a cascading delete constraint, do it in a query: ALTER TABLE table1ADD CONSTRAINT fk_customerFOREIGN KEY (customerID) REFERENCES table2 (customerID) ON DELETE CASCADE GO
Future guru in the making. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Zoroaster
Aged Yak Warrior
702 Posts |
Posted - 2007-09-18 : 14:55:33
|
quote:
Originally posted by X002548
be bery bery carefulIt's Wabbit seasonBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam
Right, Mondeo - please proceed with caution, research throughly, make sure you have backups and test, test, test before applying in production.
Future guru in the making. |
|
|
|
nathans
Aged Yak Warrior
938 Posts |
Posted - 2007-09-18 : 15:19:35
|
| Ok, so for the poster's "first experience with a constraint" we want to suggest that he should use a cascading delete??yikes.I think hes better off learning how to navigate the relationships via tsql.Nathan Skerl |
|
|
|
Zoroaster
Aged Yak Warrior
702 Posts |
Posted - 2007-09-18 : 15:43:42
|
quote:
Originally posted by nathans
Ok, so for the poster's "first experience with a constraint" we want to suggest that he should use a cascading delete??yikes.I think hes better off learning how to navigate the relationships via tsql.Nathan Skerl
It was what he specifically asked for in the subject line.
Future guru in the making. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2007-09-18 : 16:08:48
|
quote:
Originally posted by Zoroaster
quote:
Originally posted by nathans
Ok, so for the poster's "first experience with a constraint" we want to suggest that he should use a cascading delete??yikes.I think hes better off learning how to navigate the relationships via tsql.Nathan Skerl
It was what he specifically asked for in the subject line.
Future guru in the making.
TrueBut if a 3 year old asked to play with your 357 magnum, would you give it to him/her?Besides, I have never impleted cascade anythingBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-09-18 : 16:34:02
|
| i have. it's not so bad..._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
nathans
Aged Yak Warrior
938 Posts |
Posted - 2007-09-18 : 16:49:03
|
quote:
Originally posted by spirit1
i have. it's not so bad...
What? The .357 or the cascade?Nathan Skerl |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-09-18 : 17:02:31
|
cascade  _______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Zoroaster
Aged Yak Warrior
702 Posts |
Posted - 2007-09-18 : 22:08:30
|
quote:
Originally posted by X002548TrueBut if a 3 year old asked to play with your 357 magnum, would you give it to him/her?Besides, I have never impleted cascade anythingBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam
My bad, I didn't realize the OP was akin to a 3 year old and a cascading delete to a pistol!
Future guru in the making. |
|
|
|
Mondeo
Constraint Violating Yak Guru
287 Posts |
Posted - 2007-09-19 : 06:43:12
|
| Thanks guys,Should have said i'm using a test environment for this, its more a learning excercise than something I need for a production request.I'll give the second post a try.Thanks |
|
|
|
postbryan
Starting Member
1 Post |
Posted - 2007-10-31 : 15:14:38
|
| Assuming you have the PK / FK relationships setup in your 2 tables, then you can add the cascade delete via the Management Studio 2005 console.Go to the TABLE1. Select the treeview node for teh table, and choose DESIGN from the menu. No, in the right hand DESIGN pane, select the column with the PK (lets say it is called Table1ID). Select RELATIONSHIPS from the context menu. Ypu should see a new window open, and on there is the PK FK relationship you defined between the tbales. There may be other defined, so select the one you are interested in. Notice you see a node called INSERT and UPDATE SPECIFICATION. Expand that. Set The DELETE rule to CASCADE. Voila. No SQL required, what could be simpler. Good luck! |
|
|
|
|
Cascade delete