This is the 31st day of my participation in the August Challenge

ALTER TABLE ALTER TABLE

Modify the table.

The outline

ALTER TABLE table alter-action

where alter-action is one of the following:
     ADD  [(] add-action {,add-action} [)] |
     DROP [COLUMN ] drop-column-action {,drop-column-action} |
     DROP drop-action |
     DELETE drop-action |
     ALTER [COLUMN] field alter-column-action |
     MODIFY modification-spec {,modification-spec}
     RENAME table

add-action ::= 
     [CONSTRAINT identifier]
     [(] FOREIGN KEY (field-commalist) 
          REFERENCES table (field-commalist)
          [ON DELETE ref-action] [ON UPDATE ref-action] 
          [NOCHECK]  [)]
     |
     [(] UNIQUE (field-commalist)  [)] 
     |
     [(] PRIMARY KEY (field-commalist) [)] 
     | 
     DEFAULT [(] default-spec [)] FOR field
     |
     [COLUMN] [(] field datatype  [sqlcollation] 
           [%DESCRIPTION string]
           [DEFAULT [(] default-spec [)] ]
           [ON UPDATE update-spec ]
           [UNIQUE] [NOT NULL]
           [REFERENCES table (field-commalist) 
              [ON DELETE ref-action] [ON UPDATE ref-action] 
              [NOCHECK]  ]
           [)]

drop-column-action ::= 
       [COLUMN] field [RESTRICT | CASCADE] [%DELDATA | %NODELDATA] 

drop-action ::= 
     FOREIGN KEY identifier |
     PRIMARY KEY |
     CONSTRAINT identifier |

alter-column-action ::= 
     RENAME newfieldname |
     datatype | 
     [SET] DEFAULT [(] default-spec [)]  |  DROP DEFAULT | 
     NULL |  NOT NULL | 
     COLLATE sqlcollation

modification-spec ::=
     oldfieldname RENAME newfieldname |
     field [datatype] 
          [DEFAULT [(] default-spec [)]]
          [CONSTRAINT identifier] [NULL] [NOT NULL]

sqlcollation ::=
     { %EXACT | %MINUS | %MVR | %PLUS | %SPACE |   
        %SQLSTRING [(maxlen)] | %SQLUPPER [(maxlen)] |
        %TRUNCATE[(maxlen)]  }
Copy the code

parameter

describe

ALTER TABLE statement ALTER TABLE definition; It can add elements, delete elements, or modify existing elements. Only one type of operation can be performed in each ALTER TABLE statement.

• RENAMEYou can rename the table or use itALTER COLUMNorMODIFYSyntax renames existing columns in a table.
• AddYou can add multiple columns and/or constraints to a table. You only need to specify it onceADDKeyword, followed by a comma-separated list. You can use comma-separated lists to add multiple new columns to a table, add a list of constraints to an existing column, or add both new columns and constraints to an existing column.
• DROP COLUMNMultiple columns can be deleted from a table. You only need to specify it onceDROPKeyword, followed by a comma-separated list of columns, each with optional cascading and/or data deletion options.
• ALTER COLUMNYou can change the definition of a single column. It cannot change multiple columns.
• MODIFYYou can change the definition of a single column or a comma-separated list of columns. It does not supportALTER COLUMNAll options provided.
• Delete Can remove constraints from one or a group of fields.DROPYou can only operate on a single constraint.

The ALTER TABLE DROP keyword and the ALTER TABLE DELETE keyword are synonyms.

To determine the current namespace in the existence of a specified table, please use $SYSTEM. SQL. Schema. TableExists () method.

Permissions and lock

The ALTER TABLE command is a privileged operation. A user must have %ALTER_TABLE administrative permission to execute ALTER TABLE. The % MSG User ‘name’ does not have %ALTER_TABLE PRIVILEGES.

The user must have %ALTER privilege on the specified table. If the user is the owner (creator) of the table, the user is automatically granted %ALTER permission on the table. Otherwise, the user must be granted the %ALTER privilege on the table. % MSG User ‘name’ does not have required %ALTER privilege needed to change the table definition for ‘Schema.TableName’.

To determine whether the current user has the %ALTER privilege, call the %CHECKPRIV command. To determine whether specified users have % ALTER privileges, please call $SYSTEM. SQL. Security. CheckPrivileve () method.

To assign the desired administrative permissions, use the GRANT command with the %ALTER_TABLE permission; This requires appropriate grant permissions. To assign %ALTER OBJECT permissions, use:

• with%ALTERThe powers of theGRANTCommand. This requires appropriate grant permissions.
• On the page used to edit roles or users, in the administrative portalSQLThe table change check box on the table TAB. This requires appropriate grant permissions.

In embedded SQL, you can use the $system.security.login () method to Login as a user with the appropriate permissions:

   DO $SYSTEM.Security.Login("_SYSTEM"."SYS")
   &sql(      )
Copy the code

You must have %Service_Login: Use permission to invoke the $system.security.login method.

• Unless the table class definition includes[DdlAllowed]Otherwise, it cannot be used on tables projected from persistent classesALTER TABLE. Otherwise, the operation will fail and displaySQLCODE-300errorthe %msg DDL not enabled for class 'Schema.tablename'.
• ALTER TABLETables that cannot be used for projection from deployed persistent classes. This operation fails and displaysSQLCODE-400errorthe %msg Unable to execute DDL that modifies a deployed class: 'classname'.

ALTER TABLE Obtains a table-level lock on a TABLE. This prevents other processes from modifying the table data. This lock is automatically released at the end of the ALTER TABLE operation. When ALTER TABLE locks the corresponding class definition, it uses the CURRENT process’s SQL Lock timeout setting.

To change a table, the table cannot be locked by another process in exclusive or shared mode. A % MSG such as the following: Unable to acquire exclusive table lock for table ‘sample. MyTest’.

Rename table

You can rename an existing table using the following syntax:

ALTER TABLE schema.TableName RENAME NewTableName
Copy the code

This action renames an existing table in its existing schema. You can only change the table name, not the table schema. Specifying the schema name in NewTableName causes a SQLCODE-1 error. Specifying the same table name for both the old and new tables generates a SQLCODE-201 error.

Renaming the table changes the SQL table name. It does not change the corresponding permanent class name.

Renaming the table does not change the reference to the old table name in the trigger.

If the view references an existing table name, renaming the table will fail. This is because attempting to rename the table is an atomic operation that causes the view to be recompiled, resulting in a SQLcode-30 error. “The Table schema. Oldname not found”.

Add column limits

Adding columns You can add a single column or a comma-separated list of columns.

ALTER TABLE TABLE NAME ADD COLUMN ALTER TABLE TABLE NAME ADD COLUMN

• If a column with that name already exists, the statement will fail and displaySQLCODE-306Error.
• If the statement specifies a columnNOT NULLConstraint, and the column has no default value, the statement will fail if data already exists in the table. This is because of the completionDDLAfter the statement, all pre-existing rows are not satisfiedNOT NULLConstraints. This generates error codeSQLCODE-304Attempt to add a non-empty field with no default value to a table containing data.
• If the statement specifies a columnNOT NULLConstraint, and the column has a default value, the statement updates all existing rows in the table and assigns the column’s default value to the field. This includesCURRENT_TIMESTAMPAnd so on.
• If the statement does not specify a columnNOT NULLConstraint, and the column has a default value, the column will not be updated in any existing rows. The column values of these rows are zeroNULL.

To change the behavior of this default NOT NULL constraint, refer to SET OPTION command COMPILEMODE=NOCHECK.

If you specify a plain data field named “ID” and the RowID field is already named “ID” (the default), the column addition operation will succeed. ALTER TABLE adds the ID data column and renames the RowId column to “ID1” to avoid duplicate names.

Add integer counter

ALTER TABLE TABLE NAME ADD COLUMN ALTER TABLE TABLE NAME ADD COLUMN

• If the table does not have an identity field, you can add an identity field to the table. If the table already has an identity fieldALTER TABLEThe operation will fail and displaySQLCODE-400Error and display as follows%msg:ERROR #5281: Class has multiple identity properties: 'Sample.MyTest::MyIdent2'. When you define this field using the add column, IRIS will use the correspondingRowIDInteger values populate the existing data rows for this field.

If the CREATE TABLE defines a bitmap index and then adds an identity field to the TABLE that is not of type %BigInt, %Integer, %SmallInt, or %TinyInt whose MINVAL is 1 or higher, and there is no data in the TABLE, the system automatically deletes the bitmap index.

• You can add one or more sequences to a table (%Library.Counter) field. When you define this field with Add Column, the existing data behavior for this field is empty. You can useUPDATESupply values to existing data rows for which this field is NULL; You can’t useUPDATEChange theNULLValue.
• If the table does not haveROWVERSIONField can be added to the tableROWVERSIONField. If the table already hasROWVERSIONField,ALTER TABLEThe operation will fail and displaySQLCODE-400Error and display as follows% MSG: : ERROR #5320: Class 'Sample.MyTest' has more than one property of type %Library.RowVersion. Only one is allowed. Properties: MyVer,MyVer2. The existing data behavior of this field when it is defined using the Add columnNULL; Cannot be updated toNULLtheROWVERSIONValue.

Changing column limits

ALTER COLUMN can ALTER the definition of a single COLUMN:

• Use the syntaxALTER TABLE TABLE NAME ALTER COLUMN oldname rename newnameRename columns. Renaming the column changesSQLField name. It does not change the corresponding persistent class attribute name.ALTER COLUMN OLDNAME RENAME NEWNAMEReplace trigger code andComputeCodeThe old field name reference in.
• Change column characteristics: data type, default value,NULL/NOT NULLAnd collation types.

If the table contains data, you cannot change the data type of the column that contains the data, which would result in the streaming data type being non-streaming or the non-streaming data type being streaming. Attempting to do so results in a SQLCODE -374 error. This type of data type change is allowed if there is no existing data.

You can use ALTER COLUMN to add, change, or delete field defaults.

NOT NULL cannot be specified if the table contains data and if the column contains NULL values; This will result in a SQLCODE -305 error.

If you change the collation type of a column that contains data, all indexes for that column must be rebuilt.

Modify column limits

MODIFY can MODIFY the definition of a single column or a comma-separated list of columns.

• Use the syntaxALTER TABLE tablename MODIFY oldname RENAME newnameRename the column. Renaming the column changesSQLField name. It does not change the corresponding persistent class attribute name.Modify oldnamerenamenewnameReplace trigger code andComputeCodeThe old field name reference in.
• Change column characteristics: data type, default values, and other characteristics.

If the table contains data, you cannot change the data type of a column containing data to an incompatible data type:

• Data type A data type with a lower priority (less contained) if this conflicts with existing data values. Trying to do so would lead toSQLCODE-104Error, where%msgSpecify which field and which data value caused the error.
• With smallerMAXLENorMAXVAL/MINVALData type (if this conflicts with existing data values). Trying to do so would lead toSQLCODE-104Error, where%msgSpecify which field and which data value caused the error.
• A data type is changed from a streaming data type to a non-streaming data type or from a non-streaming data type to a streaming data type. Trying to do so would lead toSQLCODE-374Error. This type of data type change is allowed if there is no existing data.

You can use modifications to add or change field defaults. You cannot use modifications to remove field defaults.

If the table contains data, you cannot specify NOT NULL for a column that contains NULL values. This causes a SQLCODE-305 error. Syntax ALTER TABLE mytable MODIFY field1 NOT NULL and ALTER TABLE mytable MODIFY field1 CONSTRAINT nevernull NOT NULL performs the same operation. The optional constraint identifier clause is a no-operation provided for compatibility. Do not reserve or use this field to constrain the name. An attempt to remove this field constraint by specifying the field constraint name causes a SQLCODE-315 error.