Re: Problem altering table and adding a default
From: Billy Yao [MSFT] (v-binyao_at_online.microsoft.com)
Date: 02/20/04
- Next message: Greg Linwood: "Re: SQL_Server_does_not_exist_or_access_denied"
- Previous message: Hari: "Re: Date setting - newbie"
- In reply to: J.Marsch: "Re: Problem altering table and adding a default"
- Next in thread: J.Marsch: "Re: Problem altering table and adding a default"
- Reply: J.Marsch: "Re: Problem altering table and adding a default"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 20 Feb 2004 04:27:53 GMT
Hi Jeremy,
Thank you for your feedback. It's my pleasure to confirm this issue for you. After performing
a further testing, I found out that the problem was not related to Enterprise Manager (EM), but
to the following statement which attempted to add a new NOT NULL column.
--------------------------
ALTER TABLE dbo.Test_tbl ADD Notnull_col1 char(10) NOT NULL
--------------------------
In SQL Server, adding a column to a table through ADD COLUMN after the table has been
created implies that if the table is not empty, NULLs values will be inserted into the table for
the new column. SQL Server does not allow that - you can only add nullable columns. Below
is the error message you get from SQL Server if you try to add a new NOT NULL column:
-------------------------
CREATE TABLE FOO (Col1 int);
ALTER TABLE FOO ADD Col2 INTEGER NOT NULL;
-------------------------
After you add a new column in Enterprise Manager, uncheck Allow Nulls, bind the uder-
defind default in the Default Value combo box and click Save (or close the table design
interface), the ALTER TABLE statement above will pass to the SQL Server and SQL Server
doesn't allow you do that.
Additionally, the transaction rolls back and the binding operation sp_bindefault will not be
executed at all. Therefore, it's nothing to do with binding the user-defined default, but is
related to the fact that SQL Server does not allow this.
Here are my detailed testing steps and findings, I sincerely hope that you are clear about
the root cause and know how to workaround it:
================================================
0. Create a table Test_tbl add one column Test_col, add some data into the Test_col
column. Please make sure that the table was created and had data within it.
Test three scenarios and trace with profiler respectively:
================================================
Test 1. Add a column binding to a existed user-defined default in EM.
1) Create a user-defined default Test_df valued '' (empty string)
2) Add a column Notnull_col1 to the Test_tbl, data type as Char, Uncheck the Allow Nulls
3) Select Test_df in the Default Value of the column
4) Start Profiler and perform a new trace.
5) Save the changes receive the following message
In EM:
--------------------------------------------------------------------------
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER TABLE only allows
columns to be added that can contain nulls or have a DEFAULT definition specified.
Column 'Notnull_col1' cannot be added to table 'Test_tbl' because it does not allow nulls
and does not specify a DEFAULT definition.
------------------------------------------------------------------------------------------
In Profiler (main SQL statement):
-----------------------------------
BEGIN TRANSACTION
go
ALTER TABLE dbo.Test_tbl ADD
Notnull_col1 char(10) NOT NULL --- ****** The same statement as I mentioned at
the beginning of this message******---
go
ROLLBACK
go
-----------------------------------
=======================================================
Test 2. Add a new column Notnull_col2 specify the default value '' directly in EM.
Re-start Profiler and Click Save in EM, can add the Not Null column Notnull_col2 and
receive the trace information in Profiler:
-------------------------------------------------------
BEGIN TRANSACTION
go
ALTER TABLE dbo.Test_tbl ADD
Notnull_col2 char(10) NOT NULL CONSTRAINT DF_Test_tbl_Notnull_col2
DEFAULT ''
go
COMMIT
go
-------------------------------------------------------
Test 3. On the column Notnull_col2, change the Default Value by deleting the '' first and then
selecting the user-defined default dbo.Test_df.
Re-start Profiler and click Save in EM. This time, the column can be bound to a user-defined
default in EM. The message in Profiler says:
-------------------------------------------------------------
BEGIN TRANSACTION
go
ALTER TABLE dbo.Test_tbl
DROP CONSTRAINT DF_Test_tbl_Notnull_col2
go
sp_MShelp_replication_status @agent_type = 0, @exclude_anonymous = 0
go
EXECUTE sp_bindefault N'dbo.Test_df', N'Test_tbl.Notnull_col2'
go
COMMIT
go
---------------------------------------------------------------
===============================================
In summary:
1. The issue will not happen in a new blank table (have no data)
2. From Test 1 and Test 2, we can confirm this is now allowed in SQL Server.
3. From Test1 and Test 3, we can come to a conclusion:
The NOT NULL clause in ALTER TABLE ADD COLUMN can only be accepted if the
column has already existed. In this scenario, the existed column would be populated with
the new values and the NOT NULL restriction wouldn't be a problem, and will prevent
NULLs to be inserted or updated later.
To workaround this issue:
1. Specify the Default Value as '' directly.
2. First use the Default Value as '', save it to add the column. After that, change the Default
Value to your user-defined default.
3. Design the new blank table (Schema) and import only data into that table.
Best regards,
Billy Yao
Microsoft Online Support
----------------------------------------------------
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.
- Next message: Greg Linwood: "Re: SQL_Server_does_not_exist_or_access_denied"
- Previous message: Hari: "Re: Date setting - newbie"
- In reply to: J.Marsch: "Re: Problem altering table and adding a default"
- Next in thread: J.Marsch: "Re: Problem altering table and adding a default"
- Reply: J.Marsch: "Re: Problem altering table and adding a default"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
