AntDB-Oracle Compatibility Developer's Manual P3–6
Published: 2023-08-31 08:40:13

**Description**

ALTER TABLE changes the definition of an existing table. The following are the subcommands that can follow after this command.

MODIFY column type

This subcommand serves to redefine the data type of a column and can modify one or more columns at a time.

ADD column type

The syntax of this subcommand is the same as in the CREATE TABLE command, which adds a new column to a table.

DROP COLUMN

The effect of this subcommand is to remove a column from a table. It also removes the indexes and table constraints that involve this column.

ADD table\_constraint

The syntax of this subcommand is the same as that used in the CREATE TABLE command, which has the effect of adding a new constraint to a table.

DROP CONSTRAINT

This subcommand removes the constraint from a table of data. It is not usually required that constraints on a table have a unique name, so there may be multiple constraints here that match the specified name. All constraints that match the specified name are deleted.

RENAME

The RENAME command changes the name of a table (or index, sequence, or view) or individual columns on a table without having any effect on the stored data.

Only the owner of the table, can use the ALTER TABLE command.

**argument**

name

The name of a table to be modified (which can be referenced in a schema-qualified manner).

column

The name of a new or existing column.

new\_column

A new name for an existing column.

new\_name

A new name for the table. 

type

The data type of the newly added column.

table\_constraint

A new table constraint on the table.

constraint\_name

The name of the constraint to delete.

CASCADE

Automatically delete objects that depend on the deleted constraint.

**Note**

When adding a column using the ADD COLUMN command, the value of the column is initialized to the default value of the column in all records (if the DEFAULT clause is not specified, then the default value is null).

Adding a column with a non-null default value will require a rewrite of the entire table. For large tables, this will take a long time; it will temporarily require double the disk space.

Adding a CHECK or NOT NULL constraint can require that existing rows be checked for compliance when the table is scanned.

The DROP COLUMN command does not actually delete the column, but simply sets the column to be invisible to SQL operations. Subsequent insert and update operations store only a null value for this column. Therefore, the operation to delete a column is fast, but does not immediately reduce the storage space on disk for the data table. This is because no space is reclaimed for the deleted column. Only when an update operation is performed on an already existing record, the associated storage space is reclaimed.

No modification of any part of the system view is allowed.

For more description of valid parameters, refer to the CREATE TABLE command.

**Example**

Add a column of type VARCHAR2 to the table.

```
ALTER TABLE emp ADD COLUMN address VARCHAR2(30); 
```

Delete a column from the table.

```
ALTER TABLE emp DROP COLUMN address; 
```

Rename an existing column.

```
ALTER TABLE emp RENAME COLUMN address TO city; 
```

Rename an existing table.

```
ALTER TABLE emp RENAME TO employee; 
```

Add a check constraint to a table.

```
ALTER TABLE emp ADD CONSTRAINT sal_chk CHECK (sal \> 500); 
```

Remove a check constraint from a table.

```
ALTER TABLE emp DROP CONSTRAINT sal_chk; 
```

#### COMMENT

**Name**

COMMENT - defines or changes the object's comment

**Summary**

COMMENT ON

{

TABLE table\_name \|

COLUMN table\_name.column\_name

} IS 'text'

**Description**

The COMMENT command is used to save the comments of a database object. If you modify the annotation of an object, you need to run a new COMMENT command under the same object. Only one comment string can be stored per object. If you want to delete a comment, specify an empty string (two consecutive single quotes with no space between them) for 'text' and you are done. When the object is deleted, the associated comment is automatically deleted as well.

**argument**

table\_name

The name of the annotated table, the table name can be referenced in a schema-qualified manner.

table\_name.column\_name

The column name of the annotated table specified by the table\_name parameter. The table name can be referenced in a schema-qualified manner.

text

New comments.

**Note**

There is no corresponding security mechanism for annotations. Any user logged on to the database can see the annotations of all objects in the database (although only the superuser can modify the annotations of objects it does not own). Therefore, do not include information in the annotations that is important for security.

**Example**

Add a comment to table emp.

```
COMMENT ON TABLE emp IS 'Current employee information'; 
```

Add a comment to the empno column of the emp table.

```
COMMENT ON COLUMN emp.empno IS 'Employee identification number';
```

Delete the comment added above:

```
COMMENT ON TABLE emp IS ''; COMMENT ON COLUMN emp.empno IS '';
```