Basic primary key / foreign key with constraint, sequence, trigger

Question

Learner here in Oracle 11g. I'm having an issue with INSERTing some rows into two tables that are linked by a primary/foreign key relationship.

Basically I create a sequence to start with 1000 and increment by 1.

Then create a 'STORE' table with a ST_ID column

The ST_ID column is linked to the SEQUENCE with a TRIGGER.

Then I have an 'EMPLOYEE' table that has a EST_ID field that is a foreign key to the ST_ID column in the STORE table.

However, when I tried to insert rows I initially got a error saying EST_ID could not be null. So I created a sequence and trigger for EST_ID and now I'm getting an error saying the foreign key constraint is being violated.

I think that was maybe the wrong thing to do. Do I really want E_ID and EST_ID to be identical and how would I get that to happen? With some kind of trigger?

The actual code:

 CREATE SEQUENCE   "STORSEQ"  MINVALUE 1000 MAXVALUE 9999 INCREMENT BY 1 START WITH     1000     NOCACHE  NOORDER  

NOCYCLE ;

CREATE TABLE  "STORE" 
   (    "ST_ID" CHAR(4) NOT NULL ENABLE, 
    "STADDR_ID" CHAR(4) NOT NULL ENABLE, 
     CONSTRAINT "STORE_PK" PRIMARY KEY ("ST_ID") ENABLE
   ) ;

CREATE TABLE  "EMPLOYEE" 
   (    "E_ID" CHAR(8) NOT NULL ENABLE, 
    "EF_NAME" VARCHAR2(20) NOT NULL ENABLE, 
    "EL_NAME" VARCHAR2(20) NOT NULL ENABLE, 
    "EST_ID" CHAR(4) NOT NULL ENABLE, 
     CONSTRAINT "EMPLOYEE_PK" PRIMARY KEY ("E_ID") ENABLE
   ) ;

alter table "EMPLOYEE" add CONSTRAINT "EMPLOYEE_CON" foreign key ("EST_ID") references 

"STORE" ("ST_ID")
/

CREATE OR REPLACE TRIGGER  "BI_STORE" 
  before insert on "STORE"               
   for each row  
begin   
  if :NEW."ST_ID" is null then 
    select "STORSEQ".nextval into :NEW."ST_ID" from dual; 
  end if; 
end; 

/

At the moment my INSERT code looks like this:

    INSERT INTO STORE
    (ST_ID, STADDR_ID)
    VALUES
    (DEFAULT, DEFAULT);

    INSERT INTO EMPLOYEE
    (EF_NAME, EL_NAME)
    VALUES
    ('James', 'Smith');

when you are inserting into employee are you passing value for EST_ID? — rs.
No I'm not. Just values for the other columns. I thought the code might know to automatically insert the same value into EST_ID as ST_ID but I guess I'm totally wrong about that? — SJSL
yes it will not automatically insert value for you, you need to pass that value. Are you inserting rows in both table in single transaction? — rs.
But if ST_ID is automatically inserted by a SEQUENCE and TRIGGER, how do I pass whatever value that is? Do I need another trigger to say 'put the same value as ST_ID into EST_ID?' (thanks for your help - really appreciate it) — SJSL
how are you executing your insert statement? ARe they executed one after another in same procedure? — rs.

rs. rs. · Accepted Answer · 2013-04-28T00:07:41

When you try to insert data into table that has foreign key reference, it will not get value for id automatically, you need to pass that value.

You can do this:

declare 
v_store_id integer;
begin
  INSERT INTO STORE (ST_ID, STADDR_ID) VALUES (DEFAULT, DEFAULT)
  RETURNING ST_ID INTO v_Store_id; 

  INSERT INTO EMPLOYEE (EF_NAME, EL_NAME, EST_ID) 
   VALUES ('James', 'Smith', v_store_id);
end;

You can also insert id in store id table without trigger using this

declare 
v_store_id integer;
begin
  INSERT INTO STORE (ST_ID, STADDR_ID) VALUES ("STORSEQ".nextval, DEFAULT)
  RETURNING ST_ID INTO v_Store_id; 

  INSERT INTO EMPLOYEE (EF_NAME, EL_NAME, EST_ID) 
  VALUES ('James', 'Smith', v_store_id);
end

Basic primary key / foreign key with constraint, sequence, trigger

1 Answers