How to set the self-addition primary key in Dameng database and precautions

The self-increase of the Dameng database is usually done by using sequences and triggers.

1. Create a sequence

CREATE SEQUENCE your_sequence_name  
START WITH 1  
INCREMENT BY 1  
NOCACHE;

2. Create a trigger

When inserting a new record, use a trigger to automatically get the next value from the sequence and set it to the self-increment field.

CREATE OR REPLACE TRIGGER your_trigger_name  
BEFORE INSERT ON your_table_name  
FOR EACH ROW  
BEGIN  
    IF : IS NULL THEN -- AssumptionsidIt is a self-increasing field  
        SELECT your_sequence_name.NEXTVAL INTO : FROM DUAL;  
    END IF;  
END;

Complete examples are:

CREATE SEQUENCE seq_u_operation_log_id  
START WITH 1  
INCREMENT BY 1  
NOCACHE;

CREATE TABLE HSZ_PRODUCT_UCENTER.U_OPERATION_LOG (
	ID BIGINT NOT NULL,
	OPERATION VARCHAR(50),
	REQUEST_URI VARCHAR(255),
	REQUEST_METHOD VARCHAR(20),
	REQUEST_PARAMS CLOB,
	USER_AGENT VARCHAR(500),
	CREATOR_NAME VARCHAR(50),
	OPERATION_TIME TIMESTAMP,
	REQUEST_IP VARCHAR(100),
	REQUEST_TIME BIGINT,
	REQUEST_STATUS TINYINT,
	REQUEST_RESULT CLOB,
	PRIMARY KEY (ID)
);

CREATE OR REPLACE TRIGGER trg_u_operation_log_before_insert  
BEFORE INSERT ON U_OPERATION_LOG  
FOR EACH ROW  
BEGIN  
    IF  IS NULL OR  = 0 THEN   
        SELECT seq_u_operation_log_id.NEXTVAL INTO : FROM DUAL;  
    END IF;  
END;  

Notice: The trigger syntax in DMDB may not be exactly the same as the example above, depending on your DMDB version and configuration. The example above is based on Oracle-style syntax, because the Dameng database is similar to Oracle in some ways. You may need to consult the official documentation for your DMDB version to get the exact trigger syntax.

Things to note

When using IDENTITY columns, make sure your application does not specify values ​​for IDENTITY columns when inserting records, unless you have special requirements.

If you use sequences and triggers, make sure that the names, table names, field names, etc. of the sequences and triggers match your actual situation.

When using sequences, consider whether caching (NOCACHE or CACHE) is needed to improve performance. However, using cache may bring the risk of sequence value rollback, depending on your specific application scenario.

Always check the latest Dameng database documentation, as different versions of the database may differ in functionality and syntax.

Summarize

This is the article about how to set the self-added primary key of the Dameng database and precautions. For more related contents of setting the self-added primary key of the Dameng database, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!