Обсуждение: changing the nulability of columns

Hi guys,

This is like the 10th time I've tried to post this!  Every time I send a
mail with SET NULL / SET NOT NULL in the subject - it never appears on the
list - what's going on?????

I've been chatting to Tom about implementing the ability to change the NULL
status of a column via SQL.

This is the Oracle syntax:

alter table table_name modify column1 not null;
alter table table_name modify column1 null;

This is the MySQL syntax:

ALTER TABLE asfd CHANGE [COLUMN] old_col_name create_definition [FIRST |
AFTER column_name]
or    ALTER TABLE asfd MODIFY [COLUMN] create_definition [FIRST | AFTER
column_name]

CHANGE col_name, DROP col_name, and DROP INDEX are MySQL extensions to ANSI
SQL92.
MODIFY is an Oracle extension to ALTER TABLE.

So, the question is - what the heck is the standard syntax?  Is there a
standard syntax?  How about this syntax that I came up with:

ALTER TABLE blah ALTER COLUMN col SET [NULL | NOT NULL]

Anyone have any ideas?  Perhaps we should use some sort of 'MODIFY'-like
syntax to enable in the future maybe the ability to change column specs in
more advanced ways (such as column type and size)

If the answer is no, Postgres's parser does not have this syntax enabled,
then I'm going to have to ask someone to implement it for me, and then I can
fill in the actual guts of the function - whereever that may be.  (I don't
know parser stuff!)

Chris

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.

> So, the question is - what the heck is the standard syntax?  Is there a
> standard syntax?  How about this syntax that I came up with:
>
> ALTER TABLE blah ALTER COLUMN col SET [NULL | NOT NULL]

If there is no standard syntax for this, I would recommend emulating oracle 
or SQL server rather than coming up a new one.  Why create yet another SQL 
extension that is not compatible with anyone elses.