Shulou(Shulou.com)05/31 Report--

This article is to share with you about how to use DMSQL DM self-adding column. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

The use of DM self-increment column

DM self-increment column definition

1. Self-adding function definition

Create a self-incrementing column in the table. This attribute is used with the CREATE TABLE statement, and a table can have only one self-incrementing column.

Grammatical format

IDENTITY [(seed, increment)]

Parameters.

1. The value used when the seed is loaded into the first row in the table

2.Increment value, which is added to the identity value of the previous loaded row. The increment value can be positive or negative, but not 0.

instructions

1.IDENTITY is suitable for columns of type int (- 2147483648) and bigint (- 263-2). Only one self-incrementing column can be created per table.

2. You cannot use DEFAULT constraints on self-incrementing columns

3. You must specify both the seed and the increment value, or neither. If neither is specified, the default value is taken (1). If the seed or increment is a decimal, an error is reported.

4. The maximum and minimum values are the boundaries of the data type of the column

5. table seed and increment greater than the maximum or seed and increment less than the minimum time error

6. Once the self-increment column is generated, it cannot be updated, and it is not allowed to modify it with the Update statement.

7. Temporary tables, column storage tables, horizontal partition tables, and vertical partition tables do not support the use of self-incrementing columns.

2. Self-incrementing column query function

1) IDENT_SEED (function)

Syntax format:

IDENT_SEED ('tablename')

Function: returns a seed value that is specified when a self-increment column is created in a table with a self-increment column.

Parameter: tablename: is a string constant in quotation marks, or it can be a variable, function, or column name. The data type of tablename is char or varchar. It means table name and can be prefixed with schema name.

Return type: return data type is int / NULL

2) IDENT_INCR (function)

Syntax format:

IDENT_INCR ('tablename')

Function: returns an incremental value that is specified when a self-increment column is created in a table with a self-increment column.

Parameter: tablename: is a string constant in quotation marks, or it can be a variable, function, or column name. The data type of tablename is char or varchar. It means table name and can be prefixed with schema name.

Return type: return data type is int / NULL

For example, use the self-increment column query function to get the seed and increment information of the self-increment column of the table PERSON_TYPE.

SQL > SELECT IDENT_SEED ('PERSON.PERSON_TYPE'); LINEID IDENT_SEED (' PERSON.PERSON_TYPE')-1 1used time: 1.529 (ms). Execute id is 46903.SQL > SELECT IDENT_INCR ('PERSON.PERSON_TYPE'); LINEID IDENT_INCR (' PERSON.PERSON_TYPE')-1 1used time: 0.956 (ms). Execute id is 46905.

SET IDENTITY_INSERT attribute

Sets whether explicit values are allowed to be inserted into the self-incrementing column of the table.

Grammatical format

SET IDENTITY_INSERT [

.]

ON | OFF

Parameters.

1.

Indicates the mode to which the table belongs, which defaults to the current mode

two。

Indicates the name of the table that contains the self-incremented column.

instructions

The default value of the 1.IDENTITY_INSERT property is OFF. SET IDENTITY_INSERT is set up at execution or run time. When a connection ends, the IDENTITY_INSERT property is automatically restored to OFF

2.DM requires that the IDENTITY_INSERT property of only one table in a session connection can be set to ON, and when you set a new table identity _ INSERT property to ON, tables that were previously set to ON will be automatically restored to OFF. When the IDENTITY_INSERT property of a table is set to ON, the value of the auto-increment column in the table is specified by the user. If the insert value is greater than the current identity value of the table (the current value of the self-increment column), DM automatically uses the new insert value as the current identity value, that is, it changes the current value of the self-increment column of the table; otherwise, the current value of the self-increment column will not be affected.

3. When the IDENTITY_INSERT property of a table is set to OFF, the current value of the self-incrementing column in the newly inserted row is automatically generated by the system and cannot be specified by the user.

4. Once the self-adding column is inserted, it cannot be modified

5. Manually insert a self-incrementing column, in addition to setting IDENTITY_INSERT to ON, it also requires that the name of the self-incrementing column to be inserted be explicitly specified in the insert list. Insert in exactly the same way as non-IDENTITY tables. If you insert without specifying a self-increment column name or assigning a value to a self-increment column, the current value of the self-increment column in the newly inserted row is automatically generated by the system.

Give examples to illustrate

For example, the use of SET IDENTITY_INSERT

1) the PERSON_ type column in the PERSON_ type table is self-incrementing.

SQL > select * from person.person_type LINEID PERSON_TYPEID NAME--1 1 Purchasing Manager 2 2 Purchasing Representative 3 3 sales Manager 4 4 sales Representative 5 9 Hygiene 6 10 cleaners 6 rows gotused time: 12.207 (ms). Execute id is 46927.

2) insert data into the table, and the value of the self-incrementing column is automatically generated by the system.

SQL > INSERT INTO PERSON.PERSON_TYPE (NAME) VALUES ('Director of sales'); affect rows 1used time: 0.884 (ms). Execute id is 46930.SQL > INSERT INTO PERSON.PERSON_TYPE (NAME) VALUES (Human Resources Manager); affect rows 1used time: 0.749 (ms). Execute id is 46931.SQL > commit;executed successfullyused time: 21.221 (ms). Execute id is 46933.SQL > select * from person.person_type LINEID PERSON_TYPEID NAME--1 1 Purchasing Manager 2 2 Purchasing Representative 3 3 sales Manager 4 4 sales Representative 5 9 Health worker 6 10 Cleaner 7 11 sales Director 8 12 Human Resources Manager 8 rows gotused time: 0.445 (ms). Execute id is 46934.

3) when inserting data and specifying the value of the self-incrementing column, the column to be inserted in the PERSON_TYPEID must be specified in the insert statement when IDENTITY_INSERT must be set to ON through the statement. For example:

SQL > SET IDENTITY_INSERT PERSON.PERSON_TYPE ON;executed successfullyused time: 32.673 (ms). Execute id is 46938.SQL > INSERT INTO PERSON.PERSON_TYPE (PERSON_TYPEID, NAME) VALUES (14, 'Advertising Manager'); affect rows 1used time: 0.966 (ms). Execute id is 46941.SQL > INSERT INTO PERSON.PERSON_TYPE (PERSON_TYPEID, NAME) VALUES (15, 'Finance Manager'); affect rows 1used time: 0.788 (ms). Execute id is 46943.SQL > commit;executed successfullyused time: 25.501 (ms). Execute id is 46944.SQL > select * from person.person_type LINEID PERSON_TYPEID NAME--1 1 Purchasing Manager 2 2 Purchasing Representative 3 3 sales Manager 4 4 sales Representative 5 9 Hygienist 6 10 Cleaner 7 11 sales Director 8 12 Human Resources Manager 9 14 Advertising Manager 10 15 Finance Manager 10 rows gotused time: 0.467 (ms). Execute id is 46945.

4) users are not allowed to modify the values of self-incrementing columns.

SQL > UPDATE PERSON.PERSON_TYPE SET PERSON_TYPEID = 14 WHERE NAME = 'Advertising Manager'; UPDATE PERSON.PERSON_TYPE SET PERSON_TYPEID = 14 WHERE NAME = 'Advertising Manager'; [- 2664]: Error in line: 1Try to alter identity column [PERSON_TYPEID]. Used time: 0.565 (ms). Execute id is 0.

5) restore the IDENTITY_INSERT property.

SQL > SET IDENTITY_INSERT PERSON.PERSON_TYPE OFF;executed successfullyused time: 0.597 (ms). Execute id is 46951.

6) query again after insertion. Notice the change in the current value of the self-increment column.

SQL > SET IDENTITY_INSERT PERSON.PERSON_TYPE OFF;executed successfullyused time: 0.597 (ms). Execute id is 46951.SQL > INSERT INTO PERSON.PERSON_TYPE (NAME) VALUES ('Marketing Director'); affect rows 1used time: 1.013 (ms). Execute id is 46954.SQL > commit;executed successfullyused time: 16.449 (ms). Execute id is 46955.SQL > select * from person.person_type LINEID PERSON_TYPEID NAME--1 1 Purchasing Manager 2 2 Purchasing Representative 3 3 sales Manager 4 4 sales Representative 5 9 Hygienist 6 10 Cleaner 7 11 sales Director 8 12 Human Resources Manager 9 14 Advertising Manager 10 15 Finance Manager 11 16 Marketing Director 11 rows gotused time: 1.000 (ms). Execute id is 46956. Thank you for reading! This is the end of the article on "how to use DMSQL DM self-addition". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.