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.

score 259 · Accepted Answer · edited Nov 29 '11 at 17:49

259

 ALTER TABLE YourTable ALTER COLUMN YourColumn columnType NULL

edited Nov 29 '11 at 17:49

Omu

answered Jul 30 '10 at 09:22

5

I found I had to include the type in YourColumn eg. ALTER TABLE YourTable ALTER COLUMN YourColumn int NULL – Adam Butler Jun 28 '11 at 01:16
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) – nr5 Sep 19 '12 at 18:11
69

In postgres: `ALTER TABLE YourTable ALTER COLUMN YourColumn DROP NOT NULL` – Shane Mar 20 '13 at 16:41

score 3 · Answer 2 · edited Dec 13 '17 at 09:18

3

Remove constraint not null to null

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

edited Dec 13 '17 at 09:18

Pang

answered Aug 19 '16 at 18:59

Nan Yu

Why does this work and not the above accepted answer for mysql ? – HopeKing Feb 13 '18 at 08:47
3

@HopeKing the question was about Microsoft SQL Server and not MySql. – Orchidoris Feb 18 '19 at 14:00
I'm finding that our copy of MS SQL Server 2019 isn't familiar with the `change` keyword. – 15ee8f99-57ff-4f92-890c-b56153 Jan 13 '23 at 19:09

score 2 · Answer 3 · answered Apr 18 '19 at 12:17

2

Remove column constraint: not null to null

ALTER TABLE test ALTER COLUMN column_01 DROP NOT NULL;

answered Apr 18 '19 at 12:17

PasQualE

3

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. – TT. Apr 18 '19 at 14:06

score -2 · Answer 4 · answered Nov 17 '19 at 07:01

-2

ALTER TABLE tableName MODIFY columnName columnType NULL;

answered Nov 17 '19 at 07:01

Rujoota Shah

4 Answers4