************************************************************************************* * Description: Creating trigger and sequence to add primary key on an existing table * Date: 05:13 PM EST, 08/01/2017 ************************************************************************************* <1> Synmton: | |__ o. In Oracle database, if a tables got created without primary key, the application may throw out below Error 6013: The table/view 'EMERALIT_DEV.RENEWAL_INDUSTRY_CATEGORY' does not have a primary key defined and no valid primary key could be inferred. The table/view has been excluded. To use the entity, you will need to review your schema, add the correct keys, and uncomment it. <2> Creating a preseudo primary key column on an exsiting table: | | |__ o. Table DDL before applying the changes: | | SQL> CREATE TABLE "EMERALIT"."RENEWAL_COEFFICIENTS" | ( "VAR_DESCRIPTION" VARCHAR2(50), | "VAR_COEFFICIENT" NUMBER, | "MODEL_NO" NUMBER, | "VAR_CODE" VARCHAR2(2) | ) SEGMENT CREATION IMMEDIATE | PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 | NOCOMPRESS LOGGING | STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 | PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 | BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) | TABLESPACE "LAPPS_DATA"; | | |__ o. Adding a new column as primary key: | | SQL> ALTER TABLE EMERALIT.RENEWAL_COEFFICIENTS ADD( RENEWAL_ID number ); | | |__ o. Creating a sequence for the primary key value: | | SQL> CREATE SEQUENCE EMERALIT.RENEWAL_ID_SEQ START WITH 1 INCREMENT BY 1; | | |__ o. Update table with assigning sequence number as primary key value: | | SQL> UPDATE EMERALIT.RENEWAL_COEFFICIENTS SET RENEWAL_ID = EMERALIT.RENEWAL_ID_SEQ.nextval; | | |__ o. Creating constraint on primary key column: | | SQL> ALTER TABLE EMERALIT.RENEWAL_COEFFICIENTS ADD CONSTRAINT PK_RENEWAL_ID PRIMARY KEY( RENEWAL_ID ); | | |__ o. Create insert trigger to ensure primary key value got updated automatically: SQL> CREATE OR REPLACE TRIGGER EMERALIT.RENEWAL_ID_TRGG BEFORE INSERT ON EMERALIT.RENEWAL_COEFFICIENTS FOR EACH ROW BEGIN :new.RENEWAL_ID := EMERALIT.RENEWAL_ID_SEQ.nextval; END; /
Your Comments