CODEWITHSUNDEEP
sqloracle-databasemerge
fati lem
fati lem

Reputation: 79

insert a null value in a foreign key column using a merge statement oracle

I have a table imp described as imp(ID, col1, col2, col3) and a second table soc (ID, colSoc1, colSoc2, colSoc3). The ColSoc3 is a foreign key to another table . I've implemented the following request to merge the two tables :

 MERGE INTO soc tb
    USING (
              SELECT ID,
                     col1,
                     col2,
                     CASE
                      WHEN col3= NULL THEN
                          NULL
                      ELSE
                          col3
                      END AS col3
              from imp ) req on ( tb.ID=req.ID)
   when matched then 
     update set tb.colSoc1=req.col1, tb.colSoc2=req.col2, tb.colSoc3=req.col3
   when not matched then  
    insert (ID, colSoc1, colSoc2, colSoc3) 
   values (req.ID, req.col1, req.col2, req.col3);

The problem I have is when the col3 value is NULL nothing is inserted in the soc table. How can I force insertion of col3 into Colsoc3 even if it has null value?

the ddl for soc is as below :

  CREATE TABLE "OWNER"."soc" 
   (    
    "ID" VARCHAR2(17 CHAR) NOT NULL ENABLE, 
    "socCol1" VARCHAR2(17 CHAR), 
    "socCol2" VARCHAR2(17 CHAR),  
    "socCol3" VARCHAR2(17 CHAR),  
     CONSTRAINT "Soc_PK" PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  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 "DATA0001"  ENABLE, 
     CONSTRAINT "Soc_FK1" FOREIGN KEY ("socCol3")
      REFERENCES "OWNER"."socParent" ("ID_parent") ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS NOLOGGING
  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 "DATA0001" ;

what let me think that the null foreign key is the problem is I tried not to insert col3 and it worked and then I used the request below to see what are the lines with parent key not found and the result all have null value for col3

select * from imp where col3 not in ( select ID_parent from socParent);

Thanks in advance.

Upvotes: 0

Views: 119

Answers (1)

fati lem
fati lem

Reputation: 79

I've tried : WHEN col3 = 'null' instead of WHEN col3 is NULL and it works.

Upvotes: 0

Related Questions