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.
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)
)
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
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.
alter a table column to nullable
This came from phpmyadmin as preview SQL
and it seems to work:
ALTER TABLE `other_details` CHANGE `used_asset` `used_asset` VARCHAR(100) NULL DEFAULT NULL;
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
How to change a PG column to NULLABLE TRUE?
From the fine manual:
ALTER TABLE mytable ALTER COLUMN mycolumn DROP NOT NULL;
There's no need to specify the type when you're just changing the nullability.
Related Topics
How to Manually Execute SQL Commands in Ruby on Rails Using Nuodb
How to Select and Update Rows at the Same Time
Selecting Rows Where Remainder (Modulo) Is 1 After Division by 2
Move Data from One Table to Another, Postgresql Edition
Concatenate/Merge Array Values During Grouping/Aggregation
Extra Fields with SQL Min() & Group By
Iterate Through Rows in SQL Server 2008
Postgres Date Overlapping Constraint
Cakephp See the Compiled SQL Query Before Execution
Select Multiple Rows with the Same Value(S)
How to Schedule a SQL Job in Microsoft Azure SQL Database
SQL Server 2008: Ordering by Datetime Is Too Slow