Re: Management Console error when Inserting new column
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Sun, 20 Sep 2009 20:15:29 +0000 (UTC)
Charles (Charles@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
I am using the Microsoft SQL Management Studio to insert a new column
into an existing table. This is on a SQL Server 2005 Standard Edition.
Using the Management console I select "Design" on my table.
I then select a column and select "Insert Column" from the menu. After
entering a Column name and Data type, I clear the Allow Nulls checkbox.
When I try to save the changes, I get:
"Cannot insert the value NULL into column..."
I cleared the Allow Nulls! Why am I getting this error?
And if you did not specify a default value, how could it work out?
If you want to add a new column that does not permit NULL, and nor has
a default value, you must specify the value in some other way. About the
only way is to rename the old table, create the new table and then copy
rows over, and in the script specifying the values for the new column. When
you are done, you need to create indexes, triggers and foreign keys. And
you need to migrate referenceing foreign keys to point the new table
before you table drop the old version.
This is exactly what the table designer does, but it does not permit
you to specify a value for the new column. (Which could be tricky, as
it is often the case when you do this that the values are derived in
some non-trivial ways, like getting them from a different table.)
You can however, take the script that the table designer produces and
modify it to meet your needs. Somewhere there is an option to generate
scripts. Beside adapting them to your needs, there are a couple of other
modifications you need to make. The most important is to run a find/replace
to change all occurrance of WITH NOCHECK to WITH CHECK, so that con-
straints are rechecked when you restore them.
You also need to be aware of that the transaction handling in the script
is poor. There are typically three transactions, when there should have
been one. And if one batch fails and rollsback the transaction is rolled
back, the rest of the batch is carried out anyway. The simplest workaround
for this is to backup the database before you run the script, and roll
back in case of an error.
Overal I adivce against using the table designer. The Modify Table function
has several serious bugs, of which I have already touched some - but
there are more.
An alternate way of adding your column could be:
ALTER TABLE tbl ADD col datatype NULL
Then you populate the column in some way. Then you can change the column
to be non-NULLable:
ALTER TABLE tbl ALTER COLUMN datatype NOT NULL
This methods presumes that you accept to add the columns as the last
column in the table.
Note that you cannot use the Table Designer for the last part. The
Table Designer only uses ALTER TABLE in situations where SQL Server 6.5
would accept it, and this does not include ALTER COLUMN.
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.
- References:
- Management Console error when Inserting new column
- From: Charles
- Management Console error when Inserting new column
- Prev by Date: Management Console error when Inserting new column
- Next by Date: Re: Unattended SQL 2005 install
- Previous by thread: Management Console error when Inserting new column
- Index(es):
Relevant Pages
|
