Tuesday, September 28, 2010

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.

No comments: