August 5, 2011 at 9:36 am
Dear Friends,
I have one table CityStates where I saved all States and Cities included in that state using StateId as ParentId For Cities.
The table has relation with itself with "StateId" as Primary key and "ParentId" as Foreign Key.
(Pls check attachment for table structure)
But when I tried to delete the State using StateId I'm getting an error message.
My problem is I want to delete all the records both cities and states
using that StateId.
Means if I want to delete the state then cities in that particular state should
be deleated.
Thanks in Adv
August 5, 2011 at 9:47 am
Cities and states are different entities with different properties. Model them as parent and child tables.
This problem is only the beginning, it will get much worse if you continue with the model.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 5, 2011 at 9:54 am
This is just a small DataSet where I'm using .NET code to extract it.
Now I want to delete the data using StateId.
Thanks
August 5, 2011 at 10:17 am
Maybe a better case would be Employee, supervisor, teams...
Won't get you any "you need to redo the design" types of answers.
August 5, 2011 at 11:44 am
I'm not sure if I'm following what you want to do, but can't you simply delete the rows?
DELETE CityStates
WHERE StateID = 1
OR PartneID = 1
August 5, 2011 at 11:51 am
Two ways to go about this. One is to set "on delete cascade" on the foreign key. That will make it so when you delete the state anything that references the state is deleted as well. I haven't used this and it makes me nervous because it makes it very easy to accidentally delete a lot of data.
The other is so do something like this:
delete from CityStates where parentid = 1
delete from CityStates where stasteid = 1
Small data set or not, you're likely to be better off long term not mixing entities like this. If there's any chance of this table growing at all the now is the time to plan for that.
August 5, 2011 at 2:17 pm
cfradenburg (8/5/2011)
Two ways to go about this. One is to set "on delete cascade" on the foreign key. ...Small data set or not, you're likely to be better off long term not mixing entities like this. If there's any chance of this table growing at all the now is the time to plan for that.
/soapbox on
The other big problem that I see is that you are using NULL as a value. Your records with NULL for a ParentID indicate that it is a state. Just in general you would be better off to use a IsState as a bit field (I know a bit can have 3 values but...). That way you can find states by "where IsState = 1" instead of "where ParentID is null". It my be splitting hairs but it is pet peeve of mine to use NULL as an indication of anything. The premise that NULL indicates an unknown value is lost with this type of structure.
/soapbox off
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 5, 2011 at 2:28 pm
Sean Lange (8/5/2011)
cfradenburg (8/5/2011)
Two ways to go about this. One is to set "on delete cascade" on the foreign key. ...Small data set or not, you're likely to be better off long term not mixing entities like this. If there's any chance of this table growing at all the now is the time to plan for that.
/soapbox on
The other big problem that I see is that you are using NULL as a value. Your records with NULL for a ParentID indicate that it is a state. Just in general you would be better off to use a IsState as a bit field (I know a bit can have 3 values but...). That way you can find states by "where IsState = 1" instead of "where ParentID is null". It my be splitting hairs but it is pet peeve of mine to use NULL as an indication of anything. The premise that NULL indicates an unknown value is lost with this type of structure.
/soapbox off
Not everyone agrees that this is the only definition of NULL, it's also a placeholder to indicate that a fact in that attribute is inappropriate for what is modeled in the row.
IE: In a human proportions table, unless you're planning on splitting personal attributes into a male/female split tables, the 'chest cup size' portion of the Male's entry should be NULL, as it is beyond N/A, it is a non-existant attribute. (Edit: I'm using that as the indicator even though if you want to split hairs, yes, men can have a cup size. If you like, 'milk per minute' production. Take your choice of impossible attribute.)
Now, do I think city and state should be in the same table? No, not really. It's a direct hierarchy that won't multi-node.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 5, 2011 at 2:43 pm
Craig Farrell (8/5/2011)
Sean Lange (8/5/2011)
cfradenburg (8/5/2011)
Two ways to go about this. One is to set "on delete cascade" on the foreign key. ...Small data set or not, you're likely to be better off long term not mixing entities like this. If there's any chance of this table growing at all the now is the time to plan for that.
/soapbox on
The other big problem that I see is that you are using NULL as a value. Your records with NULL for a ParentID indicate that it is a state. Just in general you would be better off to use a IsState as a bit field (I know a bit can have 3 values but...). That way you can find states by "where IsState = 1" instead of "where ParentID is null". It my be splitting hairs but it is pet peeve of mine to use NULL as an indication of anything. The premise that NULL indicates an unknown value is lost with this type of structure.
/soapbox off
Not everyone agrees that this is the only definition of NULL, it's also a placeholder to indicate that a fact in that attribute is inappropriate for what is modeled in the row.
IE: In a human proportions table, unless you're planning on splitting personal attributes into a male/female split tables, the 'chest cup size' portion of the Male's entry should be NULL, as it is beyond N/A, it is a non-existant attribute. (Edit: I'm using that as the indicator even though if you want to split hairs, yes, men can have a cup size. If you like, 'milk per minute' production. Take your choice of impossible attribute.)
Now, do I think city and state should be in the same table? No, not really. It's a direct hierarchy that won't multi-node.
LOL Craig. I agree totally with your example of using NULL in that situation. I am not one to adhere to anything 100% including my own definition and usage of NULL but in your example above this would be like using the Cup size field to indicate the sex. Cup size of anything other than NULL means it is a male (we could of course take this to silly places like 8 year old girls and such but alas that is another thread to hijack at another time). I would however on a human proportions table expect there to be a field to explicitly indicate the sex. I would find that using NULL in the cup size to tell me it is a male totally unacceptable. This is the point I was trying to make (and as typical failed in finding the words to explain).
We wouldn't expect the sql to find all males to be "where CupSize is NULL".
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 5, 2011 at 3:01 pm
Sean Lange (8/5/2011)
Craig Farrell (8/5/2011)
Sean Lange (8/5/2011)
cfradenburg (8/5/2011)
Two ways to go about this. One is to set "on delete cascade" on the foreign key. ...Small data set or not, you're likely to be better off long term not mixing entities like this. If there's any chance of this table growing at all the now is the time to plan for that.
/soapbox on
The other big problem that I see is that you are using NULL as a value. Your records with NULL for a ParentID indicate that it is a state. Just in general you would be better off to use a IsState as a bit field (I know a bit can have 3 values but...). That way you can find states by "where IsState = 1" instead of "where ParentID is null". It my be splitting hairs but it is pet peeve of mine to use NULL as an indication of anything. The premise that NULL indicates an unknown value is lost with this type of structure.
/soapbox off
Not everyone agrees that this is the only definition of NULL, it's also a placeholder to indicate that a fact in that attribute is inappropriate for what is modeled in the row.
IE: In a human proportions table, unless you're planning on splitting personal attributes into a male/female split tables, the 'chest cup size' portion of the Male's entry should be NULL, as it is beyond N/A, it is a non-existant attribute. (Edit: I'm using that as the indicator even though if you want to split hairs, yes, men can have a cup size. If you like, 'milk per minute' production. Take your choice of impossible attribute.)
Now, do I think city and state should be in the same table? No, not really. It's a direct hierarchy that won't multi-node.
LOL Craig. I agree totally with your example of using NULL in that situation. I am not one to adhere to anything 100% including my own definition and usage of NULL but in your example above this would be like using the Cup size field to indicate the sex. Cup size of anything other than NULL means it is a male (we could of course take this to silly places like 8 year old girls and such but alas that is another thread to hijack at another time). I would however on a human proportions table expect there to be a field to explicitly indicate the sex. I would find that using NULL in the cup size to tell me it is a male totally unacceptable. This is the point I was trying to make (and as typical failed in finding the words to explain).
We wouldn't expect the sql to find all males to be "where CupSize is NULL".
LOL, sorry, bad explanation on my part. Assumption was somewhere in that row was a 'Gender' field, but this field wouldn't be applicable when Gender = 'M'.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 5, 2011 at 3:09 pm
On the contrary, I think your explanation was pretty much spot on. I was just trying to take your example to demonstrate how the OP was using NULL in the ParentID field. Your example worked perfectly as an explanation of how NOT to do it. 😉
It is late on Friday, I can almost taste the beer that I am about to go get.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 5, 2011 at 3:12 pm
Sean Lange (8/5/2011)
On the contrary, I think your explanation was pretty much spot on. I was just trying to take your example to demonstrate how the OP was using NULL in the ParentID field. Your example worked perfectly as an explanation of how NOT to do it. 😉It is late on Friday, I can almost taste the beer that I am about to go get.
*facepalm* Snicker, ah, NOW I understand! Definately Friday. 😛
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply