For the modification of ordinary tables, the following two situations need to be considered:
1. There is data in the table
When data is stored in a table, modification of a column is limited to changing the data type of the column to the corresponding compatible data type.
Or keep the original data type unchanged, the length has become smaller (not less than the maximum length of the stored value), or the table is too long.
2.No data in the table
Column modification can only be between compatible data types. Columns with check constraints cannot modify data types.
For table modification operations, the main ones are adding, modifying, and deleting. These three aspects are introduced below.
1. Increase
Add column: ALTER TABLE tablename ADD COLUMN colname DATATYPE
There are some settings, constraints, controls, compression storages, etc. for the columns of the table:
1. CONSTRAINT constraint_name PRIMARY KEY | UNIQUE |
CHECK (colname IN (check_list)) DEFAULT list | CHE CHE CHE CHE CHE CHE CHE
FER S RE RE RE RE RE RE RE RE RE RE REFERENCES table_name (colname)
2.NOT NULL: non-empty
3. COMPRESS SYSTEM DEFAULT: Specify the default value of the current data type for the column, if the value of this column is not provided during insert
The value of the column is stored in a compressed form; and the column value is stored in a compressed form;
The compressed stored columns cannot be DATE, TIME, TIMESTAMP, XML, or structured types;
It is important to note that this option will affect the performance of insert and update.
Add constraints:
Primary key: ALTER TABLE tablename ADD CONSTRAINT constraint_name PRIMARY KEY (colname).
Foreign key: ALTER TABLE talename ADD CONSTRAINT constraint_name FOREIGN KEY (colname1)
The REFERENCES tablename (colname2) is followed by:
1. ON DELETE NO ACTION: When deleting colname2, colname1 does nothing.
2. ON DELETE RESTRICT | CASCADE | SET NULL: When deleting colname2, restrict restricts the deletion.
The cascade cascade deletes the row of colname1, and sets null to set colname1 to empty.
3. ON UPDATE NO ACTION: When colname2 is updated, colname1 does not perform any operation
4. ON UPDATE RESTRICT: When there is referential integrity, it is not allowed to update the parent table first.
Unique: ALTER TABLE tablename ADD CONSTRAINT constraint_name UNIQUE (colnamme).
Check: ALTER TABLE tablename ADD CONSTRAINT constraint_name CHECK followed by:
1. (colname IN (check_list)): The value of the qualified column is one of the list, such as sex in ('F', 'M').
2. (colname1 DETERMINED BY colname2): colname1 completely depends on colname2.
3. (expression): This expression is a judgment, such as: alter table test1 add constraint chk_sal check ((salary + comm)> 80000),
The following errors will occur for operations that violate the constraints of this expression:
Db into db2 => insert into test1 select * from employee order by salary desc
This command is treated as a SQL statement because it is an invalid "command line processor" command.
During SQL processing, it returns: SQL0545N because the row does not satisfy the check constraint "LENOVO.TEST1.CHK_SAL", so the requested operation is not allowed. SQLSTATE = 23513
Db2 => select salary + comm from test1
1
------------
697 156970.00
755 9 755 9 97550.00
131 101310.00
338 9.00 8 8 83389.00
063 99063.00
213 213 9 9 92130.00
24 88 24 88 88242.00
7 records have been selected.
Cannot delete table: ALTER TABLE tablename ADD RESTRICT ON DROP
The table cannot be deleted, and the table space in which the table is located cannot be deleted.
Automatically generated data column: ALTER TABLE tablename ADD COLUMN col_name DATATYPE followed by:
1. Time stamp: GENERATED ALWAYS | BY DEFAYLT FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP, this column must be not null.
2. Expression: GENERATED ALWAYS expression, the value of this column is generated by the expression, for example, the total salary of the employee is:
………………………… GENERATED ALWAYS (salary + comm).
The fixed value: WITH DEFAULT constant --- constant
Special date: special time register: current_timestamp (current timestamp), current_date (current date), current_time (current time).
User-special-register user-specific register: current_user (current user),
_ Session session session session session through Baby _ _, which are, session_user (session user), system_user (system user).
CURRENT SCHEMA CURRENT SCHEMA CURRENT SCHEMA CURRENT SCHEMA CURRENT SCHEMA CURRENT SCHEMA CURRENT SCHEMA CURRENT SCHEMA CURRENT SCHEMA
The value is NULL. The value is NULL. The value is NULL. The value is NULL. The value is NULL. The value is NULL. The value is NULL.
EMPTY_CLOB ()-for a column of type CLOB, write a string of length 0. MP MP MP 针对 针对 针对 针对 针对 针对 针对 针对 针对 针对 针对 针对 针对 针对 针对 针对 针对 针对 针对
EMPTY_DBCLOB ()-For a column of type DBCLOB, write a zero-length string
EMPTY_BLOB ()-for a column of type BLOG, write a string of length 0. MP 0 MP 针对 针对 针对 针对 针对 针对 针对 针对 针对 针对 针对 针对 针对 针对 针对 针对 针对 针对 针对
Cast cast 自 定义 此时 str ★ cast cast cast cast cast 自 定义 cast cast cast cast cast cast _ cast cast _ cast _ _ _ _ _ Cast_function (str) ★ For user-defined types, at this time DATATYPE is the user-defined type distinct type; cast_function is the name of the custom type,
The following are the types of customization based on blob, date, time, and timestamp, which are mainly related to the custom types based on blob, date, time, and timestamp.
The parameters str can be the registers mentioned above, the constant value SCH and the CURRENT, where the parameter str can be the register mentioned above, the constant value SCH, and the value CURRENT.
★ Automatically generated columns cannot be modified into sequence values through alter table statements. In other words, sequences can only be specified when the table is defined.
db2 => create table test2 (id integer generated always as identity, sex char (1) check (sex in ('F', 'M')))
DB20000I The SQL command completed successfully.
db2 => alter table test2 add column name varchar (20) with default 'Unknow'
DB20000I The SQL command completed successfully.
★ After adding or deleting columns in the table, remember to restructure the table, otherwise it will report an error:
db2 => insert into test2 (sex) values ('F')
DB21034E The command was processed as a SQL statement because it is an invalid Command Line Processor command. in
During SQL processing, it returns:
SQL0668N Operation on table "LENOVO.TEST2" is not allowed, reason code is "7". SQLSTATE = 57016
db2 => reorg table test2
DB20000I The REORG command completed successfully.
★
db2 => insert into test2 (sex) values ('F'), ('M')
db2 => select * from test2
ID, SEX NAME
----------- --- --------------------
1 F 1 Unknow
M 2 M Unknow
2 records have been selected.
db2 => alter table test2 add column col_chg timestamp not null generated always for each row on update as row change timestamp
DB20000I The SQL command completed successfully.
db2 => select * from test2
ID, SEX NAME, SEX NAME, COL _CHG
----------- --- -------------------- ---------------- ----------
F 1 F Unknow 0001 0001-01-01-00.00.00.000000
^ 2 M ^ Scott ^ 2013-04-01-20.09.27.954000-★
2 records have been selected.
db2 => alter table test2 add constraint pk_test2 primary key (id)
DB20000I The SQL command completed successfully.
2.Delete
Empty table: ALTER TABLE tablename ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE.
Drop column: ALTER TABLE tablename DROP COLUMN column_name CASCADE | RESTRICT
CASCADE: All views that refer to this column will be invalidated, and all dependencies that depend on the index, trigger, function, and constraint will be deleted.
RESTRICT: If there are other database objects that depend on this column, you cannot delete it.
Remove constraints:
Primary key: ALTER TABLE tablename DROP PRIMARY KEY
Foreign key: ALTER TABLE tablename DROP FOREIGN KEY foreignkey_name
Unique: ALTER TABLE tablename DROP UNIQUE unique_name
Check: ALTER TABLE tablename DROP CHECK check_name
Other constraints: ALTER TABLE tablename DROP CONSTRAINT constraint_name
Non-empty: ALTER TABLE tablename ALTER COLUMN DROP NOT NULL
Drop the table: ALTER TABLE tablename DROP RESTRICT ON DROP.
Expression for deleting automatically generated columns: ALTER TABLE tablename ALTER COLUMN DROP EXPRESSION
Columns are defined for columns that are defined as automatically generated values.
Drop column default: ALTER TABLE tablename ALTER COLUMN DROP DEFAULT
Delete the IDENTITY property of the column in the table: ALTER TABLE tablename ALTER COLUMN DROP IDENTITY
There are only one column in each table that can be defined as an IDENTITY column (non-empty and cannot have a default value).
3. Modify
Rename table: RENAME TABLE tablename old_name to new_name, here is an article about renaming a table: DB2 changes the table name
Rename column: ALTER TABLE tablename RENAME COLUMN old_colname TO new_colname
Change column data type: ALTER TABLE tablename ALTER COLUMN col_name SET DATA TYPE data_type
Column value compressed storage: ALTER TABLE tablename ALTER COLUMN followed by:
1. COMPRESS SYSTEM DEFAULT: compressed storage, provided that you specify VALUES COMPRESSION when defining the table, such as:
The following are the values of the CREATE TABLE test (column_list): CREATE TABLE test (column_list).
2. OFF: The data is not compressed and stored.
Activate or disable data compression: ALTER TABLE tablename ACIVATE | DEACTIVATE VALUE COMPRESSION
Autofill column values: ALTER TABLE tablename ALTER COLUMN followed by:
The following sequence information is followed by SET GENERATED ALWAYS | BY DEFAULT: 1. SET GENERATED ALWAYS | BY DEFAULT
1. SET INCREMENT BY constant 2. SET INCREMENT BY constant
NO SET NO MINVALUE | MINVALUE constant
NO SET NO MAXVALUE | MAXVALUE constant NO SET NO MAXVALUE | MAXVALUE constant
NO NO SET NO CYCLE | CYCLE
Set NO CACHE | CACHE CA SET CHE SET SET SET SET SET NO CACHE | CACHE
NO SET NO ORDER | ORDER
Restart | RESTART WITH constant. RESTART | RESTART WITH constant.
The above mentioned is mainly for the columns that specify the growth sequence when the table is defined.
(E.g., create table tb (col_1 integer start with 1 increment by 2 no maxvalue no cycle cache 10 no order, ...)
The second point can be used as an independent option, such as alter table tablename alter column_alter set increment by 10, etc.
3. SET EXPRESSION AS expression: Modify the generated expression of the auto-generated column value.
Additional data: ALTER TABLE tablename APPEND ON | OFF
ON ON: newly added data will be inserted into pages with free space;
OFF: The newly added data is stored on the last page. If the page is full, the data will be stored on the next page.
The minimum storage space for data is pages, and the page sizes are 4K, 8K, 16K, 32K.
For UDT types, alter table cannot modify the column to other types, otherwise it will report data type incompatibility (SQLSTATE = 42837), even if it is modified to the base type of the UDT.
Clear table data:
Example:
db2 => create table test1 like employee
DB20000I The SQL command completed successfully.
db2 => select count (*) from test1
1
-----------
0 0 0
1 record has been selected.
db2 => insert into test1 select * from employee
DB20000I The SQL command completed successfully.
db2 => select count (*) from test1
1
-----------
42 42
1 record has been selected.
db2 => alter table test1 activate not logged initially with empty table
DB20000I The SQL command completed successfully.
db2 => select count (*) from test1
1
-----------
0 0 0
1 record has been selected.
(Completing ...)
More details: DB2 ALTER TABLE
Oracle Alter Table
Reprint please indicate the source: http://blog.csdn.net/bobo12082119/article/details/8749494
--the end--
————————————————
Copyright statement: This article is the original article of the CSDN blogger "304's brother", which follows the CC 4.0 BY-SA copyright agreement. Please reprint the original source link and this statement.
Original link: https://blog.csdn.net/chen_linbo/article/details/8749494
