You can determine if the current user has %ALTER privilege by invoking the %CHECKPRIV command. Failing to do so results in an SQLCODE –99 error with the %msg User 'name' does not have required %ALTER privilege needed to change the table definition for 'Schema.TableName'. Otherwise, the user must be granted %ALTER privilege for the table. If the user is the Owner (creator) of the table, the user is automatically granted %ALTER privilege for that table. The user must have %ALTER privilege on the specified table. Failing to do so results in an SQLCODE –99 error with the %msg User does not have %ALTER_TABLE privileges. The user must have %ALTER_TABLE administrative privilege to execute ALTER TABLE. The ALTER TABLE command is a privileged operation. To determine if a specified table exists in the current namespace, use the $() Opens in a new tab method. The ALTER TABLE DROP keyword and the ALTER TABLE DELETE keyword are synonyms. DROP can only operate on a single column or constraint. The ALTER TABLE DROP statement can drop a column from a table or drop a constraint from a field or group of fields. The ALTER TABLE MODIFY statement can only operate on a single column. The ALTER TABLE ALTER COLUMN statement can alter the definition of a column. You can use a comma-separated list to add multiple new columns to a table, add a list of constraints to existing columns, or both add new columns and add constraints to existing columns. You specify the keyword ADD once, followed by a comma-separated list. You can only perform one type of operation in each ALTER TABLE statement.Īn ALTER TABLE ADD statement can add multiple columns and/or constraints to a table. For further details refer to Table Field/Property Definition Collation in the “Collation” chapter of Using InterSystems SQL.Īn ALTER TABLE statement modifies a table definition it can add elements, remove elements, or modify existing elements. The percent sign (%) prefix to these collation parameter keywords is optional. %SQLSTRING, %SQLUPPER, and %TRUNCATE may be specified with an optional maximum length truncation argument, an integer enclosed in parentheses. The default is the namespace default collation (%SQLUPPER, unless changed). Optional - Specify one of the following SQL collation types: %EXACT, %MINUS, %PLUS, %SPACE, %SQLSTRING, %SQLUPPER, %TRUNCATE, or %MVR. Do not use the SQL zero-length string as a default value. Allowed values are: a literal value one of the following keyword options (NULL, USER, CURRENT_USER, SESSION_USER, SYSTEM_USER, CURRENT_DATE, CURRENT_TIME, and CURRENT_TIMESTAMP) or an OBJECTSCRIPT expression.

For a list of valid data types, see the SQL reference material at the end of this manual.Ī default data value automatically supplied for this field, if not overridden by a user-supplied data value. For further details on valid identifiers, see the “Identifiers” chapter of Using InterSystems SQL.Ī valid InterSystems SQL data type. An field-commalist must be enclosed in parentheses, even when only a single column is specified. The name of a column or a comma-separated list of columns. The name of the column to be altered (added, modified, deleted). Schema search path values are not used.Ī unique name assigned to a constraint. An unqualified table name takes the default schema name.

The table name can be qualified (schema.table), or unqualified (table). DEFAULT default-spec | DROP DEFAULT |ĬOLLATE sqlcollation modification-spec ::=

Where alter-action is one of the following:ĪLTER field alter-column-action |

Alter sql