title: Basic primary key / foreign key with constraint, sequence, trigger
body: <p>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. </p>
  
  <p>Basically I create a sequence to start with 1000 and increment by 1.</p>
  
  <p>Then create a 'STORE' table with a ST_ID column</p>
  
  <p>The ST_ID column is linked to the SEQUENCE with a TRIGGER.</p>
  
  <p>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.</p>
  
  <p>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. </p>
  
  <p>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? </p>
  
  <p>The actual code:</p>
  
  <pre><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; 
  
  /
  </code></pre>
  
  <p>At the moment my INSERT code looks like this:</p>
  
  <pre><code>    INSERT INTO STORE
      (ST_ID, STADDR_ID)
      VALUES
      (DEFAULT, DEFAULT);
  
      INSERT INTO EMPLOYEE
      (EF_NAME, EL_NAME)
      VALUES
      ('James', 'Smith');
  </code></pre>