Changing the table structure

With ALTER TABLE the structure of a table can be modified. The attributes and constraints that were created with CREATE TABLE can be modified, new ones can be added, and existing ones can be deleted. The command has the following syntax:

ALTER TABLE <table name> <Change> ;

whereas <Change> can include various commands:

• ADD [COLUMN] <Attribute defintion>
  Add an attribute (Attribute definiton as in CREATE)
• ALTER [COLUMN] <Attribute name> SET DEFAULT <Standard value>
  define a new standard value
• ALTER [COLUMN] <Attribute name> DROP DEFAULT
  delete current standard value
• DROP [COLUMN] <Attribute name> {RESTRICT | CASCADE}
  delete an attribute
• ADD <Table constraint>
  add new table constraint (table constraint as in CREATE)
• DROP CONSTRAINT <Table constraint>
  delet a table constraint

With the above commands, attributes and constraints can be added or deleted respectively. In addition, standard values for the attributes can be set or deleted. There are other SQL commands that are not listed here.
Default SQL does not include any commands for modification or renaming of attributes. This would lead to problems when data already exists. However, in some databases these commands are included (e.g. MODIFY or RENAME). The syntax is different in every system though. If there are no data, the attribute to be changed can be delete and reattached.

In this example, an attribute is added to a table. The dataset shown contains NULL for this attribute because there was no value assigned yet. Afterwards, this attribute is delted from the table again. The keyword RESTRICT provokes that only attributes that are not linked to other tables can be deleted (foreign key). Alternatively, the keyword CASCADE can be used. Using this, not only the designated column but also the linked column in the other table is delted.