Unable to Alter Column from Null to Not Null

[rmchung]

I have an existing table in which I want 2 fields to make a composite key. Both fields are set to "allow null". When I use the "alter table" and add a primary key constraint, I keep getting the error - "can't create primary key on nullable column. Any suggestions? Thanks

 

[redlam]

Hi there.
SQL will not allow you to use a nullable field in a primary key. You will need to alter the table so that these fields do not allow null values - if you have existing data you will first need to update any null values with some sort of default value. Once you have done this, you should be able to create your primary key constraint.

 

[rmchung]

Sorry, forgot to also mention that I am trying to alter the columns to not allow null before I assign the columns as a composite key. I just can't seem to get the right coding for it to do that. There are no null values in my existing column so I don't have to worry about updating any values.

 

[redlam]

Ok, see if something like this works:
alter table <tablename>
alter column <columnname> <datatype> not null

 

[rmchung]

Ah yes it worked. I figured out why it didn't work before...I didn't have &quot;GO&quot; at the end of each command. Thanks!!