Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am mass updating a SQL Server database. I am changing all our numeric(38,0) columns to int (yes, SQL Server scripts were created from Oracle scripts). Using SMO and C# (I am a sw engineer), I managed to generate really nice scripts like SQL Server Management studio would. It all works very nicely except for one particular issue:

for a a handful of tables, when I call ALTER TABLE [myTable] ALTER COLUMN [columnA] INT it decides to also change the column from NOT NULL to NULL. That of course is a huge issue since I need to regenerate primary keys for most of those tables on those particular columns.

Obviously, I have plenty of options using SMO to find out which columns are primary keys and force them to be NOT NULL after or while I am updating the data type, but I am really curious as to what can be causing this.

share|improve this question
    
You can find this out if columns are members of the primary key via T/SQL as well. SMO isn't required. –  mrdenny Feb 8 '11 at 23:44

1 Answer 1

up vote 9 down vote accepted

It depends on your setting of SET ANSI_NULL_DFLT_OFF or SET ANSI_NULL_DFLT_ON (read these for other effects too from SET ANSI_DEFAULTS).

Personally, I'd have specified this constraint explicitly if I wanted null or non null columns rather than relying on environment settings.

ALTER TABLE [myTable] ALTER COLUMN [columnA] INT NOT NULL

You can issue this command again and if a column is already at the desired nullability the command is ignored.

However, you also said that you only need to change it where it's part of a primary key. That should have no bearing on whether a column can be nullable or not from the modelling/implementation phase

share|improve this answer
1  

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.