How to remove not null constraint in sql server using query

Question

I am trying to remove not null constraint in sql server 2008 without losing data.

Nash

Answer 1

 ALTER TABLE YourTable ALTER COLUMN YourColumn columnType NULL

Raman

I found I had to include the type in YourColumn eg. ALTER TABLE YourTable ALTER COLUMN YourColumn int NULL – Goal

or you can do : alter table table_name modify column_name type(30) NULL. 30 being the size of your column type, example: varchar(30) – Insecurity

In postgres: ALTER TABLE YourTable ALTER COLUMN YourColumn DROP NOT NULL – Bewilderment

Answer 2

Remove constraint not null to null

ALTER TABLE 'test' CHANGE COLUMN 'testColumn' 'testColumn' datatype NULL;

Shalandashale

Why does this work and not the above accepted answer for mysql ? – Jemy

@Jemy the question was about Microsoft SQL Server and not MySql. – Victorious

I'm finding that our copy of MS SQL Server 2019 isn't familiar with the change keyword. – Prognosticate

Answer 3

Remove column constraint: not null to null

ALTER TABLE test ALTER COLUMN column_01 DROP NOT NULL;

Francoisefrancolin

That doesn't look like valid T-SQL. Although the question was marked with SQL, note that the question explicitly refers to SQL Server which only accepts T-SQL. – Brenneman

Answer 4

Reference: https://www.tutorialspoint.com/How-can-we-remove-NOT-NULL-constraint-from-a-column-of-an-existing-MySQL-table

ALTER TABLE tableName MODIFY columnName columnType NULL;

Improvement