How to Remove Not Null Constraint in SQL Server Using Query

How to remove not null constraint in sql server using query

 ALTER TABLE YourTable ALTER COLUMN YourColumn columnType NULL

Can I drop not null constraints online in SQL Server?

As stated in commment section this operation should be metadata operation only(if no data type changes occured):

ALTER TABLE DECLARATION
    ALTER COLUMN LOCAL_REFERENCE_NUMBER VARCHAR(22) NULL;

It could be verified by setting Extended Event session and observing sqlserver.compressed_alter_column_is_md_only event (SQL Server 2016+)

SQL Server: Drop all Not Null constraints in all DB tables

You can use the following query to generate the alter statements for each column.
And use the generated statements to alter table.
This won't alter the primary key columns, but it doesn't take into account other foreign key constraints.

DECLARE @Sql NVARCHAR(MAX) = ''

SELECT @Sql +=  CONCAT(N'ALTER TABLE '
, QUOTENAME(schema_name(t.schema_id))
, N'.', QUOTENAME(t.name) 
, N' ALTER COLUMN '
, QUOTENAME(c.Name
), N' '
,Type_name(c.user_type_id) + CASE 
        --types without length, precision, or scale specifiecation 
        WHEN Type_name(c.user_type_id) IN (
                N'int'
                ,N'bigint'
                ,N'smallint'
                ,N'tinyint'
                ,N'money'
                ,N'smallmoney'
                ,N'real'
                ,N'datetime'
                ,N'smalldatetime'
                ,N'bit'
                ,N'image'
                ,N'text'
                ,N'uniqueidentifier'
                ,N'date'
                ,N'ntext'
                ,N'sql_variant'
                ,N'hierarchyid'
                ,N'geography'
                ,N'geometry'
                ,N'timestamp'
                ,N'xml'
                )
            THEN N''
                --types with precision and scale specification 
        WHEN Type_name(c.user_type_id) IN (
                N'decimal'
                ,N'numeric'
                )
            THEN N'(' + Cast(c.PRECISION AS VARCHAR(5)) + N',' + Cast(c.scale AS VARCHAR(5)) + N')'
                --types with scale specification only 
        WHEN Type_name(c.user_type_id) IN (
                N'time'
                ,N'datetime2'
                ,N'datetimeoffset'
                )
            THEN N'(' + Cast(c.scale AS VARCHAR(5)) + N')'
                --float default precision is 53 - add precision when column has a different precision value 
        WHEN Type_name(c.user_type_id) IN (N'float')
            THEN CASE 
                    WHEN c.PRECISION = 53
                        THEN N''
                    ELSE N'(' + Cast(c.PRECISION AS VARCHAR(5)) + N')'
                    END
                --types with length specifiecation 
        ELSE N'(' + CASE ic.CHARACTER_MAXIMUM_LENGTH
                WHEN -1
                    THEN N'MAX'
                ELSE Cast(ic.CHARACTER_MAXIMUM_LENGTH AS NVARCHAR(20))
                END + N')'
        END + CASE 
        WHEN c.is_filestream = 1
            THEN N' FILESTREAM'
        ELSE N''
        END + COALESCE(N' COLLATE ' + c.collation_name, N'') + CASE 
        WHEN c.is_sparse = 1
            THEN N' SPARSE'
        ELSE N''
        END + CASE 
        WHEN c.is_rowguidcol = 1
            THEN N' ROWGUIDCOL'
        ELSE N''
        END
, N' NULL', '
GO
'  )
FROM sys.tables t
INNER JOIN sys.columns c on c.object_Id = t.object_Id
INNER JOIN INFORMATION_SCHEMA.COLUMNS  ic on ic.TABLE_SCHEMA = Schema_name(t.schema_id)
    AND ic.TABLE_NAME = t.name
    AND ic.COLUMN_NAME = c.name
WHERE c.is_nullable = 0
-- And is not part of the primary key
AND NOT EXISTS (
    SELECT 1
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS cons
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS K ON cons.TABLE_NAME = K.TABLE_NAME
        AND cons.CONSTRAINT_CATALOG = K.CONSTRAINT_CATALOG
        AND cons.CONSTRAINT_SCHEMA = K.CONSTRAINT_SCHEMA
        AND cons.CONSTRAINT_NAME = K.CONSTRAINT_NAME
    WHERE cons.CONSTRAINT_TYPE = 'PRIMARY KEY'
    AND k.COLUMN_NAME = c.Name
    AND k.TABLE_NAME = t.Name
    AND k.TABLE_SCHEMA = ic.TABLE_SCHEMA
)

-- PRINT(@SQL)
-- Execute the generated alter table statements
EXEC (@SQL)

How to delete Not null constraint in SQL?

Try

ALTER TABLE EMP MODIFY (ENO null);

How to drop all NOT NULL constraints from a PostgreSQL table in one go

You can group them all in the same alter statement:

alter table tbl alter col1 drop not null,
                alter col2 drop not null,
                …

---

You can also retrieve the list of relevant columns from the catalog, if you feel like writing a do block to generate the needed sql. For instance, something like:

select a.attname
  from pg_catalog.pg_attribute a
 where attrelid = 'tbl'::regclass
   and a.attnum > 0
   and not a.attisdropped
   and a.attnotnull;

(Note that this will include the primary key-related fields too, so you'll want to filter those out.)

If you do this, don't forget to use quote_ident() in the event you ever need to deal with potentially weird characters in column names.

Redshift - How to remove NOT NULL constraint?

You cannot alter the table.

There is an alternative approach. You can create a new column with NULL constraint. Copy the values from your old column to this new column and then drop the old column.

Something like this:

ALTER TABLE table1 ADD COLUMN somecolumn (definition as per your reqm);
UPDATE table1 SET somecolumn = oldcolumn;
ALTER TABLE table1 DROP COLUMN oldcolumn;
ALTER TABLE table1 RENAME COLUMN somecolumn TO oldcolumn;

How can a not null constraint be dropped?

I would try something like this

ALTER TABLE testTable MODIFY COLUMN colA int;

Interview: How to handle SQL NOT NULL constraint on the code end

It's usually a best practice to insert a dummy value such as a -1 that you can easily replace later. A blank string can be more problematic in some cases. To do this you would either use a CASE WHEN statement, or ideally, an ISNULL() function which would look like this ISNULL([ColName], -1) ISNULL is probably the answer they were looking for. That would insert the data if you have it and then if it's null, it would insert a -1.

As Gordon commented, you could also use a DEFAULT value when creating the table. In my answer above, I am assuming you're working with a table that had already been created - meaning you couldn't do that without altering the table.

---

Related Topics