In the process of database design, we usually set the ID field in the database table to increase by itself. Following is an example of a commonly used data dictionary table to illustrate how to set up self-increasing fields in each database.

MySQL

The MySQL database only needs to add AUTO_INCREMENT to the target field and set AUTO_INCREMENT=x for the table.
x: Number from the beginning of the increase.

Reference examples:

CREATE TABLE `dictionary` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `parent_id` int(10) unsigned NOT NULL COMMENT 'father ID',
  `type` varchar(50) NOT NULL COMMENT 'Metadata Type',
  `item_name` varchar(100) NOT NULL COMMENT 'Display name of metadata item',
  `item_value` varchar(100) DEFAULT NULL COMMENT 'Metadata Item Storage Value',
  `comment` varchar(200) DEFAULT NULL COMMENT 'Remarks',
  `deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Delete tag',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='data dictionary';

PostgreSQL

PostgreSQL database has many ways to realize the setting of self-increasing fields, commonly used are:

1. SERIAL, the simplest
2. IDENTITY, a new feature of PostgreSQL 10
3. Create SEQUENCE, more flexible

Reference example: SERIAL

create table dictionary (
   id                   SERIAL not null,
   parent_id            INT4                 not null,
   type                 VARCHAR(50)          not null,
   item_name            VARCHAR(100)         not null,
   item_value           VARCHAR(100)         null,
   comment              VARCHAR(200)         null,
   deleted              INT2                 not null default 0,
   create_time          DATE                 not null default CURRENT_TIMESTAMP,
   constraint PK_dictionary primary key (id)
);

Oracle

Two commonly used methods for setting self-increasing fields in Oracle databases are as follows:

1. Create SEQUENCE
2. IDENTITY, requires Oracle database version 12c or more

12c version example:

create table "dictionary" (
   "id"                 INTEGER  generated as identity ( start with 1 nocycle noorder)  not null,
   "parent_id"          INTEGER               not null,
   "type"               VARCHAR2(50)          not null,
   "item_name"          VARCHAR2(100)         not null,
   "item_value"         VARCHAR2(100),
   "comment"            VARCHAR2(200),
   "deleted"            SMALLINT             default 0  not null,
   "create_time"        TIMESTAMP            default CURRENT_TIMESTAMP  not null,
   constraint "PK_dictionary" primary key ("id")
);

11g version example:

-- Building tables
create table "dictionary" (
   "id"                 INTEGER               not null,
   "parent_id"          INTEGER               not null,
   "type"               VARCHAR2(50)          not null,
   "item_name"          VARCHAR2(100)         not null,
   "item_value"         VARCHAR2(100),
   "comment"            VARCHAR2(200),
   "deleted"            SMALLINT             default 0  not null,
   "create_time"        TIMESTAMP            default CURRENT_TIMESTAMP  not null,
   constraint "PK_dictionary" primary key ("id")
);
-- Create sequence
CREATE SEQUENCE DICTIONARY_ID_SEQ
INCREMENT BY 1
START WITH 1;
-- Create triggers, not required
CREATE OR REPLACE TRIGGER DICTIONARY_ID_SEQ_TRG
BEFORE INSERT ON "dictionary"
FOR EACH ROW
WHEN (NEW."id" IS NULL)
BEGIN
  SELECT DICTIONARY_ID_SEQ.NEXTVAL
  INTO :NEW."id"
  FROM DUAL;
END;

MsSQL

MsSQL is the SQL Server database, using IDENTITY can be.

Reference examples:

create table dictionary (
   id                   int                  identity,
   parent_id            int                  not null,
   type                 varchar(50)          not null,
   item_name            varchar(100)         not null,
   item_value           varchar(100)         null,
   comment              varchar(200)         null,
   deleted              smallint             not null default 0,
   create_time          datetime             not null default CURRENT_TIMESTAMP,
   constraint PK_dictionary primary key (id)
);

diboot Simple and Efficient Light Code Development Framework (star)