|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
can't insert into ...because of nulls problme!Hello,
I am trying to insert some records into a table from an SP but get this message: Server: Msg 515, Level 16, State 2, Procedure stp_InsetIntoSubDetail, Line 6 Cannot insert the value NULL into column 'CurEntryDate', table 'Subscriber.dbo.SubDetail'; column does not allow nulls. INSERT fails. The statement has been terminated. I am inserting into only a few fields. How can I negotiate this? Thanks, Rich You cannot, unless you pass a value for every non-nullable column in the
table. The column CurEntryDate sounds like a column that captures the insertion time of the row. So you might be able to pass CURRENT_TIMESTAMP as a value in the INSERT statement. If being passed through a stored procedure, declare a local variable that has the current date/time value and pass it as an input parameter. You may also consult your database designer and see if he can set a default on this column to eliminate the need to pass this value in every INSERT statement. -- Anith Thanks. What I did was to temporarily allow nulls in these fields. and then
restored the table back. Is this practice OK? Show quote "Anith Sen" wrote: > You cannot, unless you pass a value for every non-nullable column in the > table. > > The column CurEntryDate sounds like a column that captures the insertion > time of the row. So you might be able to pass CURRENT_TIMESTAMP as a value > in the INSERT statement. If being passed through a stored procedure, declare > a local variable that has the current date/time value and pass it as an > input parameter. > > You may also consult your database designer and see if he can set a default > on this column to eliminate the need to pass this value in every INSERT > statement. > > -- > Anith > > > If constraints have no purpose don't use them at all. I'd suggest going
through the documentation, but then again you may have already done that without any success. My guess is that if a constraint was ever defined it was defined for a reason. If you've now gone and inserted data through a backdoor, this just might cause problems for you later on - i.e. with queries written against non-nullable columns which now contain null values. ML --- http://milambda.blogspot.com/ On Thu, 19 Jan 2006 14:29:02 -0800, Rich wrote:
>Thanks. What I did was to temporarily allow nulls in these fields. and then Hi Rich,>restored the table back. Is this practice OK? No, it definitely isn't. There is probably a reason why this column is defined to not allow NULLs. If you overide that restriction, you're probably entering invalid data and endagnering the integrity of your database. Would you also temporarly disable a CHECK constraint that allows only 'Male' and 'Female' in the gender column to enable the value 'Water' to be entered there? -- Hugo Kornelis, SQL Server MVP > Thanks. What I did was to temporarily allow nulls in these fields. and The practice doesn't make sense. Don't you think the NOT NULL constraints > then > restored the table back. Is this practice OK? are there for a reason? Why bother having them at all if you're just going to remove them every time you insert data? Isn't this kind of like not wearing your seatbelt? Thank you all for your input on this matter. I undid what I was going to
try. Anyway, No, there is no documentation. I am on my own! Truth be told, I am not at the guru level. I am going to have to pay my dues with a lot of heart ache. The task was one of these add-hoc things, and I just started on assuming the duties of this project which was already in motion for a while. So I rushed! then I slowed down and created fake data ('' for varchars and an agreed upon fake date for date fields) to accommodate the constraints. That did the trick. The first reply said I can't insert records if nulls are not allowed unles I have some values. So I created the values. Anyway, I really appreciate the replies. Even though I was suggesting ideas of an amateur nature these replies will help to keep me on the proper course. Rich Show quote "Rich" wrote: > Hello, > > I am trying to insert some records into a table from an SP but get this > message: > > Server: Msg 515, Level 16, State 2, Procedure stp_InsetIntoSubDetail, Line 6 > Cannot insert the value NULL into column 'CurEntryDate', table > 'Subscriber.dbo.SubDetail'; column does not allow nulls. INSERT fails. > The statement has been terminated. > > I am inserting into only a few fields. How can I negotiate this? > > Thanks, > Rich In case of lacking documentation maybe one of the users can help. And there's
got to be at least some shread of documentation. A leaflet, a greasy paper napkin, something. ML --- http://milambda.blogspot.com/ Your idea was reasonable for someone new to database programming. You had
some reservations and asked the right folks a question. We all have to ask questions like this when we start out. An amateur would have made the change and not bothered asking anyone if it made sense. One additional note though... If you had removed the constraint in order to insert the null values, you would not have been able to put the constraint back without first changing the null values to actual values. If your data does not follow the rules of the constraint you are trying to make, then the database will not let you create the constraint. Show quote "Rich" <R***@discussions.microsoft.com> wrote in message news:43A5D9EE-1103-409F-A440-7EFCDC318DEF@microsoft.com... > Thank you all for your input on this matter. I undid what I was going to > try. Anyway, No, there is no documentation. I am on my own! Truth be told, > I am not at the guru level. I am going to have to pay my dues with a lot of > heart ache. The task was one of these add-hoc things, and I just started on > assuming the duties of this project which was already in motion for a while. > So I rushed! then I slowed down and created fake data ('' for varchars and > an agreed upon fake date for date fields) to accommodate the constraints. > That did the trick. The first reply said I can't insert records if nulls are > not allowed unles I have some values. So I created the values. Anyway, I > really appreciate the replies. Even though I was suggesting ideas of an > amateur nature these replies will help to keep me on the proper course. > > Rich > > "Rich" wrote: > > > Hello, > > > > I am trying to insert some records into a table from an SP but get this > > message: > > > > Server: Msg 515, Level 16, State 2, Procedure stp_InsetIntoSubDetail, Line 6 > > Cannot insert the value NULL into column 'CurEntryDate', table > > 'Subscriber.dbo.SubDetail'; column does not allow nulls. INSERT fails. > > The statement has been terminated. > > > > I am inserting into only a few fields. How can I negotiate this? > > > > Thanks, > > Rich > Your idea was reasonable for someone new to database programming. You had I strongly agree. No one is born smart.> some reservations and asked the right folks a question. We all have to ask > questions like this when we start out. > > An amateur would have made the change and not bothered asking anyone if it > made sense. ML --- http://milambda.blogspot.com/  |
|||||||||||||||||||||||
Other interesting topics