This page is part of the book SQL-99 Complete, Really, by Peter Gulutzan & Trudy Pelzer. The authors have graciously allowed us to reproduce the contents of the book here. Because the book is about the SQL-99 standard, the contents of this and other pages in the book may not directly apply to MariaDB. Use the navigation bar to navigate the book.

The ALTER TABLE statement changes a Base table's definition. The required syntax for the ALTER TABLE statement is:

ALTER TABLE <Table name> <alter table action> 

   <alter table action> ::=
   ADD [ COLUMN ] <Column definition> | 
   ALTER [ COLUMN ] <Column name> SET DEFAULT default value | 
   ALTER [ COLUMN ] <Column name> DROP DEFAULT | 
   ALTER [ COLUMN ] <Column name> ADD SCOPE <Table name list> | 
   ALTER [ COLUMN ] <Column name> DROP SCOPE {RESTRICT | CASCADE} | 
   DROP [ COLUMN ] <Column name> {RESTRICT | CASCADE} | 
   ADD <Table Constraint> | 
   DROP CONSTRAINT <Constraint name> {RESTRICT | CASCADE}

    <Table name list> ::=
   (<Table name> [ {,<Table name>}... ]) | 
   <Table name>

The <Table name> must identify an existing Base table whose owner is either the current <AuthorizationID> or a Role that the current <AuthorizationID> may use. That is, only the <AuthorizationID> that owns the Table may alter it. ALTER TABLE can be used to change a persistent Base table, a GLOBAL TEMPORARY Base table or a created LOCAL TEMPORARY Base table, but you can't use it to change a declared LOCAL TEMPORARY Base table.

ADD [ COLUMN ] clause

The effect of ALTER TABLE <Table name> ADD [COLUMN] <Column definition>, e.g.:

   ALTER TABLE Table_1 ADD COLUMN 
      column_1 SMALLINT DEFAULT 150 
         CONSTRAINT constraint_1 NOT NULL NOT DEFERRABLE;

is that the Table named will increase in size by one Column: the Column defined by the <Column definition>. The <keyword> COLUMN in the ADD [COLUMN] clause is noise and can be omitted. For example, these two SQL statements are equivalent:

   ALTER TABLE Table_1 ADD COLUMN 
      column_1 SMALLINT DEFAULT 150; 

   ALTER TABLE Table_1 ADD 
      column_1 SMALLINT DEFAULT 150; 

Adding a new Column to a Table has a four-fold effect:

  1. The degree (i.e.: the number of Columns) of the Table is increased by 1; the new Column's ordinal position in the Table is the new degree of the Table.
  2. Every <AuthorizationID> that has a SELECT, UPDATE, INSERT, or REFERENCES Privilege on all existing Columns of the Table receives a matching set of Privileges on the new Column. The grantor of the new Privilege(s) is the same as the grantor of the previous Privileges(s) and so is the grantability of the new Privilege(s).
  3. The value of the new Column for every existing row of the Table is set to its default value.
  4. The Column is added to the Column list of every UPDATE Trigger event for all Triggers that act on the Table. However, adding a new Column to a Table has no effect on any existing View definition or Constraint definition that refers to the Table because implicit <Column name>s in these definitions are replaced by explicit <Column name>s the first time the View or Constraint is evaluated.

ALTER [ COLUMN ] ... SET DEFAULT clause:

The effect of ALTER TABLE <Table name> ALTER [COLUMN] <Column name> SET DEFAULT default value, e.g.:

ALTER TABLE Table_1 ALTER COLUMN 
  column_1 SET DEFAULT 200;

is that the default value of the Column named will be changed. (You can use this version of ALTER TABLE either to add a default value to a <Column definition> or to change a Column's existing default value.) The <keyword> COLUMN in the ALTER [COLUMN] clause is noise and can be omitted. For example, these two SQL statements are equivalent:

ALTER TABLE Table_1 ALTER COLUMN 
  column_1 SET DEFAULT CURRENT_TIME;

ALTER TABLE Table_1 ALTER 
  column_1 SET DEFAULT CURRENT_TIME;

The ALTER [COLUMN] ... SET DEFAULT options are: DEFAULT <literal>, DEFAULT CURRENT_DATE, DEFAULT CURRENT_TIME(p), DEFAULT CURRENT_TIMESTAMP(p), DEFAULT LOCALTIME(p), DEFAULT LOCALTIMESTAMP(p), DEFAULT USER, DEFAULT CURRENT_USER, DEFAULT SESSION_USER, DEFAULT SYSTEM_USER, DEFAULT CURRENT_PATH, DEFAULT ARRAY[], DEFAULT ARRAY??(??), and DEFAULT NULL see "<default clause>", earlier in this chapter.

ALTER [ COLUMN ] ... DROP DEFAULT clause

The effect of ALTER TABLE <Table name> ALTER [COLUMN] <Column name> DROP DEFAULT, e.g.:

ALTER TABLE Table_1 ALTER COLUMN 
    column_1 DROP DEFAULT;

is that the default value of the Column named will be removed from the <Column definition>. (You'll get a syntax error if the Column's definition doesn't include a default value.) The <keyword> COLUMN in the ALTER [COLUMN] clause is noise and can be omitted. For example, these two SQL statements are equivalent:

ALTER TABLE Table_1 ALTER COLUMN 
  column_1 DROP DEFAULT;

ALTER TABLE Table_1 ALTER 
  column_1 DROP DEFAULT;

ALTER [ COLUMN ] ... ADD SCOPE clause:

The effect of ALTER TABLE <Table name> ALTER [COLUMN] <Column name> ADD SCOPE <Table name>, e.g.:

ALTER TABLE Table_2 ALTER COLUMN 
  column_1 ADD SCOPE Table_1;

is that a non-empty scope is added to the <Column definition> of the Column named. This version of ALTER TABLE can only be used (a) for Columns with a REF(UDT) <data type>, where the <reference type> descriptor includes an empty scope and (b) where the Column named is not the referenceable Column of its Table. (The Table named in the SCOPE clause must, of course, be a referenceable Base table whose structured type is the same as the structured type of the referenced UDT.) The <keyword> COLUMN in the ALTER [COLUMN] clause is noise and can be omitted. For example, these two SQL statements are equivalent:

ALTER TABLE Table_3 ALTER COLUMN 
  column_1 ADD SCOPE (Table_1,Table_2);

ALTER TABLE Table_3 ALTER 
  column_1 ADD SCOPE (Table_1,Table_2);

ALTER [ COLUMN ] ... DROP SCOPE clause

The effect of ALTER TABLE <Table name> ALTER [COLUMN] <Column name> DROP SCOPE RESTRICT, e.g.:

ALTER TABLE Table_2 ALTER COLUMN 
  column_1 DROP SCOPE RESTRICT;

is that the SCOPE clause in the definition of the Column named becomes empty, provided that no impacted dereference operation is contained in an SQL routine, in a View definition, in a Constraint or Assertion definition or in the triggered action of a Trigger definition. (An impacted dereference operation is a <dereference operation> that operates on the Column named, a <method reference> that operates on the Column named or a <reference resolution> that operates on the Column named.) That is, RESTRICT ensures that only a scope with no dependent Objects can be made empty. If the Column is operated on by any impacted dereference operation, ALTER TABLE ... DROP SCOPE RESTRICT will fail.

The effect of ALTER TABLE <Table name> ALTER [COLUMN] <Column name> DROP SCOPE CASCADE, e.g.:

ALTER TABLE Table_2 ALTER COLUMN 
  column_1 DROP SCOPE CASCADE;

is that the SCOPE clause in the definition of the Column named becomes empty and that all Objects which contain an impacted dereference operation for the Column are also dropped, with the CASCADE drop behaviour (except for Assertions, where this is not applicable). This version of ALTER TABLE can only be used (a) for Columns with a REF(UDT) <data type>, where the <reference type> descriptor includes a SCOPE clause and (b) where the Column named is not the referenceable Column of its Table. The <keyword> COLUMN in the ALTER [COLUMN] clause is noise and can be omitted. For example, these two SQL statements are equivalent:

ALTER TABLE Table_2 ALTER COLUMN 
  column_1 DROP SCOPE RESTRICT;

ALTER TABLE Table_2 ALTER 
  column_1 DROP SCOPE RESTRICT;

DROP [ COLUMN ] clause

The effect of ALTER TABLE <Table name> DROP [COLUMN] <Column name> RESTRICT, e.g.:

ALTER TABLE Table_1 DROP COLUMN 
  column_1 RESTRICT;

is that the Column named is removed from the definition of the Table that owns it, provided that the Column is not referred to in any View definition, SQL routine, Trigger definition or in any Constraint or Assertion definition (with one exception) and, if the Column is the system-generated Column of its Table, provided that the Table is not named in any SCOPE clause. That is, RESTRICT ensures that only a Column with no dependent Objects can be destroyed. If the Column is used by any other Object, ALTER TABLE ... DROP COLUMN RESTRICT will fail. (Note: A Column referred to in a <Table Constraint> of the Table that owns the Column can be dropped despite the RESTRICT <keyword> if it is the only Column that the <Table Constraint> operates on.) The Column named may not be the only Column in its Table, since a Table must always contain at least one Column. If the Table is a typed Base table, the Column named must be the Table's referenceable Column.

The effect of ALTER TABLE <Table name> DROP [COLUMN] <Column name> CASCADE, e.g.:

ALTER TABLE Table_1 DROP COLUMN 
  column_1 CASCADE;

is that the Column named is removed from the definition of the Table that owns it and that all Objects which are dependent on the Column are also dropped. The <keyword> COLUMN in the DROP [COLUMN] clause is noise and can be omitted. For example, these two SQL statements are equivalent:

ALTER TABLE Table_1 DROP COLUMN 
  column_1 RESTRICT;

ALTER TABLE Table_1 DROP 
  column_1 RESTRICT;

Dropping a Column from a Table has a six-fold effect:

  1. The degree (i.e.: the number of Columns) of the Table is decreased by 1; the ordinal position of each Column that followed this Column in the Table's definition is adjusted accordingly.
  2. The INSERT, UPDATE, SELECT, and REFERENCES Privileges on the Column are revoked (by the SQL special grantor, "_SYSTEM") from the <AuthorizationID> that owns the Column's Table with a CASCADE drop behaviour, so that the same Privileges are also revoked from all other <AuthorizationID>s.
  3. Any Trigger whose definition explicitly includes the Column is dropped and any UPDATE Trigger whose definition includes the Column only implicitly is changed so that it no longer operates on that Column.
  4. Any View, Constraint, Assertion or SQL routine whose definition includes the Column is dropped with a CASCADE drop behaviour.
  5. If the Column is the system-generated Column of its Table, the Table's definition is changed so that it no longer shows the Table to be a referenceable Base table and the Table is removed from every SCOPE clause that includes it.
  6. The data in the Column is destroyed.

ADD <Table Constraint> clause

The effect of ALTER TABLE <Table name> ADD <Table Constraint>, e.g.:

ALTER TABLE Table_1 ADD CONSTRAINT 
  constraint_1 CHECK(column_1 IS NOT NULL) NOT DEFERRABLE;

is that one <Table Constraint> is added to the definition of the Table named see "<Table Constraint>" in our chapter on Constraints and Assertions.

DROP CONSTRAINT clause

The effect of ALTER TABLE <Table name> DROP CONSTRAINT <Constraint name> RESTRICT, e.g.:

ALTER TABLE Table_1 DROP CONSTRAINT constraint_1 RESTRICT;

is that the Constraint named is removed from the definition of the Table that owns it, provided that the Constraint is not used by any SQL routine, and provided that no other Constraint and no View are dependent on the Constraint. (A FOREIGN KEY Constraint is dependent on the UNIQUE or PRIMARY KEY Constraint that names its referenced Columns and a View is dependent on a Constraint if (a) it's a grouped View that includes a Column which isn't also referred to in a set function and (b) if the Constraint is needed to conclude that there is a known functional dependency between the group and the Column named.) That is, RESTRICT ensures that only a Constraint with no dependent Objects can be dropped. If the Constraint is used by any other Object, ALTER TABLE ... DROP CONSTRAINT will fail.

The effect of ALTER TABLE <Table name> DROP CONSTRAINT <Constraint name> CASCADE, e.g.:

ALTER TABLE Table_1 DROP CONSTRAINT constraint_1 CASCADE;

is that the Constraint named is removed from the definition of the Table that owns it and that all dependent Constraints, Views and SQL routines are also dropped with a CASCADE drop behaviour. (Note: If the dropped Constraint caused one or more Columns to have the "known not nullable" nullability characteristic, then the affected Columns' nullability characteristic becomes "possibly nullable" unless some other Constraint also constrains them to non- null values.)

If you want to restrict your code to Core SQL, don't use ALTER TABLE to drop a Column from a Table, to change a <Column definition> using any of the available options, to add a Constraint to a Table or to drop a Constraint from a Table.

Comments

Comments loading...