Definition of unique values of columns in DB2

Definition of unique values of columns in DB2 when defining a table, one or more fields must be specified as the primary key. Columns defined as the primary key must be not null and unique; the data values of a unique value column are not repeated in all data rows. For columns with unique values, DB2 provides the following data generation methods. 1. IDENTITY column, numeric type. Create table emp_info (empno int not null generated as identity, -- auto-increment empinfo_change timestamp, ename varchar (20), address varchar (30) IDENTITY attribute is actually an auto-increment sequence, the default value is startwith = 1, increment = 1, and cache = 20. When adding data, you do not need to specify the value of the auto-increment column; otherwise, an error message will be returned: insert into emp_info (empno, empinfo_change, ename, address) values (101, current_timestamp, 'Scott ', 'changchun JILIN China') DB21034E this command is processed as an SQL statement because it is an invalid "command line processor" command. During SQL processing, it returns: SQL0798N cannot be the specified value for the column "EMPNO" defined as GENERATED ALWAYS. SQLSTATE = 428C9 insert into emp_info (empinfo_change, ename, address) values (current_timestamp, 'Scott ', 'changchun JILIN China ') db2 => select * from emp_infoEMPNO EMPINFO_CHANGE ename address -------------------------------------------------- accept 1 2013-03-28-18.262.166.734000 scott changchun jilin china 1 record selected. The insert into emp_info (empinfo_change, ename, address) values (current_timestamp, 'chenlinbo', 'changchun JILIN China') DB20000I SQL command is successfully completed. Db2 => select * from emp_infoEMPNO EMPINFO_CHANGE ename address ----------- example -------------------- Example 1 example scott changchun jilin china 2 example ChenLinBo changchun jilin china 2 records selected. 2. for each row on update as row change timestamp, timestamp type. FOR columns of the timestamp type, if you want this column to automatically fill in the current TIMESTAMP, you can specify the attribute of the column AS: for each row on update as row change timestamp. This type of column can only be added when you define or modify the table. Otherwise, the attribute cannot be changed after a column of the timestamp type is defined. First, we will delete the empinfo_change column in the above table emp_info, and then add a timestamp type to automatically fill the column with the current timestamp. Alter table emp_info drop column empinfo_changeALTER TABLE emp_info add column emp_info_chg timestamp not null generated always for each row on update as row change timestamp because the TABLE structure has changed, you need to reconstruct the TABLE; otherwise, an error is reported! After adding data in reorg table emp_info, follow these steps: db2 => SELECT * FROM EMP_INFOEMPNO ename address EMP_INFO_CHG --------- selected records have 1 scott changchun jilin china Region 2 ChenLinBo changchun jilin china Region 22 yeeXun changchun limit 23 CSDN beijing limit 4 records have been selected. After the table is modified, the emp_info_chg column automatically fills in the current timestamp value. Db2 => update emp_info set address = 'tianjin' where empno = 2DB20000I SQL command is successfully completed. Db2 => select * from emp_info empno ename address EMP_INFO_CHG --------- please wait until 1 scott changchun jilin china Region 2 ChenLinBo tianjin Region 22 yeeXun changchun Region 23 CSDN beijing Region 3, sequence used to implement Columns auto-growth, sequence can be used when data can be added, but the sequence attribute must be no cycle. below is the sequence Two pseudo columns of the column: next value: indicates the next sequence value, which can be replaced by nextval; previous value: indicates the current sequence value, which can be replaced by currval. Source: http://blog.csdn.net/bobo12082119/article/details/8734679