Data cleansing Note: Generation of primary key class ID Fields

Data cleansing Note: Generation of primary key class ID Fields

 

Background]

After data is extracted from the source end and inserted to the target end, a primary key ID field needs to be created.

 

[Solution]

Method 1: Use the sys_guid () function to generate a 32-bit Note Number

For example, in the emp table, create a field and set the default value of the field to automatically generate a 32-bit random number with the sys_guid () function.

Alter table EMP add ID varchar2 (32) default sys_guid ();

 

Method 2: Use a trigger to insert an auto-incrementing Sequence

For example, before inserting a new piece of data into the emp1 table, insert an ID value. Specifically, seq_idadd is a sequence that has been created in advance. You can customize the starting value and difference value of the sequence.

Create or replace trigger tr_IDADD

Before insert on emp1

For each row

Begin

Select seq_idadd.nextval into: new. id from dual;

End;

 

Method 3: Splicing

For example, an ID Primary Key is formed by splicing the source business fields. For example:

Source ID|To_char (sysdate, 'yyyymmdd ')|Lpad (NEXTNUM. Nextval, 10, 0)

Assume that the source ID is 12 bits. In this way, a 12 + 8 + 10 = 30-bit primary key number is intercepted.

 

[Experiment]

Use sys_guid to obtain the ID value, as shown below:

With the trigger, ID increments by 1 for each new piece of data, as shown below:

 

Obtain the primary key ID by splicing, as shown below:

Small knowledge, easy to remember.