Re: Problem altering table and adding a default
From: J.Marsch (jeremy_at_ctcdeveloper.com)
Date: 02/23/04
- Next message: Narayana Vyas Kondreddi: "Re: Add Startup DBCC Traceon (1204)"
- Previous message: Dan: "Add Startup DBCC Traceon (1204)"
- In reply to: Billy Yao [MSFT]: "Re: Problem altering table and adding a default"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 23 Feb 2004 09:22:58 -0600
Thank you Billy. It might be that the times that this has worked for me in
EM, I was operating on an empty table. I appreciate your attention to
detail on this issue.
Jeremy
""Billy Yao [MSFT]"" <v-binyao@online.microsoft.com> wrote in message
news:7QgUkn29DHA.3472@cpmsftngxa07.phx.gbl...
> 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: Narayana Vyas Kondreddi: "Re: Add Startup DBCC Traceon (1204)"
- Previous message: Dan: "Add Startup DBCC Traceon (1204)"
- In reply to: Billy Yao [MSFT]: "Re: Problem altering table and adding a default"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
