Modify Table: How to Change 'Allow Nulls' Attribute from Not Null to Allow Null

Can I change a column from NOT NULL to NULL without dropping it?

ALTER TABLE myTable ALTER COLUMN myColumn {DataType} NULL

where {DataType} is the current data type of that column (For example int or varchar(10))

How do I modify a MySQL column to allow NULL?

You want the following:

ALTER TABLE mytable MODIFY mycolumn VARCHAR(255);

Columns are nullable by default. As long as the column is not declared UNIQUE or NOT NULL, there shouldn't be any problems.

Altering a column: null to not null

First, make all current NULL values disappear:

UPDATE [Table] SET [Column]=0 WHERE [Column] IS NULL

Then, update the table definition to disallow "NULLs":

ALTER TABLE [Table] ALTER COLUMN [Column] INTEGER NOT NULL

Change a column to not allow nulls

Clearly, the table has NULL values in it. Which you can check with:

select *
from mydatabase
where WeekInt is NULL;

Then, you can do one of two things. Either change the values:

update mydatabase
    set WeekInt = -1
    where WeekInt is null;

Or delete the offending rows:

delete from mydatabase
    where WeekInt is null;

Then, when all the values are okay, you can do the alter table statement.

How to modify null to not null in sql

It sounds like the Modified_By column does not allow nulls. Try altering the column to allow nulls:

ALTER TABLE BOM_Rules
ALTER COLUMN Modified_By NVARCHAR(50) NULL

Altering a column to be nullable

Assuming SQL Server (based on your previous questions):

ALTER TABLE Merchant_Pending_Functions ALTER COLUMN NumberOfLocations INT NULL

Replace INT with your actual datatype.

Alter Column to Not Null where System Versioned column was nullable

I also looked at this and it seems you have to update the NULL values in the system version column to some value.

ALTER TABLE dbo.MyTable
    SET (SYSTEM_VERSIONING = OFF)
GO
UPDATE dbo.MyTable_History
    SET MyInt = 0 WHERE MyInt IS NULL --Update to default value
UPDATE dbo.MyTable
    SET MyInt = 0 WHERE MyInt IS NULL --Update to default value
ALTER TABLE dbo.MyTable
    ALTER COLUMN MyInt INT NOT NULL
ALTER TABLE dbo.MyTable_History
    ALTER COLUMN MyInt INT NOT NULL
GO
ALTER TABLE dbo.MyTable 
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.MyTable_History));
GO

Change a Nullable column to NOT NULL with Default Value

I think you will need to do this as three separate statements. I've been looking around and everything i've seen seems to suggest you can do it if you are adding a column, but not if you are altering one.

ALTER TABLE dbo.MyTable
ADD CONSTRAINT my_Con DEFAULT GETDATE() for created

UPDATE MyTable SET Created = GetDate() where Created IS NULL

ALTER TABLE dbo.MyTable 
ALTER COLUMN Created DATETIME NOT NULL 

ALTER TABLE, set null in not null column, PostgreSQL 9.1

ALTER TABLE person ALTER COLUMN phone DROP NOT NULL;

More details in the manual: http://www.postgresql.org/docs/9.1/static/sql-altertable.html

---

Related Topics