Q

Foreign key referencing unique key in parent table

How do I create a foreign key from the child table that references a UNIQUE key in the parent table? I have a table named employee with a field emp_id. I want to add an entry in the table every 10 minutes if the max value of emp_id is 10. After 10 minutes I want to add an entry in the table with value 11. And after 10 minutes, another entry must be added with the value 12, and so on.

How do I create a foreign key from the child table that references a UNIQUE key in the parent table? I have a table named employee with a field emp_id. I want to add an entry in the table every 10 minutes if the max value of emp_id is 10. After 10 minutes I want to add an entry in the table with value 11. And after 10 minutes, another entry must be added with the value 12, and so on.
The alter table command listed below is an example of how to create a foreign key that references a parent table. The field in the parent table can be either a UNIQUE key or a PRIMARY key. These keys can also be concatenated keys.
ALTER TABLE child_table ADD 
CONSTRAINT frpp_report_id_fk
 FOREIGN KEY (FRPP_RUN_ID)
 REFERENCES parent_table (FRPP_RUN_ID) ENABLE
 VALIDATE

The job procedure below inserts an ID that is incremented by 1 every 10 minutes, into the table TEST.

DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
    (
      job        => X
     ,what       => 'insert into table test (ID)
                     values (sequence_name.NEXTVAL);'
     ,next_date  => to_date('27/08/2006 17:41:13','dd/mm/yyyy hh24:mi:ss')
     ,interval   => 'SYSDATE+10/1440 '
     ,no_parse   => FALSE
    );
END;
This was first published in August 2006

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close