Hi,
I want to insert the data by using the EF. I've four tables in my project:
1. Product_Main
2. Product_Sub
3. Product_Code
4. Billing_Groups
I already provided the relationship as per the REFERENTIAL CONSTRAINT to these table. I want to pick the value of one particualar column (PK column)from a table and want to pass that column's value to the referencing table.
I already created the required sequences. Kindly refer the table structures.
Table:1
CREATE TABLE PRODUCT_MAIN
(
PRODUCT_MAIN_CODE NUMBER(10) NOT NULL,
DESCRIPTION VARCHAR2(200 BYTE) NOT NULL,
IS_SELECTABLE_FLAG VARCHAR2(1 BYTE)
)
TABLESPACE PH4DBA
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 256K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE UNIQUE INDEX PK_PRODUCT_MAIN ON PRODUCT_MAIN
(PRODUCT_MAIN_CODE)
LOGGING
TABLESPACE PH4DBAIDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 256K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
CREATE OR REPLACE TRIGGER trigger_product_main
BEFORE INSERT ON "PRODUCT_MAIN"
FOR EACH ROW
BEGIN
IF (:new."PRODUCT_MAIN_CODE" IS NULL) THEN
SELECT PRODUCT_MAIN_SEQ.NEXTVAL INTO :new."PRODUCT_MAIN_CODE" FROM DUAL;
END IF;
END;
/
SHOW ERRORS;
ALTER TABLE PRODUCT_MAIN ADD (
CONSTRAINT PK_PRODUCT_MAIN
PRIMARY KEY
(PRODUCT_MAIN_CODE)
USING INDEX
TABLESPACE PH4DBAIDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 256K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
));
Table:2
CREATE TABLE PRODUCT_SUB
(
PRODUCT_SUB_CODE NUMBER(10) NOT NULL,
DESCRIPTION VARCHAR2(200 BYTE),
IS_SELECTABLE_FLAG VARCHAR2(1 BYTE)
)
TABLESPACE PH4DBA
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 256K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE UNIQUE INDEX PK_PRODUCT_SUB ON PRODUCT_SUB
(PRODUCT_SUB_CODE)
LOGGING
TABLESPACE PH4DBAIDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 256K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
CREATE OR REPLACE TRIGGER trigger_product_sub
BEFORE INSERT ON "PRODUCT_SUB"
FOR EACH ROW
BEGIN
IF (:new."PRODUCT_SUB_CODE" IS NULL) THEN
SELECT PRODUCT_SUB_SEQ.NEXTVAL INTO :new."PRODUCT_SUB_CODE" FROM DUAL;
END IF;
END;
/
SHOW ERRORS;
ALTER TABLE PRODUCT_SUB ADD (
CHECK ("DESCRIPTION" IS NOT NULL) DISABLE);
ALTER TABLE PRODUCT_SUB ADD (
CONSTRAINT PK_PRODUCT_SUB
PRIMARY KEY
(PRODUCT_SUB_CODE)
USING INDEX
TABLESPACE PH4DBAIDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 256K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
));
Table:3
CREATE TABLE BILLING_GROUP
(
BILLING_GROUP_CODE VARCHAR2(15 BYTE) NOT NULL,
DESCRIPTION VARCHAR2(200 BYTE),
LOW NUMBER(7),
HIGH NUMBER(7),
BID_LIMIT NUMBER(5),
STD_TIMING_DAYS NUMBER(5)
)
TABLESPACE PH4DBA
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 256K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE UNIQUE INDEX PK_BILLING_GROUP ON BILLING_GROUP
(BILLING_GROUP_CODE)
LOGGING
TABLESPACE PH4DBAIDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 256K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
ALTER TABLE BILLING_GROUP ADD (
CHECK ("DESCRIPTION" IS NOT NULL) DISABLE);
ALTER TABLE BILLING_GROUP ADD (
CONSTRAINT PK_BILLING_GROUP
PRIMARY KEY
(BILLING_GROUP_CODE)
USING INDEX
TABLESPACE PH4DBAIDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 256K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
));
Table:4
CREATE TABLE PRODUCT_CODES
(
PRODUCT_CODE NUMBER(10) NOT NULL,
BILLING_GROUP_CODE VARCHAR2(15 BYTE),
PRODUCT_MAIN_CODE NUMBER(10),
PRODUCT_SUB_CODE NUMBER(10),
GRADE_LEVEL NUMBER(2),
POSITION_DESCRIPTION VARCHAR2(2000 BYTE),
RATE_CODE VARCHAR2(15 BYTE),
SKILLS_REQUIRED VARCHAR2(2000 BYTE),
SKILLS_PREFERRED VARCHAR2(2000 BYTE),
EXP_REQUIRED VARCHAR2(2000 BYTE),
EXP_PREFERRED VARCHAR2(2000 BYTE),
EDU_REQUIRED VARCHAR2(2000 BYTE),
EDU_PREFERRED VARCHAR2(2000 BYTE),
BACKGROUND_VERIFICATION_FLAG VARCHAR2(2 BYTE),
IS_SELECTABLE_FLAG VARCHAR2(1 BYTE),
RR_FLAG VARCHAR2(1 BYTE) DEFAULT 'N'
)
TABLESPACE PH4DBA
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 512K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE UNIQUE INDEX PK_PRODUCT_CODES ON PRODUCT_CODES
(PRODUCT_CODE)
LOGGING
TABLESPACE PH4DBAIDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 256K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
CREATE INDEX IDX_PRODUCT_REF_MAIN_CODE ON PRODUCT_CODES
(PRODUCT_MAIN_CODE)
LOGGING
TABLESPACE PH4DBAIDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 256K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
CREATE INDEX IDX_PRODUCT_REF_SUB_CODE ON PRODUCT_CODES
(PRODUCT_SUB_CODE)
LOGGING
TABLESPACE PH4DBAIDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 256K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
ALTER TABLE PRODUCT_CODES ADD (
CONSTRAINT PK_PRODUCT_CODES
PRIMARY KEY
(PRODUCT_CODE)
USING INDEX
TABLESPACE PH4DBAIDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 256K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
));
ALTER TABLE PRODUCT_CODES ADD (
CONSTRAINT FK_PRODUCT__REF_GRADE_GRADE_LE
FOREIGN KEY (GRADE_LEVEL)
REFERENCES GRADE_LEVEL (GRADE_LEVEL) DISABLE);
ALTER TABLE PRODUCT_CODES ADD (
CONSTRAINT FK_PRODUCT__REF_BILLI_BILLING_
FOREIGN KEY (BILLING_GROUP_CODE)
REFERENCES BILLING_GROUP (BILLING_GROUP_CODE) DISABLE);
ALTER TABLE PRODUCT_CODES ADD (
CONSTRAINT FK_PRODUCT__REF_2896_PRODUCT_
FOREIGN KEY (PRODUCT_SUB_CODE)
REFERENCES PRODUCT_SUB (PRODUCT_SUB_CODE) DISABLE);
ALTER TABLE PRODUCT_CODES ADD (
CONSTRAINT FK_PRODUCT__REF_2893_PRODUCT_
FOREIGN KEY (PRODUCT_MAIN_CODE)
REFERENCES PRODUCT_MAIN (PRODUCT_MAIN_CODE) DISABLE);
Kindly advise.
Many Thanks,
Pradeep Dubey.