A SQL Headache

So, Ive had a working database for over 8 months where everything is going smooth, the person who requested it had me set pretty much every field to not allow NULLS. Which is fine, I like it when theres alot of information myself.

New person takes over, doesnt like it at all.

Without losing data, is there a way to now make a field allow nulls.

I am using Microsoft SQL Server Management Studio (SQL Server 2008).

Using Sql Server Management Studio, If you right click the table, and go to Design, can't you simply check the "allow nulls" box on the column(s) you wish?

Sgraffite said:

Using Sql Server Management Studio, If you right click the table, and go to Design, can't you simply check the "allow nulls" box on the column(s) you wish?

Click to expand...

I think this ought to work, or the equivalent ALTER COLUMN DDL command. Just try it first on a simple test database or a safe copy of the working one, FFS.

Lots of info about DDL syntax here: http://msdn.microsoft.com/en-us/library/ms190273.aspx

Look at the null part of the arguments section about 1/4 of the way down.

Sgraffite said:

Using Sql Server Management Studio, If you right click the table, and go to Design, can't you simply check the "allow nulls" box on the column(s) you wish?

Click to expand...

Tried this, it tells me;

Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that cant be re-recreated or enabled the option Prevent saving changes that require the table to be re-created.

FrEaKy said:

Tried this, it tells me;

Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that cant be re-recreated or enabled the option Prevent saving changes that require the table to be re-created.

Click to expand...

You can get around this by going to Tools -> Options, then click on Designers on the left side. Uncheck "Prevent saving changes that require table re-creation".

That has worked for me in the past, however I wasn't working on a production server at the time. I believe it does some behind the scenes table recreation, but I'm not sure what the real world effect is.

Make a full DB backup before you start this. Sgraffite's suggestion should go through fine, but keep yourself covered if something blows up.

PTNL said:

Make a full DB backup before you start this. Sgraffite's suggestion should go through fine, but keep yourself covered if something blows up.

Click to expand...

This worked wonderfully.

I did make a backup of the database just incase. I appreciate the input from all, thank you!

There's never a reason to drop and recreate the table just to allow nulls. You should learn the DDL commands to directly allow nulls to the columns which need them. If the column is involved in an index, you might need to recreate the index, but never the table itself.

It seems like you skipped the step of modeling; why are nulls being allowed? Are you doing this for all, or just a few of them? What's the business need? Nulls don't mean "A lot of data"; they have a significant and contextual meaning in the table and the system. Making this change lightly is a mistake.

mikeblas said:

There's never a reason to drop and recreate the table just to allow nulls. You should learn the DDL commands to directly allow nulls to the columns which need them. If the column is involved in an index, you might need to recreate the index, but never the table itself.

It seems like you skipped the step of modeling; why are nulls being allowed? Are you doing this for all, or just a few of them? What's the business need? Nulls don't mean "A lot of data"; they have a significant and contextual meaning in the table and the system. Making this change lightly is a mistake.

Click to expand...

And I understand that, when I started the project 8 months ago, I was new to SQL, had to learn everything from the start, which is fine, yes I may have missed a point or two, but the need was great and the time given for the project was very limited.

When this was originally made, the original owner of this database wanted EVERY BIT of data to entered into the input fields I did via ColdFusion. She wanted detailed information out the wazoo and required that of her underlings. However her underlings decided to bypass things and would enter blank statements aka: LicenseNumber - 00000.... This was a problem from the getgo.

She than left the division recently (aka a month ago) and has been replaced. The new owner of the DB complained to much was information was required, so I had to than find a way to drop the required field to allow nulls for the users to enter data as they saw fit.

I know this change shouldnt have been taken lightly, and I made sure to take full precautions incase of any backlash. I have backed up the database to an external source and had a server ghost image done as well prior to the changes to ensure data would not be lost.

At this time, the change has worked wonderfully and have lost NO data, and the users are able to enter the information they desire with no issues at all, there are only 4 required fields they must enter now out of 12 which makes their lives easier. Those fields being PermitNumber, Campus, FiscalYear and LicensePlateNumber. PermitNumber and Campus are a composite primary key because the permit numbers can be the same, but not at the same campus, for instance, we have 4 campuses, all of which have a 00001 PermitNumber through 99999. So there is a 00001 Campus A as there is a 00001 Campus B. These fields remain non-null as they will always be required and the user is forced to enter this data.

I am still learning alot about SQL but have found Ive made leaps and bounds in my learning regarding it and love alot of what you can do with SQL database management.


Thank you for the concern, but I would like to think, or hope, that I may have a grasp of this in some form. I will continue to learn something new regarding SQL each day as I do more and more database creations involving it.

I find that most developers don't understand the point of NULL in a DB.

In my perspective you should have a specific reason for *disabling* NULL. Their default state should be *enabled*.
If NULL is meaningless to your domain, then your DAL should be in charge of the conversion, not the database. In most cases you will want the ability to discern between "zero" and "not-set"

Zangmonkey said:

I find that most developers don't understand the point of NULL in a DB.

In my perspective you should have a specific reason for *disabling* NULL. Their default state should be *enabled*.
If NULL is meaningless to your domain, then your DAL should be in charge of the conversion, not the database. In most cases you will want the ability to discern between "zero" and "not-set"

Click to expand...

You will have to forgive me than for not understanding.

Thank you for the information.