0

I am trying to make Table Product_Classification to Classify Products on three levels columns as below

I think it is something called table with it self relation 

CREATE TABLE Product_Classification
(
   Classification_ID   NUMBER           NOT NULL,
   Classification_Desc VARCHAR2 (1024)  NOT NULL,
   Class1_ID           NUMBER,
   Class1_Desc         VARCHAR2 (1024),
   Class2_ID           NUMBER,
   Class2_Desc         VARCHAR2 (1024),
   Class3_ID           NUMBER,
   Class3_Desc         VARCHAR2 (1024),
   Notes               VARCHAR2 (1024),
   Created_By          VARCHAR2 (64)    NOT NULL,
   Created_On          DATE             NOT NULL,
   Updated_By          VARCHAR2 (64),
   Updated_On          DATE,
   --------------------Constraint--------------------
   CONSTRAINT PK_Classification_ID PRIMARY KEY (Classification_ID)
);
/

Classification_ID it should be controlled Primary key which will be the concatenation of Class1_ID, Class2_ID, and Class3_ID so each four digits in the primary key will allow me to access a level on the product classifications tree for example

Classification_ID  |    Classification_Desc |   class1_ID  |    class1_desc |   class2_ID | class2_desc   |  class3_ID| class3_desc
1000           |    example         |          |            |         |           |       |
10001111       |    example         |   1111       |    x product   |         |           |       |
100011112222       |    example         |   1111       |    x product   |   2222      | Product       |       |
1002           |    example1        |          |            |         |           |       |
10022222       |    example1        |   2222       |    y prodcut   |         |           |       | 
100222223333       |    example1        |   2222       |    y prodcut   |   3333      |     Product 1     |       |

So have created three sequences as below 

CREATE SEQUENCE Seq_Classification_ID MINVALUE 1000
                                      MAXVALUE 999999999999999999      /* 18 Digits */
                                      START WITH 1000
                                      INCREMENT BY 1
                                      CACHE 20;
/   
CREATE SEQUENCE Seq_Class1_ID MINVALUE 1000
                              MAXVALUE 999999999999999999      /* 18 Digits */
                              START WITH 1000
                              INCREMENT BY 1
                              CACHE 20;
/
CREATE SEQUENCE Seq_Class2_ID MINVALUE 1000
                              MAXVALUE 999999999999999999      /* 18 Digits */
                              START WITH 1000
                              INCREMENT BY 1
                              CACHE 20;
/
CREATE SEQUENCE Seq_Class3_ID MINVALUE 1000
                              MAXVALUE 999999999999999999      /* 18 Digits */
                              START WITH 1000
                              INCREMENT BY 1
                              CACHE 20;
/

Now I tried to do that by using trigger as below 

CREATE OR REPLACE TRIGGER Trg_Classification_ID_Seq
   BEFORE INSERT
   ON Product_Classification
   FOR EACH ROW
BEGIN

   IF (:NEW.CLASSIFICATION_DESC IS NOT NULL) THEN 
             :NEW.CLASSIFICATION_ID := :OLD.CLASSIFICATION_ID||:NEW.CLASS1_ID||:NEW.CLASS2_ID||:NEW.CLASS3_ID;
   ELSIF (:NEW.CLASSIFICATION_DESC IS NULL) THEN 
             :NEW.CLASSIFICATION_ID:=Seq_Classification_ID.NEXTVAL;        
   END IF;

   IF (:NEW.CLASS1_DESC IS NOT NULL) THEN
          :NEW.CLASS1_ID := Seq_Class1_ID.NEXTVAL;
   ELSIF (:NEW.CLASS1_DESC IS NULL) THEN
             :NEW.CLASS1_ID := NULL;
   END IF;

   IF (:NEW.CLASS2_DESC IS NOT NULL) THEN
             :NEW.CLASS2_ID := Seq_Class2_ID.NEXTVAL;
   ELSIF (:NEW.CLASS2_DESC IS NULL) THEN
             :NEW.CLASS2_ID := NULL;
   END IF;

   IF (:NEW.CLASS3_DESC IS NOT NULL) THEN
             :NEW.CLASS3_ID := Seq_Class3_ID.NEXTVAL;
   ELSIF (:NEW.CLASS3_DESC IS NULL) THEN
             :NEW.CLASS3_ID:=NULL;
   END IF;

END;

My problem is that I am getting cannot insert null value in CLASSIFICATION_ID

Second I do not know if that is the correct way to do something like that ?

I have also made a function to do that but that not correct way since I can insert record in table and the function will not be executed so I choose to use triggers which will be executed before each insert

please advice suggested

Thank all in advance

sam
  • 2,493
  • 6
  • 38
  • 73
  • Two thoughts. First, I would never create a column to be used as a key that is calculated off other columns. Stay away from this. I also don't see why this would add any value for you. Also, there would be no old: values for an insert trigger. For your PK, just use and stay with the a sequence generator. Second, with 12C, you can use a sequence value as a default value for the column so you will not have to use a trigger. Pre-12C, either have the client call to get the sequence, or wrap your insert with pl/sql where you can generate the sequence before insert. – unleashed Mar 09 '17 at 21:54
  • @TheGrandDuke my plan is each four digits will allow me to access a level on product classification – sam Mar 09 '17 at 22:10
  • So, what happens when the sequence moves to 10,001? It all falls apart. Do this with separate columns. Don't build this pseudo-intelligence into key values. – unleashed Mar 09 '17 at 22:28
  • @TheGrandDuke honestly I think about that but i think there is no product tree classification will exceed 10,000 it really huge number... I am looking for the standard approach or something to use that why my question is What is the correct way to implement controlled Primary key depending on column values - product classifications tree my friend I am still in implementation and looking for best way from professionals friends like you to learn from – sam Mar 09 '17 at 22:42
  • "What is the correct way to implement controlled Primary key depending on column values?" I don't believe this is valid. Someone else may have made this work, but this is not normal. Your PK should either be a sequentially generated value alone, or a column or set of columns that will be unique. But not some calculated column based on other values in the table. – unleashed Mar 09 '17 at 22:52
  • http://stackoverflow.com/questions/337503/whats-the-best-practice-for-primary-keys-in-tables – unleashed Mar 09 '17 at 22:56
  • @TheGrandDuke maybe it is not valid that is why I here and asking if their is away to do it or what should I do for that case .. thanks for link it really useful – sam Mar 09 '17 at 23:06
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/137709/discussion-between-the-grand-duke-and-sam). – unleashed Mar 09 '17 at 23:08

0 Answers0