Oracle - Create auto-increment equivalent in MySQL
So for example, I have my table below to create
CREATE TABLE "PC2"."TMP_PRODUCT"
(
"ID" NUMBER(38,0) NOT NULL ENABLE,
"ACCOUNTCODE" VARCHAR2(20 BYTE),
"PRICESEGMENT" VARCHAR2(20 BYTE),
"BRAND" VARCHAR2(20 BYTE),
"PRODUCTLENGTH" NUMBER,
"SALESID" NUMBER(38,0),
CONSTRAINT "PK_TMP2_WPP_1009_ID" PRIMARY KEY ("ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255,
CONSTRAINT "FK_TMP2_WPP_1009_SID" FOREIGN KEY ("SALESID") REFERENCES "PC2"."SALES" ("ID") DEFERRABLE INITIALLY DEFERRED ENABLE
);
Then create a sequence which would manage the incremental part of the index key.
CREATE SEQUENCE SEQ_TMP_PRODUCT
START WITH 1
INCREMENT BY 1;
Afterwards, create a trigger which what will do is to insert the incremented key of the table.
CREATE OR REPLACE TRIGGER "TMP_PRODUCT_ID" BEFORE
INSERT ON TMP_PRODUCT FOR EACH ROW WHEN (NEW.ID IS NULL) BEGIN
SELECT SEQ_TMP_PRODUCT.NEXTVAL INTO :NEW.ID FROM DUAL;
END;
Just that simple. Just be sure with the table names, sequence names as you might confuse if you base on that sample I have.
Hope that helps.
CREATE TABLE "PC2"."TMP_PRODUCT"
(
"ID" NUMBER(38,0) NOT NULL ENABLE,
"ACCOUNTCODE" VARCHAR2(20 BYTE),
"PRICESEGMENT" VARCHAR2(20 BYTE),
"BRAND" VARCHAR2(20 BYTE),
"PRODUCTLENGTH" NUMBER,
"SALESID" NUMBER(38,0),
CONSTRAINT "PK_TMP2_WPP_1009_ID" PRIMARY KEY ("ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255,
CONSTRAINT "FK_TMP2_WPP_1009_SID" FOREIGN KEY ("SALESID") REFERENCES "PC2"."SALES" ("ID") DEFERRABLE INITIALLY DEFERRED ENABLE
);
Then create a sequence which would manage the incremental part of the index key.
CREATE SEQUENCE SEQ_TMP_PRODUCT
START WITH 1
INCREMENT BY 1;
Afterwards, create a trigger which what will do is to insert the incremented key of the table.
CREATE OR REPLACE TRIGGER "TMP_PRODUCT_ID" BEFORE
INSERT ON TMP_PRODUCT FOR EACH ROW WHEN (NEW.ID IS NULL) BEGIN
SELECT SEQ_TMP_PRODUCT.NEXTVAL INTO :NEW.ID FROM DUAL;
END;
Just that simple. Just be sure with the table names, sequence names as you might confuse if you base on that sample I have.
Hope that helps.
Comments