May 13, 2009 at 1:53 pm
I keep getting this error:
'Student' table
- Unable to modify table.
Cannot insert the value NULL into column 'ID', table 'ThomasGregory.dbo.Tmp_Student'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Everytime i try to insert a column with the "Allow Nulls" check box UN-checked, it gives me an error. What do i need to do to fix this?
I'm trying to add an ID column that will be unique. The first time i tried something like this i was able to do it effortlessly. NOw I can't remember how i did it.
May 13, 2009 at 2:01 pm
The message indicates that you are trying to insert a record with out an ID. The ID field is not null and you are not supplying a value hence it fails.
If this is a Primary Key field then modify it for Identity values so you do not have to supply the ID, SQL will create one automatically. Otherwise you will need to supply a UNIQUE ID Value for every insert record statement.
Raymond Laubert
Exceptional DBA of 2009 Finalist
MCT, MCDBA, MCITP:SQL 2005 Admin,
MCSE, OCP:10g
May 13, 2009 at 2:03 pm
Hi
If you try to add a NOT NULL column to an existing table you have to:
* Either define the column as IDENTITY column to create a incremental ID for the existing rows
* Or define a DEFAULT value
Greets
Flo
May 13, 2009 at 2:14 pm
Thanks guys, but Identity Specification is DISABLED. It will not allow me to change it to identity.
You are telling me what to do but not how to do it. I'm clueless at this point. When I did it a few days ago it was a 2 second deal seems like.
Thanks!
May 13, 2009 at 2:26 pm
Try to add the column via SQL:
ALTER TABLE TestColumn ADD Id INT NOT NULL IDENTITY(1,1)
Greets
Flo
May 13, 2009 at 2:40 pm
Thanks! It worked. I don't know why but it did. I don't understand why i could do it with a script but not with the gui.
I also do understand what the difference was that prevented me this time but last time it was a breeze.
May 13, 2009 at 2:47 pm
I'm no guru with the table designer. I think you will become faster in your development and get a better knowledge about the insides if you try to handle your DDLs via SQL.
Sure, just my opinion. 😉
Greets
Flo
May 13, 2009 at 2:59 pm
You know I developed a whole database of about 20 tables using DDL, but unless you use it all the time, it seems that its easy to forget. How much of that syntax do you guys know by heart without consulting a reference?
If i have to look at the reference, with exception to a few minor details from time to time, i basically feel that I don't know it yet.
I'd like to be able to do this stuff without looking at the reference but there is so much to learn and so many different sprocs, etc, that I don't know if that is possible for me at this point in time. Most of what I do currently is DML i guess--the data manipulation language, doing select statements, updates, and inserts. I hope to learn stored procedures soon but I don't do them as part of my job right now.
May 13, 2009 at 3:17 pm
Jacob Pressures (5/13/2009)
You know I developed a whole database of about 20 tables using DDL, but unless you use it all the time, it seems that its easy to forget. How much of that syntax do you guys know by heart without consulting a reference?
From my position I can say there is almost no day without a BOL :-).
If i have to look at the reference, with exception to a few minor details from time to time, i basically feel that I don't know it yet.
For sure, looking into BOL usually implies that you don't know something. Does this matter? This is the reason why MS supplied it. I like the BOL because I always learn some new things. Sometimes things I was not looking for but which might be helpful for the current or another case.
I'd like to be able to do this stuff without looking at the reference but there is so much to learn and so many different sprocs, etc, that I don't know if that is possible for me at this point in time.
As I said, it's okay and important to look into. Keep trying and you will become more experienced.
Most of what I do currently is DML i guess--the data manipulation language, doing select statements, updates, and inserts. I hope to learn stored procedures soon but I don't do them as part of my job right now.
Correct for DML ;-). Give procedures a try and maybe you will see it's much easier to write SQL in SSMS (SQL Server Management Studio) instead of string concatenations in C#/VB.NET/PHP/C++/whatever.
Greets
Flo
May 13, 2009 at 3:19 pm
Jacob Pressures (5/13/2009)
How much of that syntax do you guys know by heart without consulting a reference?
Quite a lot in areas that I work with frequently. When I can't remember something, I look it up in BoL (which is always open).
It's like learning to touch-type. Slow at first but with large gains once you get familiar with the basics and the general form of the SQL commands.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 14, 2009 at 7:11 am
Jacob Pressures (5/13/2009)
You know I developed a whole database of about 20 tables using DDL, but unless you use it all the time, it seems that its easy to forget. How much of that syntax do you guys know by heart without consulting a reference?If i have to look at the reference, with exception to a few minor details from time to time, i basically feel that I don't know it yet.
I'd like to be able to do this stuff without looking at the reference but there is so much to learn and so many different sprocs, etc, that I don't know if that is possible for me at this point in time. Most of what I do currently is DML i guess--the data manipulation language, doing select statements, updates, and inserts. I hope to learn stored procedures soon but I don't do them as part of my job right now.
I have BOL open pretty much all day long (and sometimes into the night). I'm sure there are people out there the memorize all the syntax (Gail, Jeff, others), but most of us mere mortals are forced to look stuff up. Nothing wrong with it.
The level of immediate control you'll get out of using DDL scripts instead of the GUI will very quickly blow you away. So you have to look up the syntax, who cares.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply