ALTER TABLE

This command allows:

• add a field;
• drop a field;
• rename a field
• change the field type.

• change the DEFAULT value of a field;
• drop DEFAULT value of a field

• add or delete the table PRIMARY KEY;
• add or delete the table FOREIGN KEY;

• add or delete UNIQUE constraint;
• add or delete CHECK constraint;

alter_table_statement
	:	ALTER TABLE table_name alter_table_action [ , alter_table_action ... ]

alter_table_action
	:	ADD [COLUMN]   column_definition  
	|	ADD [COLUMN] ( column_definition [, column_definition ... ] )

	|	ADD   table_constraint_definition 
	|	ADD ( table_constraint_definition [, table_constraint_definition ... ] ) 

	|	ALTER [COLUMN] column_name alter_column_action  
	|	DROP  [COLUMN] [IF EXISTS] column_name [ drop_behavior ]

	|	DROP PRIMARY KEY  
	|	DROP INDEX index_name 
	|	DROP CONSTRAINT [IF EXISTS] IDENT 

	|	CHANGE [COLUMN] old_col_name column_definition 
	|	MODIFY [COLUMN] column_definition 
	|	RENAME [AS] table_name 


alter_column_action
    :    SET default_clause 
    |    DROP DEFAULT 
    |    RENAME [AS] column_name

Field's definition in the ALTER TABLE command have the same format as in the CREATE TABLE command, and performs the same task.

New fields are added as the last ones on the right of the table.

If the table contains records, the new field is inserted with NULL values. If the field is declared as NOT NULL, the numerical fields will get zero value and the string fields - empty strings.

Add new field contact_phone to customers table:

ALTER TABLE customers
ADD CONTACT_PHONE VARCHAR(30)

or

ALTER TABLE customers
ADD COLUMN CONTACT_PHONE VARCHAR(30)

Add new fields contact_name and contact_surname and method contact_fullname to customers table:

ALTER TABLE customers
ADD COLUMN CONTACT_NAME VARCHAR(30),
ADD COLUMN CONTACT_SURNAME VARCHAR(30),
ADD COLUMN CONTACT_FULLNAME VARCHAR(60) Method('CONTACT_NAME || CONTACT_SURNAME')

DROP col_name is MySQL extensions to standard SQL. Valentina uses it for compatibility.

ALTER TABLE gets ability to remove one or several fields from the existing table.

Some problems can occur when you try to delete fields if some link is defined using such a field. You can solve it with the help of the RESTRICT or CASCADE parameters:

• RESTRICT: if any database object is linked to the deleted field, the command will produce an error message and the object will not be deleted.

• CASCADE: any database object linked with the deleting field will be deleted

CASCADE parameter can cause a wave of changes, therefore it should be used carefully. It is better to use RESTRICT, and modify the table structure with the help of additional commands such as ALTER or DROP. (RESTRICT parameter is used as default one.)

ALTER TABLE customers
    DROP contact_name  

[NEW in 5.0]

You can easy rename a field of table using this form of ALTER TABLE command:

ALTER TABLE customers
    ALTER COLUMN contact_name RENAME AS new_contact_name 

CHANGE col_name is MySQL extensions to standard SQL. Valentina uses it for compatibility.

Using the CHANGE COLUMN statement, you can:

• rename the field. In order to do this you should point the old name, new field name and the current field type.

ALTER TABLE Person CHANGE LastName Surnname String ( 40 )

• change the field type. To do this you should point the field name and the same name as a new one with the pointing field type.

ALTER TABLE Person CHANGE age age short

• change the field length. To do this you should point the field name and the same name as a new one with the pointing field type and the changed field length.

ALTER TABLE Person CHANGE SurnName Surnname String ( 30 )

MODIFY statement usage allows not to use the field name twice in the last two cases. MODIFY is an Oracle extension.

ALTER TABLE Person MODIFY age short

ALTER TABLE Person MODIFY  Surnname String ( 30 )

Assume there was a method FullName defined as 'FirstName || LastName' (see ADD COLUMN example above) But now we realize that there is no space inside - so we want to change the text of the method.

ALTER TABLE Person MODIFY  FullName VARCHAR( 60 ) METHOD('FirstName || \' \' || LastName')

Also note - we may change not only the text of a method but its type, length and name. One more important thing - you will get an error ERR_FIELD_CANT_BE_METHOD on attempt to convert some ordinal field to method and vice versa.

Using ALTER TABLE command, you could add into the table or delete from it the definitions of primary and(or) foreign keys. Statements which add primary and foreign keys are the same ones as defined in CREATE TABLE command, and performs the same task. Statements which delete primary or foreign keys are simple enough.

ALTER TABLE offices
	ADD CONSTRAINT inregion
	FOREIGN KEY ( region )
	REFERENCES regions

ALTER TABLE salesreps
	DROP CONSTRAINT worksin

It is possible to delete foreign key only if the link created by it has the name. If link wasn’t named it is not possible to mention this link in the ALTER TABLE command.

Above we considered the changing of the restrictions for the primary and foreign keys. Besides in the ALTER TABLE command you can change UNIQUE and CHECK constraints.

ALTER TABLE offices
	DROP CONSTRAINT quota_cap CHECK

In order to rename the table from t1 to t2:

ALTER TABLE t1 RENAME t2;

Using the SET DEFAULT statement, you can define the default value of any field:

ALTER TABLE age ALTER COLUMN SET DEFAULT ‘25’

In order to delete the default value for any field DROP DEFAULT statement can be used:

ALTER TABLE age ALTER COLUMN fldName DROP DEFAULT