473,288 Members | 2,350 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,288 software developers and data experts.

change to not allow nulls

Is there a *simple* way to change a collumn from allowing null to not
null?

I just unchecked "allow nulls" in EM and the SQL it generates to do
this one thing is astonishing, create table, drop FKs, copy data, drop
table, rename new table, rebuild FKs...

I'm saving a lot of these changes to run on another database at a later
date but would rather not require a terrabyte device to store the
script :-)

Jul 23 '05 #1
3 5522
I recommend you use TSQL scripts to make structure changes. You'll have
much more control and visibility over what happens and you'll be able
to test your scripts out before you go live with the change.

You can change nullability with an ALTER TABLE... ALTER COLUMN
statement but if the column is part of an index or constraint then
you'll have to drop that before you can make the change. That means
you'll also have to drop foreign keys that reference the column. EM
tries to make this easier by generating the script for you, so you
could save that script and take it as a starting point.

Another option that may be worth trying:
1. create a new column, populate it from the previous nullable one and
make it non-nullable
2. add constraints and indexes
3. drop the old column
4. rename the column you added

Possibly this method may incur less impact and downtime but that would
depend quite a lot on how the column is used and on other factors too
such as the size of the data and whether an existing index on the
column is clustered. Test it out and see.

--
David Portas
SQL Server MVP
--

Jul 23 '05 #2

David Portas wrote:
I recommend you use TSQL scripts to make structure changes. You'll have much more control and visibility over what happens and you'll be able
to test your scripts out before you go live with the change.
I am doing, I use EM to generate the scripts for some things if there's
a shed load to do in a table or if I don't know how to do something
(such as change a null column to a not null :-)
You can change nullability with an ALTER TABLE... ALTER COLUMN
statement but if the column is part of an index or constraint then
you'll have to drop that before you can make the change. That means
you'll also have to drop foreign keys that reference the column. EM
tries to make this easier by generating the script for you, so you
could save that script and take it as a starting point.


Thanks, it's just a description column so alter table should do it.

Jul 23 '05 #3
Trevor Best (go**********@besty.org.uk) writes:
Is there a *simple* way to change a collumn from allowing null to not
null?

I just unchecked "allow nulls" in EM and the SQL it generates to do
this one thing is astonishing, create table, drop FKs, copy data, drop
table, rename new table, rebuild FKs...

I'm saving a lot of these changes to run on another database at a later
date but would rather not require a terrabyte device to store the
script :-)


That is not the main problem with the scripts generated by Enterprise
Manager. The main problem is that if something goes wrong in the
middle of those scripts, you may end of with halfly-modified database,
and you may lose foreign keys forever. This is because the scripts
has a poor transaction scope.

Overall, I strongly discourage using the table designer in Enterprise
Manager to change tables. You can take the script as a starting point,
but there is a lot of problems to sort out. Some standard remedies
to apply:

o Remove all BEGIN and COMMIT TRANSACTION but the first BEGIN and last
COMMIT.
o Put all statements in EXEC('...'). (Except for calls to stored
procedures.
o Remove all GO.
o On all re-addition of foreign keys, insert WITH CHECK before
CHECK. (Yes, WITH CHECK CHCEK.)

For this particular case, adding a new column and then dropping the
old one may be a good idea, as David suggested.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Robert Stearns | last post by:
Either I missed something, or ALTER TABLE does not have this capability. Is there any way of doing it except DROPping all constraints which mention this table, EXPORTing the data, DROPping the...
8
by: Mike | last post by:
The current databas structure that i'm working with allowed NULL's an now I'm converting the app to .NET and it will not allow NULLs in the fields when populated. So my question is, how can i...
0
by: Steve Amey | last post by:
Hi all I am having a problem when I am updating my database. I am using a strong DS and the PK column is an Identity column, but when I am udating my database I am getting the exception 'Column...
0
by: Jim | last post by:
I am trying to add records to a table using a windows form created in vb with a dataset object. I have dragged and dropped the fields I want onto my form. A BindingNavigator is created and am...
0
by: Jim | last post by:
I am trying to add records to a table using a windows form created in vb with a dataset object. I have dragged and dropped the fields I want onto my form. A BindingNavigator is created and am...
1
by: Martin Widmer | last post by:
Hi guys! I get this message when trying to insert a new record into a datatabel which is part of a dataset connected within VS 2005 (VB) from a datagridview control via a DataTableBindingSource...
3
by: Meelis Lilbok | last post by:
Hi How to get information from DataSet, allows the field NULL values or not? If i fill DataSet and read "MyDataSet.Tables("table").Columns("name").AllowDBNull" then this returns always True ...
0
by: Scott_from_Carematic | last post by:
I'm trying to use a strongly typed dataset in my asp.net (2.0) project with visual studio 2005. I have generated the dataset and tableadapters in the designer. I'm using SQL server EE 2005 as the...
5
by: John | last post by:
Hi I have a vb.net winform data app with sql server 2005 backend. The ID field in the tblClients table is an 'int' identity column that does not allow nulls. The problem is that when I try to...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.