Alter table queries in sql. MySQL Reference Guide. ALTER TABLE to add keys

Team ALTER TABLE is used for additions, removal or modifications columns in an existing one table.

ALTER TABLE Command

Team ALTER TABLE modifies the table definition in one of the following ways:

• adds a column
• adds an integrity constraint
• overrides a column (data type, size, default value)
• deletes a column
• modifies memory characteristics or other parameters
• Enables, disables, or removes an integrity constraint or trigger.

Condition: The table must be in the user's schema, or the user must have system privilege ALTER ANY TABLE.

Adding a column with a constraint NOT NULL, developer or administrator DB must take into account a number of circumstances. You first need to create a column without a constraint, and then enter values ​​in all of its rows. After all column values ​​are no longer NULL-values, a constraint can be applied to it NOT NULL. If a column has a constraint NOT NULL tries to add the user, an error message is returned saying that either the table must be empty or the column must contain a value for every existing row (recall that after placing a constraint on the column NOT NULL cannot be present in it NULL-values ​​in none of the existing rows). IN Oracle DBMS Since version 8i, you can delete columns.

When changing the data types of existing columns or adding columns to a database table, you must meet a number of conditions. It is generally accepted that increasing is good, and decreasing is generally not so good. Allowable increases:

• Increasing Column Size CHAR or VARCHAR2
• Increasing Column Size NUMBER
• Adding new columns to a table

Decrease various characteristics table, including some column data types and the actual number of table columns, requires special considerations. Often, before making a change, you need to ensure that all values ​​in the relevant column or columns are correct. NULL-meanings. To perform such operations on table columns containing data, the developer must find or create some kind of area to temporarily store this data. For example, create a table using the command CREATE TABLE AS SELECT, which retrieves data from the primary key and the column or columns being modified. Allowed changes:

• Reducing Column Size NUMBER
• Reducing Column Size CHAR or VARCHAR2(only with empty column for all rows)
• Changing the data type of a column (only if the column is empty for all rows)

ALTER TABLE Example 1

Adding a column to a table:

ALTER TABLE t1(pole1 char(10));

ALTER TABLE Example 2

Resizing a table column:

ALTER TABLE t1 MODIFY(pole1 char(20));

ALTER TABLE Example 3

Removing a table column:

ALTER TABLE t1 DROP COLUMN pole1;

You can use the ALTER TABLE command to change the table name without actually moving it physical information in the database:

ALTER TABLE t1 RENAME TO t2;

A similar operation can be performed using the RENAME command:

RENAME t1 TO t2;

Integrity constraints on database columns and tables can be changed, as well as prohibited, allowed, and deleted. This gives the developer the ability to create, modify, and delete business rules that restrict data. Let's consider adding restrictions to the database. The simplicity or complexity of this process depends on certain circumstances. If you cannot create a constraint with the database, the easiest way is to add it before entering the data:

ALTER TABLE Example 4

Modifying the table structure

ALTER TABLE t1 MODIFY(field1 NOT NULL);

CREATE TABLE t2

(field1 CHAR(10) PRIMARY KEY);

ALTER TABLE t1 ADD

(CONSTRAINT fk_t1 FOREIGN KEY(field1)

REFERENCES t2(field1));

ALTER TABLE t1 ADD (UNIQUE(p_name));

ALTER TABLE t1 ADD(p_size CHAR(4) CHECK

(p_size IN ('P','S','M','L','XL','XXL','XXXL')));

The first command above uses the MODIFY clause to add a NOT NULL constraint on a column, and the ADD clause to add all other types of table integrity constraints. The column for which the constraint is added must already exist in the database table; otherwise, the constraint will not be created.

ALTER TABLE Example 5

To add integrity constraints, you can omit the name of the constraint you are creating by using the keyword CONSTRAINT. In this case the command will look like this:

ALTER TABLE t1 ADD FOREIGN KEY (pole1) REFERENCES t2 (pole1);

There are a number of conditions for creating restrictions:

• Primary Keys: Columns cannot contain NULL values ​​and all values ​​must be unique.
• Foreign keys: Those columns of other tables that are referenced must contain values ​​that match all the values ​​of the referencing columns, or the values ​​of the latter must be NULL values.
• UNIQUE Constraints: All column values ​​must be unique or NULL values.
• CHECK Constraints: The new constraint will only apply to data added or modified after it is created.
• NOT NULL: NULL values ​​are not allowed in columns.

Restrictions can be allowed or denied. An allowed constraint performs its functions by implementing business rules in relation to the data entered into the table, and a prohibited constraint is transferred to the category of inoperative, as if it were deleted, and its rules are not implemented.

ALTER TABLE Example 6

Prohibition of restrictions:

ALTER TABLE t1 DISABLE PRIMARY KEY;
ALTER TABLE t1 DISABLE UNIQUE(p_name);

ALTER TABLE Example 7

In some cases, disabling a primary key on which foreign keys depend can cause problems, for example:

ALTER TABLE t2 DISABLE PRIMARY KEY;

Error at line 1: Cannot disable constraint…. – dependencies exist(it is impossible to disable a constraint - there are dependencies)

To delete a primary key if there are dependent keys foreign keys in the ALTER TABLE DISABLE command<ограничения>CASCADE construction is required:

ALTER TABLE t2 DISABLE PRIMARY KEY CASCADE;

ALTER TABLE Example 8

The prohibited restriction is resolved as follows:

ALTER TABLE t1 ENABLE PRIMARY KEY;

ALTER TABLE t1 ENABLE UNIQUE(p_name);

You can only allow those restrictions that were set previously, and in this moment prohibited.

A constraint whose creation process has failed will not exist in a prohibited form, awaiting resolution after the error is resolved. Typically, the owner of the table, or someone who has been granted the appropriate rights, can remove the constraint:

ALTER TABLE t1 DROP UNIQUE(p_name);

The following levels of verification restrictions can be distinguished:

• attribute (column) level,
• tuple (row) level,
• relation level (table).

A column level constraint only checks the value of one individual column, in other words, the constraint of this type there is a reference to only one column of the table whose definition contains this constraint. To give an example of such a limitation, let’s return to the “Computer company” diagram. In the Product table, the type column can contain one of three values. We can prevent any other information from being entered into this column by using a constraint like this:

CHECK (type IN ("printer" , "pc" , "laptop" ) )

Let's take a step back to look at the ALTER TABLE statement, which allows us to change the structure of a table without having to recreate it every time. This is all the more important because changing the structure may be required when the table already contains data.

You can use the ALTER TABLE statement to add or remove columns, default values, and constraints.

IN currently We are interested in adding a constraint on the type column, so first we will provide the operator syntax for adding a constraint:

ALTER TABLE

ADD CONSTRAINT ;

Let's now add our constraint and check how it works.

ALTER TABLE Product

ADD CONSTRAINT chk_type CHECK (type IN ("pc" , "laptop" , "printer" ) ) ;

To make sure that the constraint works as we expect, let's try adding a new type of model:

INSERT INTO Product VALUES ("A" , 1122 , "notebook" ) ;

As expected, we will receive an error message in response:

The INSERT statement conflicted with the CHECK constraint "chk_type". The conflict occurred in database "learn", table "dbo.product", column "type". The statement has been terminated.

(INSERT statement conflicted with CHECK constraint "chk_type". The conflict occurred in database "learn", table "dbo.product", column "type". This statement was aborted.)

As you might guess, a row-level constraint contains references to multiple columns. In this case, the constraint is checked for each modified row separately. A row can be added (or changed) if the constraint is not violated.

As an example, let's ban manufacturer Z from making anything other than printers.

ALTER TABLE Product

ADD constraint chk_maker_Z CHECK ((maker="Z" AND type= "printer" ) OR maker "Z" ) ;

So, the constraint checks that the model in the Product table must be a printer from manufacturer Z (maker="Z" and type="printer") or any other manufacturer (but not Z).

If we try to add PC model of manufacturer Z,

In that textbook you will learn how to use MySQL ALTER TABLE statement to add a column, change a column, delete a column, rename a column or rename a table (with syntax and examples).

Description

MySQL ALTER TABLE statement used to add, change or delete columns in a table. The MySQL ALTER TABLE statement is also used to rename a table.

Add a column to a table

Syntax

The syntax for adding a column to a MySQL table (using the ALTER TABLE statement) is:

ALTER TABLE table_name

FIRST | AFTER column_name is optional. It tells MySQL where the column is created in the table. If this parameter is not specified, the new column will be added to the end of the table.

Example

Let's look at an example that shows how to add a column to a MySQL table using the ALTER TABLE statement.
For example:

MySQL

ALTER TABLE contacts ADD last_name varchar(40) NOT NULL AFTER contact_id;

ALTER TABLE contacts AFTER contact_id;

This MySQL ALTER TABLE example will add a column named last_name to the contacts table. It will be created as a NOT NULL column and will appear in the table after the contact_id field.

Add multiple columns to a table

Syntax

The syntax for adding multiple columns to a MySQL table (using the ALTER TABLE statement) is:

ALTER TABLE table_name
ADD new_column_name column_definition

ADD new_column_name column_definition
[ FIRST | AFTER column_name ],
…
;

table_name — the name of the table to change.
new_column_name - the name of the new column to add to the table.
column_definition - data type and definition of the column (NULL or NOT NULL, etc.).
FIRST | AFTER column_name is optional. It tells MySQL where the column is created in the table. If this parameter is not specified, the new column will be added to the end of the table.

Example

Let's look at an example that shows how to add multiple columns to a MySQL table using the ALTER TABLE statement.
For example:

MySQL

ALTER TABLE contacts ADD last_name varchar(40) NOT NULL AFTER contact_id, ADD first_name varchar(35) NULL AFTER last_name;

ALTER TABLE contacts ADD last_namevarchar (40)NOT NULL AFTER contact_id, ADD first_namevarchar(35)NULL AFTER last_name;

This ALTER TABLE example will add two columns to the contacts table, last_name and first_name .

The last_name field will be created as a varchar(40) NOT NULL column and will appear in the contacts table after the contact_id column. The first_name column will be created as a NULL varchar(35) column and will appear in the table after the last_name column.

Change a column in a table

Syntax

The syntax to change a column in a MySQL table (using the ALTER TABLE statement) is:

ALTER TABLE table_name

[ FIRST | AFTER column_name ];

table_name — the name of the table to change.

Example

Let's look at an example that shows how to change a column in a MySQL table using the ALTER TABLE statement.
For example:

MySQL

ALTER TABLE contacts MODIFY last_name varchar(50) NULL;

ALTER TABLE contacts MODIFY last_namevarchar (50)NULL ;

This ALTER TABLE example will change the column named last_name to be a varchar(50) data type and set the column's values ​​to NULL.

Change multiple columns in a table

Syntax

The syntax for changing multiple columns in a MySQL table (using the ALTER TABLE statement) is:

ALTER TABLE table_name
MODIFY column_name column_definition
[ FIRST | AFTER column_name ],
MODIFY column_name column_definition
[ FIRST | AFTER column_name ],
…
;

table_name — the name of the table to change.
column_name is the name of the column to change in the table.
column_definition - modified data type and definition of the column (NULL or NOT NULL, etc.).
FIRST | AFTER column_name is optional. It tells MySQL where in the table to place a column if you want to change its position.

Example

Let's look at an example that shows how to change multiple columns in a MySQL table using the ALTER TABLE statement.

MySQL

ALTER TABLE contacts MODIFY last_name varchar(55) NULL AFTER contact_type, MODIFY first_name varchar(30) NOT NULL;

ALTER TABLE contacts MODIFY last_namevarchar (55)NULL AFTER contact_type, MODIFY first_namevarchar (30)NOT NULL ;

This ALTER TABLE example will change two columns in the contacts table - last_name and first_name .
The last_name field will be changed to a NULL varchar(55) column and will appear in the table after the contact_type column. The first_name column will be changed to a varchar(30) NOT NULL column (and will not change the position in the contacts table definition since FIRST | AFTER is not specified).

Removing a column from a table

Syntax

The syntax to remove a column from a table in MySQL (using the ALTER TABLE statement) is:
For example:

ALTER TABLE table_name
DROP COLUMN column_name;

table_name — the name of the table to change.
column_name is the name of the column to be removed from the table.

Example

Let's look at an example that shows how to delete a column from a table in MySQL using the ALTER TABLE statement.
For example:

MySQL

ALTER TABLE contacts DROP COLUMN contact_type;

ALTER TABLE tbl_name alter_spec [, alter_spec ...] alter_specification: ADD create_definition or ADD (create_definition, create_definition,...) or ADD INDEX (index_col_name,...) or ADD PRIMARY KEY (index_col_name,...) or ADD UNIQUE (index_col_name,...) or ADD FULLTEXT (index_col_name,...) or ADD FOREIGN KEY index_name (index_col_name,...) or ALTER col_name (SET DEFAULT literal | DROP DEFAULT) or CHANGE old_col_name create_definition or MODIFY create_definition or DROP col_name or DROP PRIMARY KEY or DROP INDEX index_name or DISABLE KEYS or ENABLE KEYS or RENAME new_tbl_name or ORDER BY col or table_options

The ALTER TABLE statement provides the ability to change the structure of an existing table. For example, you can add or remove columns, create or destroy indexes, or rename columns or the table itself. You can also change the comment for the table and its type. See section.

If an ALTER TABLE statement is used to change a column's type definition, but DESCRIBE tbl_name indicates that the column has not changed, then MySQL may be ignoring the modification for one of the reasons described in section 6.5.3.1 Silent Changes to Column Definitions. For example, if you try to change a VARCHAR column to CHAR, MySQL will continue to use VARCHAR if the table in question contains other variable-length columns.

The ALTER TABLE statement creates a temporary copy of the original table at runtime. The required change is performed on the copy, then the original table is deleted and the new table is renamed. This is done so that all updates except failed ones are automatically included in the new table. While ALTER TABLE is running, the source table is readable by other clients. Update and write operations on this table are suspended until the new table is ready.

It should be noted that when using any other option for ALTER TABLE other than RENAME , MySQL will always create a temporary table, even if the data does not strictly need to be copied (for example, when a column name changes). We plan to fix this in the future, however, since ALTER TABLE is not executed very often, we (the MySQL developers) do not consider this a priority. For MyISAM tables, you can increase the speed of rebuilding the index portion (which is the slowest part of the table recovery process) by setting the myisam_sort_buffer_size variable to a large enough value.

• To use the ALTER TABLE statement, you must have ALTER, INSERT, and CREATE privileges on the table.
• The IGNORE option is a MySQL extension to ANSI SQL92. It controls how ALTER TABLE works when there are duplicate unique keys in the new table. If the IGNORE option is not specified, then the process for this copy is aborted and rolled back. If IGNORE is specified, then for rows with duplicate unique keys, only the first row is used and the rest are removed.
• You can run multiple ADD, ALTER, DROP, and CHANGE statements in a single ALTER TABLE command. This is an extension of MySQL to ANSI SQL92, where only one of the expressions mentioned in a single ALTER TABLE command is allowed.
• The CHANGE col_name , DROP col_name , and DROP INDEX options are also MySQL extensions to ANSI SQL92.
• The MODIFY option is an Oracle extension to the ALTER TABLE command.
• The optional word COLUMN represents ``white noise'' and can be omitted.
• When using ALTER TABLE table_name RENAME TO new_name without any other options, MySQL simply renames the files corresponding to the given table. In this case, there is no need to create a temporary table. See section 6.5.5 RENAME TABL E Statement Syntax.
• The create_definition statement uses the same syntax for ADD and CHANGE as for CREATE TABLE. Note that this syntax includes the column name, not just its type. See section 6.5.3 CREATE TABLE statement syntax.
• A column can be renamed using the CHANGE column_name create_definition statement. To do this, you must specify the old and new column names and its current type. For example, to rename an INTEGER column from a to b , you could do the following: mysql> ALTER TABLE t1 CHANGE a b INTEGER; If you change a column's type but not its name, the CHANGE expression syntax still requires both column names to be specified, even if they are the same. For example: mysql> ALTER TABLE t1 CHANGE b b BIGINT NOT NULL; However, as of MySQL 3.22.16a, you can also use a MODIFY statement to change the type of a column without renaming it: mysql> ALTER TABLE t1 MODIFY b BIGINT NOT NULL;
• When using CHANGE or MODIFY to reduce the length of a column that is part of an index (for example, an index on the first 10 characters of a VARCHAR column), you cannot make the column shorter than the number of characters indexed.
• When you change a column's type using CHANGE or MODIFY, MySQL attempts to convert the data to the new type as gracefully as possible.
• In MySQL 3.22 and later, you can use FIRST or ADD ... AFTER column_name to add a column at a given position within a table row. By default, the column is added at the end. As of MySQL 4.0.1, you can also use keywords FIRST and AFTER in the CHANGE or MODIFY options.
• The ALTER COLUMN option sets a new default value for a column or removes an old one. If the old default is removed and the column is NULLable, then the new default will be NULL. If the column cannot be NULL, then MySQL assigns a default value as described in section 6.5.3 CREATE TABLE Statement Syntax.
• The DROP INDEX option removes an index. It is an extension of MySQL to ANSI SQL92. See section 6.5.8 DROP INDEX Statement Syntax.
• If columns are removed from a table, those columns are also removed from any index that they are part of. If all the columns that make up an index are deleted, then that index is also deleted.
• If a table contains only one column, then that column cannot be deleted. Instead, you can drop this table using the DROP TABLE command.
• The DROP PRIMARY KEY option removes the primary index. If such an index does not exist on a given table, then the first UNIQUE index on that table is removed. (MySQL marks the first unique UNIQUE key as the PRIMARY KEY if no other PRIMARY KEY has been explicitly specified.) When you add a UNIQUE INDEX or PRIMARY KEY to a table, it is stored before any other non-unique keys so that duplicate keys can be identified as early as possible.
• The ORDER BY option allows you to create new table with lines placed in a given order. Please note that the created table will not retain this row order after insert and delete operations. In some cases, this feature can make sorting operations easier in MySQL if the table has a column arrangement that you would like to have in the future. This option is mainly useful if you know in advance a specific order in which rows will be predominantly requested. Using this option after significant table transformations allows for better performance.
• When using the ALTER TABLE command on MyISAM tables, all non-unique indexes are created in a separate batch (similar to REPAIR). This will make the ALTER TABLE command faster when there are multiple indexes.
• As of MySQL 4.0, the above feature can be enabled explicitly. The ALTER TABLE ... DISABLE KEYS command blocks MySQL from updating non-unique indexes on MyISAM tables. You can then use the ALTER TABLE ... ENABLE KEYS command to recreate the missing indexes. Since MySQL does this using a special algorithm that is much faster than inserting keys one by one, locking keys can provide significant speedup. large areas inserts.
• By using the C API mysql_info() function, you can determine how many records were copied, and also (when using IGNORE) how many records were removed due to duplicate unique key values.
• The FOREIGN KEY , CHECK and REFERENCES statements don't actually do anything. They are introduced only for compatibility reasons, to make it easier to port code from other SQL servers and running applications that create tables with links. See section 1.9.4 Differences between MySQL and ANSI SQL92.

The following examples show some uses of the ALTER TABLE command. The example starts with table t1, which is created as follows:

Mysql> CREATE TABLE t1 (a INTEGER,b CHAR(10));

To rename a table from t1 to t2:

Mysql> ALTER TABLE t1 RENAME t2;

To change the type of a column from INTEGER to TINYINT NOT NULL (keeping the name the same) and change the type of column b from CHAR(10) to CHAR(20) and rename it from b to c:

Mysql> ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);

To add a new TIMESTAMP column named d:

Mysql> ALTER TABLE t2 ADD d TIMESTAMP;

To add an index to column d and make column a the primary key:

Mysql> ALTER TABLE t2 ADD INDEX (d), ADD PRIMARY KEY (a);

To delete column c:

Mysql> ALTER TABLE t2 DROP COLUMN c;

To add a new AUTO_INCREMENT numeric column named c:

Mysql> ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD INDEX (c);

Note that column c is indexed because AUTO_INCREMENT columns must be indexed, in addition, column c is declared NOT NULL because indexed columns cannot be NULL .

When you add an AUTO_INCREMENT column, the values ​​in that column are automatically populated with sequential numbers (as records are added). The first sequence number can be set by issuing the SET INSERT_ID=# command before ALTER TABLE or using the AUTO_INCREMENT = # table option. See section 5.5.6 SET Command Syntax.

If the AUTO_INCREMENT column for MyISAM tables is not changed, then the sequence number remains the same. If you remove an AUTO_INCREMENT column and then add another AUTO_INCREMENT column, the numbers will start again at 1 .

Table manipulation is one of the most common activities that database administrators and programmers perform when working with database objects. This section details how to create and modify tables.

The ANSI standard is something of a lowest common denominator for all manufacturers, although not every feature of the standard version of the CREATE TABLE and ALTER TABLE statements is implemented by every manufacturer. However, the ANSI standard is a basic form that can be used on all platforms.

In turn, the platforms offer various extensions and additions to the ANSI standard CREATE TABLE and ALTER TABLE statements.

Typically, you need to think carefully about the design of the table and how to create it. This process is called database design. The process of analyzing a table's relationships with its own data and other tables in the database is called normalization. We recommend that programmers and database administrators study design and normalization principles before using CREATE DATABASE commands.

SQL 2003 syntax

When you run the SQL 2003 CREATE TABLE statement, a permanent or temporary table is created in the database. The syntax is as follows.

CREATE [(LOCAL TEMPORARY) GLOBAL TEMPORARY)] TABLE table_name (column_name data_type attributes [, ...]) | [column_name WITH OPTIONS options] |

| (SYSTEM GENERATED | USER GENERATED | DERIVED)] [, ...]] [table_definition]] table_name data_type attributes] |

column_name SET DEFAULT default_value] |

column_name DROP DEFAULT] |

column_name ADD SCOPE table_name |

column_name DROP SCOPE (RESTRICT | CASCADE)] |

column_name (RESTRICT | CASCADE)] |

|

Keywords

TEMPORARY A permanent or TEMPORARY table with local (LOCAL) or global (GLOBAL) scope is declared. Local temporary tables are accessible only from the session that created them, and they are automatically deleted when the session that created them ends. Global temporary tables are accessible from all active sessions, but they are automatically deleted when the session that created them terminates. Do not qualify temporary table names with a schema name.(column_name data_type attributes [,]) A list is defined that lists one or more columns, their data types, and additional attributes, such as nullability, separated by commas. Each table declaration must include at least one column, for which you can specify: column_name

Associates the specified constraint attributes with the column. You can specify multiple attributes for a single column named column_name. No commas required. Typical ANSI attributes include the following.

NOT NULL

The column does not allow NULL values ​​(or allows if the NOT NULL clause is omitted). Any INSERT and UPDATE statements that attempt to place a NULL value in a column with the NOT NULL attribute will fail and will be rolled back.

DEFAULT expression

The column will use the value of the expression if the INSERT or UPDATE statement does not supply any value. The expression must be valid for the column's data type; for example, you cannot use any alphabetic characters in a column of type INTEGER. The expression can be a string or a numeric literal, but you can also specify a user-defined or system function. The SQL 2003 standard allows the following to be used in the DEFAULT clause: system functions: NULL, USER, CURRENTJJSER, SESSION_USER, SYSTEMJJSER, CURRENT_PATH, CURRENT_D AND THOSE, CURRENTJIME, LOCALTIME, CURRENTJIMESTAMP, LOCALTJMESTAMP, ARRAY or ARRAY.

COLLATE collation_name

The collation used is determined, that is, the sort order in the corresponding column. The name of the mapping depends on the platform. If a collation name is not specified, the default collation is the character set used in the column. REFERENCES ARE CHECKED This parameter determines whether references in the REF column defined with the scope option will be checked. Additional offer ON DELETE determines whether the values ​​in the records referenced by the deleted record will be set to NULL or whether the operation will be restricted.

CONSTRAINT constraint name [constraint_type [constraint]]

The parameter assigns the column a constraint and optionally a constraint name. Types of constraints are discussed in Chapter 2. Because a constraint is associated with a specific column, the constraint declaration assumes that that column will be the only one in the constraint. Once the table is created, the constraint is considered a table-level constraint.

column_name

A column is defined with special options, such as a scope option, a default value option, a column level constraint, or a COLLATE clause. In many implementations, the WITH OPTIONS clause is limited to creating typed tables.

LIKE table_name

The new table is created with the same column definitions as in existing table table_name.

REFIS column_name (SYSTEM GENERATED | USER GENERATED DERIVED]

Defines an object identifier (OID) column in typed tables. An object identifier is required for a table that is the root of a table hierarchy. According to this parameter, the REF column can be generated automatically by the system (SYSTEM GENERATED), manually specified by the user when entering a row (USER GENERATED), or created from another REF column (DERIVED). The parameter requires that the REFERENCES attribute be included in the column_name column.

CONSTRAINT constraint type [constraint name] [, …]

A table is assigned one or more constraints. This setting is noticeably different from column-level restrictions because column-level restrictions are assumed to apply only to the column to which they are associated. For table-level constraints, it is possible to associate multiple columns with a constraint. For example, in a sales table, you might need to declare a unique constraint on the concatenated key store_id, order_id, and order_date. This can only be done using a table level constraint. For a detailed discussion of limitations, see Chapter 2.

OF type_name [table_definition]

The table is declared to be based on a ready-made user-defined type. In this situation, the table can have only one column for each structured type attribute, plus an additional column defined in the REF IS clause. The REF data type is described in detail in the CREATE/ALTER TYPE statement section. This clause is incompatible with the LIKE table_name clause. Where:

UNDER supertable [definition/tables]

Declares an immediate supertable for the current table in the same schema (if it exists). Optionally, you can specify a full table_definition for the supertable, filling it with columns, constraints, and the like.

ON COMMIT (PRESERVE ROWS DELETE ROWS]

The ON COMMIT PRESERVE ROWS clause preserves temporary table data rows when a COMMIT statement is executed. The ON COMMIT DELETE RO WS clause deletes all rows of data in a temporary table when a COMMIT statement is executed.

ADD column_name data_type attributes

A column with the appropriate data type and attributes is added to the table.

ALTER column_name SET DEFAULT default_value

Adds a default value to the column (if it does not exist) or changes an existing value.

ALTER column_name DROP DEFAULT

The default value is completely removed from the specified column.

ALTER column_name ADD SCOPE table_name

A scope is added to the specified column. A scope is a reference to a custom data type.

ALTER column_name DROP SCOPE

The scope is removed from the specified column. The RESTRICT and CASCADE clauses are explained at the end of this list.

DROP COLUMN column_name

The specified column is removed from the table. The RESTRICT and CASCADE clauses are explained at the end of this list.

ADD table_constraint

A constraint with the specified name and characteristics is added to the table.

DROP CONSTRAINT constraint name

The existing constraint is removed from the table.

RESTRICT

When this clause is specified, the RDBMS cancels the command if it finds objects in the database that depend on the object.

When this clause is specified, the RDBMS deletes all other objects that depend on this object.