Re: changing the nulability of columns - Mailing list pgsql-hackers

As a follow up to my previous post, this is how MS-SQL defines ALTER TABLE
asfd ALTER COLUMN:

------------------------------------------

ALTER COLUMN

ALTER TABLE table
{ [ ALTER COLUMN column_name   { new_data_type [ ( precision [ , scale ] ) ]       [ COLLATE < collation_name > ]
[NULL | NOT NULL ]       | {ADD | DROP } ROWGUIDCOL }   ]   | ADD       { [ < column_definition > ]       |
column_nameAS computed_column_expression       } [ ,...n ]   | [ WITH CHECK | WITH NOCHECK ] ADD       { <
table_constraint> } [ ,...n ]   | DROP       { [ CONSTRAINT ] constraint_name           | COLUMN column } [ ,...n ]   |
{CHECK | NOCHECK } CONSTRAINT       { ALL | constraint_name [ ,...n ] }   | { ENABLE | DISABLE } TRIGGER       { ALL |
trigger_name[ ,...n ] }
 
}


Specifies that the given column is to be changed or altered. ALTER COLUMN is
not allowed if the compatibility level is 65 or earlier. For more
information, see sp_dbcmptlevel.

The altered column cannot be:

A column with a text, image, ntext, or timestamp data type.


The ROWGUIDCOL for the table.


A computed column or used in a computed column.


A replicated column.


Used in an index, unless the column is a varchar, nvarchar, or varbinary
data type, the data type is not changed, and the new size is equal to or
larger than the old size.


Used in statistics generated by the CREATE STATISTICS statement. First
remove the statistics using the DROP STATISTICS statement. Statistics
automatically generated by the query optimizer are automatically dropped by
ALTER COLUMN.


Used in a PRIMARY KEY or [FOREIGN KEY] REFERENCES constraint.


Used in a CHECK or UNIQUE constraint, except that altering the length of a
variable-length column used in a CHECK or UNIQUE constraint is allowed.


Associated with a default, except that changing the length, precision, or
scale of a column is allowed if the data type is not changed.
Some data type changes may result in a change in the data. For example,
changing an nchar or nvarchar column to char or varchar can result in the
conversion of extended characters. For more information, see CAST and
CONVERT. Reducing the precision and scale of a column may result in data
truncation.

column_name

Is the name of the column to be altered, added, or dropped. For new columns,
column_name can be omitted for columns created with a timestamp data type.
The name timestamp is used if no column_name is specified for a timestamp
data type column.

new_data_type

Is the new data type for the altered column. Criteria for the new_data_type
of an altered column are:

The previous data type must be implicitly convertible to the new data type.


new_data_type cannot be timestamp.


ANSI null defaults are always on for ALTER COLUMN; if not specified, the
column is nullable.


ANSI padding is always on for ALTER COLUMN.


If the altered column is an identity column, new_data_type must be a data
type that supports the identity property.


The current setting for SET ARITHABORT is ignored. ALTER TABLE operates as
if the ARITHABORT option is ON.
precision

Is the precision for the specified data type. For more information about
valid precision values, see Precision, Scale, and Length.

scale

Is the scale for the specified data type. For more information about valid
scale values, see Precision, Scale, and Length.

COLLATE < collation_name >

Specifies the new collation for the altered column. Collation name can be
either a Windows collation name or a SQL collation name. For a list and more
information, see Windows Collation Name and SQL Collation Name.

The COLLATE clause can be used to alter the collations only of columns of
the char, varchar, text, nchar, nvarchar, and ntext data types. If not
specified, the column is assigned the default collation of the database.

ALTER COLUMN cannot have a collation change if any of the following
conditions apply:

If a check constraint, foreign key constraint, or computed columns reference
the column changed.


If any index, statistics, or full-text index are created on the column.
Statistics created automatically on the column changed will be dropped if
the column collation is altered.


If a SCHEMABOUND view or function references the column.
For more information about the COLLATE clause, see COLLATE.

NULL | NOT NULL

Specifies whether the column can accept null values. Columns that do not
allow null values can be added with ALTER TABLE only if they have a default
specified. A new column added to a table must either allow null values, or
the column must be specified with a default value.

If the new column allows null values and no default is specified, the new
column contains a null value for each row in the table. If the new column
allows null values and a default definition is added with the new column,
the WITH VALUES option can be used to store the default value in the new
column for each existing row in the table.

If the new column does not allow null values, a DEFAULT definition must be
added with the new column, and the new column automatically loads with the
default value in the new columns in each existing row.

NULL can be specified in ALTER COLUMN to make a NOT NULL column allow null
values, except for columns in PRIMARY KEY constraints. NOT NULL can be
specified in ALTER COLUMN only if the column contains no null values. The
null values must be updated to some value before the ALTER COLUMN NOT NULL
is allowed, such as:

UPDATE MyTable SET NullCol = N'some_value' WHERE NullCol IS NULL

ALTER TABLE MyTable ALTER COLUMN NullCOl NVARCHAR(20) NOT NULL

If NULL or NOT NULL is specified with ALTER COLUMN, new_data_type
[(precision [, scale ])] must also be specified. If the data type,
precision, and scale are not changed, specify the current column values.