How can I insert the ID name as a foreign key in the procedure so that the null value is not in the-CodePudding

The NAME_ID column is NULL, the NAME_ID in table table13_prc is foreign key and ID is primary key from table table_prc4. I want to output in column NAME_ID be token from id from table_prc4.

My code is:

Create Table table13_prc (
                          Family    VARCHAR2(200),
                          Name      VARCHAR2(200) ,
                          ID        INTEGER  NOT NULL PRIMARY KEY ,
                          NAME_ID   INTEGER  REFERENCES TABLE_PRC4(id)
                          ) ;
                                                   
CREATE SEQUENCE ID_seq5
  MINVALUE 1
  START WITH 1
  INCREMENT BY 1 ;
   
Create or Replace trigger trg5 
  BEFORE insert on  table13_prc 
   for each row 
   BEGIN
   select ID_seq5.nextval INTO :new.ID from dual ;
   END ;
 
CREATE OR REPLACE PROCEDURE addnewmem6 (str IN VARCHAR2)
   AS
    BEGIN
      INSERT INTO table13_prc (Name, Family)
          WITH
            temp
             AS
               (    SELECT REGEXP_SUBSTR (str,
                                           '[^,] ',
                                          1,
                                        LEVEL) val
                    FROM DUAL
               CONNECT BY LEVEL <= REGEXP_COUNT (str, ',')   1)
         SELECT TRIM(SUBSTR(val, 1, INSTR (val, ';') - 1)),
               TRIM(SUBSTR(val, INSTR (val, ';')   1))
          FROM temp;
 
      COMMIT;
   END;
 
CREATE OR REPLACE PROCEDURE delete_member2 (par_id IN VARCHAR2)
IS
BEGIN
   DELETE FROM table13_prc
         WHERE id = par_id;
END;


BEGIN
  addnewmem6 ('faezeh;Ghanbarian,pari;izadi');
END;
    
BEGIN
  addnewmem6 ('Saeed;Izadi,Saman; Rostami');
END; 
    
BEGIN
  delete_member2 (1);
END;
 
BEGIN
  delete_member2 (2) ;
END;
 
CALL delete_member2(5);
 
CALL delete_member2(66);

   
select *
from  table13_prc ;

I want in output, the column NAME_ID is not null, I want in NAME_ID be taken form id in table_prc4.

CodePudding user response：

I want in output, the column NAME_ID is not null, I want in NAME_ID be taken form id in table_prc4.

You can't "automate" it. table13_prc.NAME_ID references table_prc4.ID, but INSERT statement can't know which table_prc4.ID value to use. It is you who should specify that value.

Foreign key constraint just won't allow you to insert value (into table13_prc.NAME_ID) that doesn't exist in table_prc4.ID.

CodePudding user response：

You'll need to look up the correct name_id from table_prc4:

  INSERT INTO table13_prc (Name, Family, Name_ID)
      WITH
        temp
         AS
           (    SELECT REGEXP_SUBSTR (str,
                                       '[^,] ',
                                      1,
                                    LEVEL) val
                FROM DUAL
           CONNECT BY LEVEL <= REGEXP_COUNT (str, ',')   1)
     SELECT TRIM(SUBSTR(val, 1, INSTR (val, ';') - 1)),
           TRIM(SUBSTR(val, INSTR (val, ';')   1)),
           table_prc4.id
      FROM temp,
           table_prc4
     WHERE temp.TRIM(SUBSTR(val, 1, INSTR (val, ';') - 1)) = table_prc4.name ???

You could also do this in your insert trigger. But whether in your insert statement or in your insert trigger, you have to look up the ID value somehow. Oracle won't do that for you.