| Author |
Topic |
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
 Posted - 2002-03-14 : 02:40:06
|
I have a table as follows I am in the process of uprading the software, and normalizing the table. I am going to get rid of the PREVMESSAGE column and replace it with the PrevMessageID column. The problem is when I insert into the table a message that does not have a previous message I violate the contraint. Should I just remove the constraint? I'm thinking that might be easiest. I believe the constraint is being violated because I am inserting an empty string, if it were NULL would it not be violated? Should I do this? I think it will be a hassle, is it that big of a deal if I just take off the constraint ? :DI am a bit confused on where to go with this. Any direction greatly appreciated..thanks,mike |
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-03-14 : 02:57:44
|
| you dont have to remove the constraint( i believe you have a foreign constraints which points to messageid of the same table). its a good idea to drop the prevMessage.All this works fine.create table aaaa( a int primary key, b int references aaaa(a))insert into aaaa values(1,null)insert into aaaa values(2,1)insert into aaaa values(3,null)This gives a error. coz there is no value 6 in primary key of aaaa table.insert into aaaa values(2,6)-------------------------------------------------------------- |
 |
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2002-03-14 : 03:08:58
|
thanks again nazim..... I can basically rely on you now  My question is now this, maybe its not a SQL question anymore tho.If I pass SQL an empty string, how can I get it to insert a NULL so it doesnt error out. Or should I figure out a way to do this in the software?Thanks again,mikeEdited by - mike123 on 03/14/2002 03:09:24 |
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-03-14 : 03:24:02
|
| remember therez a lot of difference between a space , zero and null.so if you dont have any data its good you pass a null. instead of ' '. or something like this.you can change it your sp or your front end too.HTH-------------------------------------------------------------- |
|
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2002-03-14 : 16:17:22
|
| it looks like I am not going to be able to pass a NULL value to the SPROC, just an empty string. How can I make the SPROC insert a NULL value, (not an empty string) when an empty string is passed.this seems like it would be the best solution.PS: I have never really been able to get default values to work I thought the way they would. I have the @prevMsgID parameter in the SPROC default to NULL. If I dont pass a value it should default to NULL shouldn't it? I have been trying to do this buy just goinglike this with the double comma, but it is yet to work :)sproc_name "value1", "value2",,"value3" |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-14 : 16:25:18
|
| In order for default parameter values to work properly, you need to use named parameters:sproc_name @param1='value1', @param2='value2', @param4='value4'I changed this to reflect that there is no param3 passed; when you use named parameters you simply don't pass the parameter if you want its default value. If you use named parameters, you must name all of them.If you wanted to pass a null, you could also:sproc_name 'value1', 'value2', Null, 'value4'Notice that Null does not use delimiters of any kind. If you want to pass an empty string ('') and have it converted to a Null, use the NullIf() function:SELECT NullIf(@param1, '')If @param1='', then NullIf() will make it a null. NullIf() is detailed in Books Online, with some more examples of how it works. |
|
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2002-03-14 : 17:45:37
|
Thanks, I'll make things even more confusing just becauset thats how I seem to operate Ok back to database design. I will be the devils advocate here.Situation:I compose a piece of mail, that piece of mail is given an messageID of 99(IDENTITY). I recieve my own piece of mail (or any other user for that matter). I reply to that piece of mail, the new piece of mail is inserted into the table with a MESSAGEID of 100 and a PREVMSGID of 99. What happens when I want to delete message 99? Message 100 no longer can join onto message 99 to get the previous message? UH OH!  .Any thoughts, suggestions? .. Is my design flawed AGAIN?thanks...(cry,cry)mike |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-03-15 : 15:02:34
|
| What is PrevMsgID? Is it the ID of the message that you did your "reply" to? So msg 100 is a reply to msg 99? If so, why would you want to delete the beginning of the thread? For archiving purposes?You could update the PrevMsgID before you delete the other record (two separate statements). But what you update it to, depends on what you're doing with the previous message...------------------------GENERAL-ly speaking... |
|
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2002-03-15 : 20:24:00
|
| Ajarnmark,Yes you are right, PrevMsgID is the ID of the message that is being replied to. However this is not a Forum like we are using right now. I should have mentioned that this is a mailsystem, with an Inbox for each user. So if I were to send you a piece of mail with MessageID of 100 and you replied to it and sent it to me, the piece of mail you sent would have MessageID 101 and PrevMsgID 100. When I delete a message in my Inbox I do not want it affecting your Inbox.Thanks for your helpMike |
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-03-17 : 00:50:52
|
| Mike , with your present design it will not be possible to achieve what you are looking for.i would suggest you to have a seperate table to hold the relationship messageid's.likeTblMessage----------MessageIdMessageFromMessageToSubjectMessageDateCheckedtblMessageRelation-----------------MessageIdParentMessageIdDONT define any Primarykey and Foreing key relationship between tblMessage (MessageId) and tblMessageRelation(ParentMessageId) otherwise you will endup with the same problem you are encountering now.HTH-------------------------------------------------------------- |
|
|
|
|
inserting with constraint